Returning only the latest records with ROW_NUMBER()
Hi everybody,
I am trying to put togther a query that does the following:
1. Takes an numerical input to calculate a date based from todays date
2. Find orders from OPORDHD past with records in the DOCSCHECKS table prior to this date
Where I get confused is specifying more advanced criterea for stage 2. I want to only return the rows from OPORDHD relative to the latest revision number and the status_id field being 'PE'. This what I am trying to do with the ROW_NUMBER anaslytic function but its not working as expected.
This is what I have so far
SELECT * FROM
(SELECT DISTINCT
TRIM(o.ORDER_NUM) ORDER_NO,
TRIM(o.INTRNL_REF) PO,
TRIM(o.CUSTOMR_RF) REFERENCE,
TRIM(o.CUSTOMR_ID) CUSTOMER_ID,
TRIM(cu.CUST_NAME) CUSTOMER,
TRIM(o.CREATED_DT) DATE_CREATED,
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST,ch.chase_date DESC NULLS LAST) RN
FROM OPORDHD o INNER JOIN SLCUSTX cu ON cu.CUSTOMR_ID = o.CUSTOMR_ID
LEFT OUTER JOIN docsmanagement m ON m.order_no = trim(o.ORDER_NUM)
LEFT JOIN DOCSCHECKS c ON c.ORDER_NO = o.ORDER_NUM
LEFT JOIN DOCSCHASES ch on c.check_id = ch.check_id
WHERE cu.ledger_id = 'SL'
AND ((UPPER(o.ORDER_NUM) LIKE 'AM' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AX' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AS' || '%'))
AND ((SELECT to_date(sysdate - numtodsinterval(:RESPONSE,
AND c.status_id = 'PE')) WHERE RN = 1
And I would want to return AM9999 from the dataset below as one of its DOC_ID's highest REVISION has the STATUS_ID value of 'PE'
Sorry I know this is a bit confusing, hard for me to explain what I am trying to do. Thanks eveyone.
I am trying to put togther a query that does the following:
1. Takes an numerical input to calculate a date based from todays date
2. Find orders from OPORDHD past with records in the DOCSCHECKS table prior to this date
Where I get confused is specifying more advanced criterea for stage 2. I want to only return the rows from OPORDHD relative to the latest revision number and the status_id field being 'PE'. This what I am trying to do with the ROW_NUMBER anaslytic function but its not working as expected.
This is what I have so far
SELECT * FROM
(SELECT DISTINCT
TRIM(o.ORDER_NUM) ORDER_NO,
TRIM(o.INTRNL_REF) PO,
TRIM(o.CUSTOMR_RF) REFERENCE,
TRIM(o.CUSTOMR_ID) CUSTOMER_ID,
TRIM(cu.CUST_NAME) CUSTOMER,
TRIM(o.CREATED_DT) DATE_CREATED,
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST,ch.chase_date DESC NULLS LAST) RN
FROM OPORDHD o INNER JOIN SLCUSTX cu ON cu.CUSTOMR_ID = o.CUSTOMR_ID
LEFT OUTER JOIN docsmanagement m ON m.order_no = trim(o.ORDER_NUM)
LEFT JOIN DOCSCHECKS c ON c.ORDER_NO = o.ORDER_NUM
LEFT JOIN DOCSCHASES ch on c.check_id = ch.check_id
WHERE cu.ledger_id = 'SL'
AND ((UPPER(o.ORDER_NUM) LIKE 'AM' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AX' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AS' || '%'))
AND ((SELECT to_date(sysdate - numtodsinterval(:RESPONSE,
AND c.status_id = 'PE')) WHERE RN = 1
And I would want to return AM9999 from the dataset below as one of its DOC_ID's highest REVISION has the STATUS_ID value of 'PE'
Sorry I know this is a bit confusing, hard for me to explain what I am trying to do. Thanks eveyone.
DOCSCHECKS
CHECK_ID DOC_ID ORDER_NO REVISION STATUS CHECK_REQUESTED CUST_ID
100 1 AM12345 NULL NS TEST1
101 1 AM12345 0 PE TEST1
102 1 AM12345 1 A TEST1
100 2 AM12345 NULL NS TEST1
101 2 AM12345 0 PE TEST1
102 2 AM12345 1 A TEST1
103 2 AM99999 0 AC TEST2
104 2 AM99999 1 AC TEST2
105 2 AM99999 2 PE TEST2
OPORDHD
ORDER_NUM Other order related rows
AM12345
AM99999
SLCUSTX
CUST_ID Other customer related rows
TEST1
TEST2
SELECT * FROM
(SELECT DISTINCT
TRIM(o.ORDER_NUM) ORDER_NO,
TRIM(o.INTRNL_REF) PO,
TRIM(o.CUSTOMR_RF) REFERENCE,
TRIM(o.CUSTOMR_ID) CUSTOMER_ID,
TRIM(cu.CUST_NAME) CUSTOMER,
TRIM(o.CREATED_DT) DATE_CREATED,
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST,ch.chase_date DESC NULLS LAST) RN
FROM OPORDHD o INNER JOIN SLCUSTX cu ON cu.CUSTOMR_ID = o.CUSTOMR_ID
LEFT OUTER JOIN docsmanagement m ON m.order_no = trim(o.ORDER_NUM)
LEFT JOIN DOCSCHECKS c ON c.ORDER_NO = o.ORDER_NUM
LEFT JOIN DOCSCHASES ch on c.check_id = ch.check_id
WHERE cu.ledger_id = 'SL'
AND ((UPPER(o.ORDER_NUM) LIKE 'AM' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AX' || '%') OR (UPPER(o.ORDER_NUM) LIKE 'AS' || '%'))
AND ((SELECT to_date(sysdate - numtodsinterval(:RESPONSE, 'DAY')) from dual) >= c.check_requested
AND c.status_id = 'PE')) WHERE RN = 1
Naveen Kumar
what is the current output it is giving for you ?
ASKER
its retunring more records than it should, from the example dataset I would only want AM99999 returned
ASKER
at the momnet it returns every order number that has a DOCCEHCKS entry with STATUS = 'PE' regardless of the REVISION VALUE. I want only the highest revision value per doc_id to trigger this.
in your where clause you have "c.status_id = 'PE' " , so your query should get only those records which satisfy this right.
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST,ch.chase_date DESC NULLS LAST) RN --> it will get a single record for each doc_id, order_no --> can you please confirm if this is the case.
Then we will see why it is not getting the revision which you wanted otherwise first we need to understand the results produced by it before we move further.
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST,ch.chase_date DESC NULLS LAST) RN --> it will get a single record for each doc_id, order_no --> can you please confirm if this is the case.
Then we will see why it is not getting the revision which you wanted otherwise first we need to understand the results produced by it before we move further.
ASKER
That is correct use of ROW_NUMEBR, it works great with other queries I use it in but cant seem to get it going properly with this one
to debug the query, add revision column also just before the row_number query and also remove where RN=1 and first see the output whether RN column is all looking fine. then have the outer query with RN=1 and test it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please provide DOCSCHASES table structure and data as u have not mentioned it
ASKER
DOCSCHASES
CHASE_ID
CHECK_ID
CHASE_DATE
CHASE_BODY
USERNAME
CHASE_SERIAL
USER_ID
This stores records of when the document was 'chased', its in the ROW_NUMBER AF to extrcat the most recent date it was chased
CHASE_ID
CHECK_ID
CHASE_DATE
CHASE_BODY
USERNAME
CHASE_SERIAL
USER_ID
This stores records of when the document was 'chased', its in the ROW_NUMBER AF to extrcat the most recent date it was chased
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There was a data integrity lapse, adding more fields to the row_number sorted it out. thanks everyone