Friday, December 6, 2013

Rewrite correlated subqueries in cognos compatible way. Decorrelating subqueries


Correlated subquery scenario 1

SELECT c.LastName, c.FirstName,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c


Rewrite to

SELECT c.LastName, c.FirstName, cm.CompanyName
FROM Customer c LEFT OUTER JOIN Company cm
ON c.CompanyID = cm.CompanyID




Correlated subquery scenario 2
select *
from T1
where T1.> (select max(T2.a) from T2 where T2.b = T1.b)


Rewrite to

select T1.*
from T1, (select T2.b, max(T2.a) max_a from T2 group by T2.b) S
where T1.= S.and T1.> S.max_a

No comments:

Post a Comment