CODEWITHSUNDEEP
oracle-databasenetezza
ozzboy
ozzboy

Reputation: 2720

Sub-Queries in Netezza

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.

Upvotes: 5

Views: 7674

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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')

Upvotes: 1

Niederee
Niederee

Reputation: 4295

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')

Upvotes: 2

Related Questions