Sub-Queries in Netezza

Question

I am trying to port a SQL from Oracle 11 G to Netezza.

Here is my query in Oracle:

SELECT
    ID, TEACHERID, CLASS, SECTION, MAJOR, SUPERVISOR, COURSE, SCORE, SCOREDATE, 
    (select SCORE from STUDENT_SCORES d2 where d2.ID=d1.ID and d2.SCOREDATE (d1.SCOREDATE)-28 and d1.COURSE=d2.COURSE)  as PRIORSCORE, 
    (d1.SCOREDATE)-28 as PRIORSCOREDT,
    REMCD
FROM 
    STUDENT_SCORES d1
WHERE 
    TEACHERID='T1' AND SCOREDATE=(SELECT MAX(SCOREDATE) FROM STUDENT_SCORES WHERE TEACHERID='T1')

When I run this in Netezza I get an error:

ERROR:  (2) This form of correlated query is not supported - consider rewriting

How do I rewrite this query for Netezza? Please help.

Are you sure that the subquery in your `select` clause (`PRIORSCORE`) does not return multiple rows? — Rachcha, Jan 29 '14 at 14:46
Please find your answer [here](http://pic.dhe.ibm.com/infocenter/ntz/v7r0m3/index.jsp?topic=%2Fcom.ibm.nz.dbu.doc%2Fc_dbuser_correlated_subqueries_ntz_sql.html) . You cannot use correlated subqueries in `SELECT` lists. — Maheswaran Ravisankar, Jan 29 '14 at 14:56
Maheswaran Ravishankar Yes I understand I cannot use correlated subqueries but how do I rewrite this for Netezza? — ozzboy, Jan 29 '14 at 15:09
Did you get your question answered here, or are you still having issues? — JustBeingHelpful, Sep 27 '15 at 07:38

Niederee · Answer 1 · 2014-01-29T15:16:26.667

I would suggest rewriting your code to this:

select d1.ID, d1.TEACHERID, d1.CLASS, d1.SECTION, d1.MAJOR, d1.SUPERVISOR, d1.COURSE, d1.SCORE, d1.SCOREDATE
,d2.SCORE as PRIORSCORE
,d2.SCOREDATE as PRIORSCOREDT
,d1.REMCD
from STUDENT_SCORES d1 left outer join
(select * from STUDENT_SCORES ss where ss.SCOREDATE = (SELECT MAX(SCOREDATE)-28 FROM STUDENT_SCORES WHERE TEACHERID='T1') d2
on  d1.ID=d2.ID
and d1.COURSE=d2.COURSE
where d1.TEACHERID='T1'
and d1.SCOREDATE = (SELECT MAX(SCOREDATE) FROM STUDENT_SCORES WHERE TEACHERID='T1')

score 1 · Answer 2 · edited Apr 01 '15 at 20:20

As per Netezza Docs

You cannot use correlated subqueries in SET operations (UNION, INTERSECT, EXCEPT, and MINUS).
You cannot use correlated subqueries in aggregates with GROUP BY and HAVING clauses.
You cannot use correlated subqueries in ORed clauses or in CASE/WHEN expressions.
You cannot use correlated subqueries in IN lists.
You cannot use correlated subqueries in SELECT lists.

Rewritten Query: (Oracle) (Not Sure with Netezza Syntax)

SELECT
    d1.ID, d1.TEACHERID, d1.CLASS, d1.SECTION, d1.MAJOR, d1.SUPERVISOR, d1.COURSE, d1.SCORE, d1.SCOREDATE, 
    d2.SCORE as PRIORSCORE, 
    (d1.SCOREDATE)-28 as PRIORSCOREDT,
    d1.REMCD
FROM 
    STUDENT_SCORES d1
LEFT JOIN STUDENT_SCORES d2
ON (d2.ID=d1.ID and d2.SCOREDATE = (d1.SCOREDATE)-28 and d1.COURSE=d2.COURSE)
WHERE 
    d1.TEACHERID='T1' AND d1.SCOREDATE=(SELECT MAX(d3.SCOREDATE) FROM STUDENT_SCORES d3 WHERE d3.TEACHERID='T1')

Sub-Queries in Netezza

2 Answers2

Linked