CODEWITHSUNDEEP
netezzacorrelated-subquery
user2740397
user2740397

Reputation: 31

correlated Queries in Netezza

How will I re-write co-related query in Netezza database when I have a query like below in oracle database...

select table1.column1,
case when table2.column2='xxxx' then
             ( select max(table3.column3) 
                  from table3  
                  where table3.column4=4 
                    and table3.column2=table1.column2 and
                        table3.column3=table4.column4
              ) 
else null
end
from table1,table2, table4.

Upvotes: 0

Views: 928

Answers (1)

Niederee
Niederee

Reputation: 4295

Consider changing the structure.

SELECT table1.column1,
CASE WHEN table2.column2='xxxx' THEN
sub_tbl3.mx
ELSE NULL
END
FROM table1 inner join
     table2
     ON table1.column1=table2.column1 inner join
    table4
    ON table1.column2=table4.column4 left outer join
(SELECT table3.column2,
     table3.column3,
      MAX(table3.column3) AS mx
FROM table3
WHERE table3.column4=4
GROUP BY  table3.column2,
     table3.column3) sub_tbl3
ON table3.column2=table1.column2
AND table3.column3=table4.column4

Upvotes: 1

Related Questions