Solved

Oracle Join Query

Posted on 2015-01-21
4
322 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

730 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