Solved

Oracle Join Query

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 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

13 Experts available now in Live!

Get 1:1 Help Now