RE: rewrite self joins using analytic functions

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 18 Mar 2010 14:11:48 +0000

I often cannot see the obvious but I do not see where any of the analytic 
functions would be applicable to this query.

Are the where clause conditions where tab1.col1 and tab1.col2 are both tsted 
equal againt tab2.col2 correct?  Possible and not really material to answering 
your question but this caught my eye and I can't get past it.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of hrishy
Sent: Thursday, March 18, 2010 5:11 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: rewrite self joins using analytic functions

Hi

Any ideas on how to re-write this self join using Analytical functions

SELECT
tab2.col1 ,
tab2.col2
sum(tab2.col3)
count(*)
FROM
tab1,tab2
WHERE tab1.col1      = tab2.col2
AND   tab1.col2  =   tab2.col2
AND   tab1.col4         = tab2.col3
AND   tab2.col3     < tab1.col3
AND tab2.col5         = 3
AND tab1.col5          = 4
AND tab2.col6 NOT IN(303,321)
AND tab1.col6     not in (322)
GROUP BY tab2.col1,tab2.col2


Other related posts: