Solved

Oracle Join Query

Posted on 2015-01-21
4
335 Views
Last Modified: 2015-01-24
I have to write a Join query between two tables - Tcusphs and TEMP_CUSTHIS for the matching : CUS_SYS_NR.

Tricky part is .. In the TEMP_CUSTHIS Table, we have more than one row of same CUS_SYS_NR with date field
 ( TIMESTAMP ), I have to compare with most recent date of that specific CUS_SYS_NR  AND TO_CUST_NR .

If the TO_CUST_NR of the TEMP_CUSTHIS for most recent date of a specific CUS_SYS_NR is not matching with TCUSPHS, i have to return those records from TCUSPHS table.

I have attached the sample data. I feel this is a most challenging Oracle Join Query.
C--Users-ZHQ8HPV-Desktop-TEMP-CUSTHIST.x
C--Users-ZHQ8HPV-Desktop-TCUSPHS.xls
0
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40563046
>> I have to compare with most recent date of that specific

this is simple:
select CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA
FROM (
   select CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA,
   ROW_NUMBER() OVER(partition by CUS_SYS_NR order by CUS_SYS_NR, TIMESTAMP DESC) MYRN
   from TEMP_CUSTHIS
)
where myrn=1

Open in new window



Then you can use that as an inline view for your join (UNTESTED):
select something from
TCUSPHS
JOIN
(
select CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA
FROM (
   select CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA,
   ROW_NUMBER() OVER(partition by CUS_SYS_NR order by CUS_SYS_NR, TIMESTAMP DESC) MYRN
   from TEMP_CUSTHIS
)
where myrn=1
) MY_TEMP_CUSTHIS
ON <<some join conditions>>

Open in new window


I'm not sure what columns you will be joining on so you'll need to figure out this piece in the SQL above:  <<some join conditions>>

If you want a copy/paste solution, please provide expected results based on the sample data you posted.
0
 

Author Comment

by:chokka
ID: 40563282
Thank you !!

Inner Query - Returns around 3600 Records.

SELECT CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA
FROM (
   SELECT CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA,
   ROW_NUMBER() OVER(PARTITION BY CUS_SYS_NR ORDER BY CUS_SYS_NR, TIMESTAMP DESC) MYRN
   FROM TEMP_CUSTHIST
)
WHERE MYRN=1

Open in new window






On Joining with outer query..

SELECT TCUSPHS.CUS_SYS_NR FROM
TCUSPHS
JOIN
(

SELECT CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA
FROM (
   SELECT CUS_SYS_NR, TIMESTAMP, CURR_VIEW ,TRANS_TYPE, FROM_CUST_NR, FROM_CUST_NA, TO_CUST_NR, TO_CUST_NA,
   ROW_NUMBER() OVER(PARTITION BY CUS_SYS_NR ORDER BY CUS_SYS_NR, TIMESTAMP DESC) MYRN
   FROM TEMP_CUSTHIST
)
WHERE MYRN=1
) MY_TEMP_CUSTHIS

ON   TCUSPHS.CUS_SLS_CSF_CD != MY_TEMP_CUSTHIS.TO_CUST_NR
AND TCUSPHS.DAT_CX_TM_PRD_NR = 0
AND MY_TEMP_CUSTHIS.TIMESTAMP >= '01-JAN-2014' 
AND MY_TEMP_CUSTHIS.TIMESTAMP <= '30-DEC-2014' 

Open in new window



Query returns duplicate CUS_SYS_NR which goes more than 100,000 .. Expected result is further filteration from 3000 which should be less than 1000 or around 300
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40563478
>>Inner Query - Returns around 3600 Records.

Are they the 'correct' records?  If not, no point in going on.

>>Expected result is further filteration from 3000 which should be less than 1000 or around 300

That does not help.  I need what you expect based on the data you provided.
or
Provide more data and the expected results from that.

My guess is you are missing a join column.
0
 

Author Closing Comment

by:chokka
ID: 40568699
Thanks !!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question