troubleshooting Question

Oracle SQL - Join by nearest date

Avatar of holemania
holemania asked on
Oracle DatabaseSQL
9 Comments1 Solution2376 ViewsLast Modified:
I am not sure if this is possible, but I need help with a nested query.  I have a transactional table and all transactions happened within this table.  I want to join the nested table with the main table and display the "Order" field.  I can use the "Part" to link, but it is return more records and not give me the correct value.  So next is compared it by the closest date.

Example, the main query if the date is 2/1/2011 look at the nested result and if anything that is equal or closest date and the "Main Component" match then use that one .  So in the screenshot, the 2nd diagram the next closest date is 7/29/2011 and "Main Component" is 7788-0111.  Then it's order 9468.  Next one is 7/15/2013 from main query.  Look at nested query and next closest date is 10/29/2013 and "Main Component" is 998832-110, then use that one.  Make sense?

Example
So here's my query:

SELECT  T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART MAIN_COMPONENT, X.ORDER
FROM    TRANS_DETAIL T LEFT OUTER JOIN WORKORDER W ON T.TASK = W.TASK

        LEFT OUTER JOIN
        (
        SELECT  T.TRANS_DATE TRX_DATE, T.ORDER, T.LINE_NO, T.PART MAIN_COMPONENT
        FROM    TRANS_DETAIL T
        ) X ON X.MAIN_COMPONENT = W.PART AND X.TRX_DATE > T.TRANS_DATE
        
WHERE T.PART = 'VX112045'

The above query return everything after the first date and that's not what I want.  I only want it to return the closest date and that's it.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros