Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Oracle Join Query

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
chokka
Asked:
chokka
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>> 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
 
chokkaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
chokkaAuthor Commented:
Thanks !!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now