RE: rewrite self joins using analytic functions

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Thu, 18 Mar 2010 14:11:48 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415934CE008925C_at_GVW1337EXC.americas.hpqcorp.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of hrishy Sent: Thursday, March 18, 2010 5:11 AM
To: oracle-l_at_freelists.org
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

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

Received on Thu Mar 18 2010 - 09:11:48 CDT