PS Query & SQL

I have an expression that I want to make unique

'INV'|| NEXT_DAY(SYSDATE - 7, 'SUNDAY')||'_'||A.ITEM_TYPE||'_'||A.EMPLID||'_'||ROWNUM

I want it to only have the rownum after it sums up the amount for a student. 

Example student A got a 50.00 scholarship, then another row removed the 50.00 scholarship. So a net of zero. I don't want to show the person who has a net of zero - but by my expression - it gives me every row of data..... 

Is there a way to get what I want from Query Manager? 

I have an expression that I want to make unique

'INV'|| NEXT_DAY(SYSDATE - 7, 'SUNDAY')||'_'||A.ITEM_TYPE||'_'||A.EMPLID||'_'||ROWNUM

I want it to only have the rownum after it sums up the amount for a student. 

Example student A got a 50.00 scholarship, then another row removed the 50.00 scholarship. So a net of zero. I don't want to show the person who has a net of zero - but by my expression - it gives me every row of data..... 

Is there a way to get what I want from Query Manager? 

As Jeffrie said, seeing some screenshots or sample data would help.

Depending on what you're trying to do, you might be able to use something like DENSE_RANK. For example, the following query pulls from NAMES and gives each person a unique row number, now matter how many name types they have (so, the first person gets DENSE_RANK 1, second 2, etc.)

SELECT N.EMPLID, N.NAME_TYPE, N.NAME, DENSE_RANK() OVER (ORDER BY N.EMPLID) AS row_dense_rank
FROM PS_NAMES N
WHERE N.EMPLID IN ('1234567', '2345678', '3456789')
  AND N.EFFDT = (SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED WHERE N_ED.EMPLID = N.EMPLID AND N_ED.NAME_TYPE = N.NAME_TYPE AND N_ED.EFFDT <= SYSDATE)
ORDER BY N.EMPLID, N.NAME_TYPE;


Sample output from the above:

1234567  PRF  Doe, Jane  1
1234567  PRI  Doe, Jane  1
2345678  PRF  Doe, John  2
2345678  PRI  Doe, John  2
3456789  PRF  Doe, Judy  3
3456789  PRI  Doe, Judy  3

RANK() works similarly except it gives results of 1, 1, 3, 3, 5, 5

I have an expression that I want to make unique

'INV'|| NEXT_DAY(SYSDATE - 7, 'SUNDAY')||'_'||A.ITEM_TYPE||'_'||A.EMPLID||'_'||ROWNUM

I want it to only have the rownum after it sums up the amount for a student. 

Example student A got a 50.00 scholarship, then another row removed the 50.00 scholarship. So a net of zero. I don't want to show the person who has a net of zero - but by my expression - it gives me every row of data..... 

Is there a way to get what I want from Query Manager? 

Ideally I want 4 rows - and for there to be a number in the unique transaction that says a row number.

When I add ROWNUM to the end of my transaction number I get 6 rows.... because they are no longer summing up.

Steph

Stephanie Zimmerman '01

Student Financial Services

IT Tech Associate-Systems Analyst

szimmer2@ilstu.edu

309.438.5164

Restorative - Command - Ideation - Intellection - Achiever

As Jeffrie said, seeing some screenshots or sample data would help.

Depending on what you're trying to do, you might be able to use something like DENSE_RANK. For example, the following query pulls from NAMES and gives each person a unique row number, now matter how many name types they have (so, the first person gets DENSE_RANK 1, second 2, etc.)

SELECT N.EMPLID, N.NAME_TYPE, N.NAME, DENSE_RANK() OVER (ORDER BY N.EMPLID) AS row_dense_rank
FROM PS_NAMES N
WHERE N.EMPLID IN ('1234567', '2345678', '3456789')
  AND N.EFFDT = (SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED WHERE N_ED.EMPLID = N.EMPLID AND N_ED.NAME_TYPE = N.NAME_TYPE AND N_ED.EFFDT <= SYSDATE)
ORDER BY N.EMPLID, N.NAME_TYPE;


Sample output from the above:

1234567  PRF  Doe, Jane  1
1234567  PRI  Doe, Jane  1
2345678  PRF  Doe, John  2
2345678  PRI  Doe, John  2
3456789  PRF  Doe, Judy  3
3456789  PRI  Doe, Judy  3

RANK() works similarly except it gives results of 1, 1, 3, 3, 5, 5

I have an expression that I want to make unique

'INV'|| NEXT_DAY(SYSDATE - 7, 'SUNDAY')||'_'||A.ITEM_TYPE||'_'||A.EMPLID||'_'||ROWNUM

I want it to only have the rownum after it sums up the amount for a student. 

Example student A got a 50.00 scholarship, then another row removed the 50.00 scholarship. So a net of zero. I don't want to show the person who has a net of zero - but by my expression - it gives me every row of data..... 

Is there a way to get what I want from Query Manager? 

In my understanding, ROWNUM is the unordered row number of the entire result set created by the FROM and JOIN statements, before anything is filtered by the WHERE clause. I'd be happy to be corrected on that, though!

I agree with Tim Weston that DENSE_RANK() may be the option you're looking for. You should also consider RANK() and my usual, ROW_NUMBER(). You can use them as aggregate expressions (with the automatic GROUP BY in PS Query) or as analytic functions (with the PARTITION BY clause). Here's an example of the syntax for my favorite, but I believe the guts of each function are the same other than the first word.

ROW_NUMBER() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], ...
)

The key difference between the three is how they handle "ties" when the data you're ranking is identical between two or more rows.

• ROW_NUMBER() - no ties, every row gets a unique number (1, 2, 3, 4)
• RANK() - ties get the same number, then skips (1, 2, 2, 4)
• DENSE_RANK() - ties get the same number, no skipping (1, 2, 2, 3)

Hope that helps!

Ideally I want 4 rows - and for there to be a number in the unique transaction that says a row number.

When I add ROWNUM to the end of my transaction number I get 6 rows.... because they are no longer summing up.

Steph

Stephanie Zimmerman '01

Student Financial Services

IT Tech Associate-Systems Analyst

szimmer2@ilstu.edu

309.438.5164

Restorative - Command - Ideation - Intellection - Achiever

As Jeffrie said, seeing some screenshots or sample data would help.

Depending on what you're trying to do, you might be able to use something like DENSE_RANK. For example, the following query pulls from NAMES and gives each person a unique row number, now matter how many name types they have (so, the first person gets DENSE_RANK 1, second 2, etc.)

SELECT N.EMPLID, N.NAME_TYPE, N.NAME, DENSE_RANK() OVER (ORDER BY N.EMPLID) AS row_dense_rank
FROM PS_NAMES N
WHERE N.EMPLID IN ('1234567', '2345678', '3456789')
  AND N.EFFDT = (SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED WHERE N_ED.EMPLID = N.EMPLID AND N_ED.NAME_TYPE = N.NAME_TYPE AND N_ED.EFFDT <= SYSDATE)
ORDER BY N.EMPLID, N.NAME_TYPE;


Sample output from the above:

1234567  PRF  Doe, Jane  1
1234567  PRI  Doe, Jane  1
2345678  PRF  Doe, John  2
2345678  PRI  Doe, John  2
3456789  PRF  Doe, Judy  3
3456789  PRI  Doe, Judy  3

RANK() works similarly except it gives results of 1, 1, 3, 3, 5, 5

I have an expression that I want to make unique

'INV'|| NEXT_DAY(SYSDATE - 7, 'SUNDAY')||'_'||A.ITEM_TYPE||'_'||A.EMPLID||'_'||ROWNUM

I want it to only have the rownum after it sums up the amount for a student. 

Example student A got a 50.00 scholarship, then another row removed the 50.00 scholarship. So a net of zero. I don't want to show the person who has a net of zero - but by my expression - it gives me every row of data..... 

Is there a way to get what I want from Query Manager?