Solved

Oracle Join Query

Posted on 2015-01-21
4
305 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 76

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 76

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 39
automatic email alert 1 42
Trigger usage 2 59
Oracle - SQL Parse String 5 19
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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 what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now