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, 'DAY')) from dual) >= c.check_requested
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

Open in new window

LVL 2
wellsoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the current output it is giving for you ?
0
wellsoAuthor Commented:
its retunring more records than it should, from the example dataset I would only want AM99999 returned
0
wellsoAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

0
wellsoAuthor Commented:
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
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
why don't you give some sample output records in an excel file or something for me to understand what is that returning currently but without the RN=1 so i can see the complete result set and find something for you.
0
Shaju KumbalathDeputy General Manager - ITCommented:
Please provide DOCSCHASES  table structure and data as u have not mentioned it
0
wellsoAuthor Commented:
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
0
Shaju KumbalathDeputy General Manager - ITCommented:
please provide all table details
docsmanagement,
OPORDHD is still missing if possible provide some sample data
0
sridharthirunagariCommented:
When you said you wanted to see the row
"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' " .

Then your below WHERE clause statement condition is not getting satisfied, may be.

"AND ((SELECT to_date(sysdate - numtodsinterval(:RESPONSE, 'DAY')) from dual) >= c.check_requested  


AND c.status_id = 'PE'))"

So it would be easy if you could check whether the WHERE clause conditions are being satisfied by the records you are expecting to show-up.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wellsoAuthor Commented:
There was a data integrity lapse, adding more fields to the row_number sorted it out. thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.