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.a > (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.b = S.b and T1.a > S.max_a