rewrite self joins using analytic functions

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Thu, 18 Mar 2010 09:10:59 +0000 (GMT)
Message-ID: <295058.39869.qm_at_web29010.mail.ird.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 18 2010 - 04:10:59 CDT