我正在尝试将sql 11从Oracle 11 G移植到Netezza.
这是我在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')
当我在Netezza中运行它时出现错误：
ERROR: (2) This form of correlated query is not supported - consider rewriting
如何为Netezza重写此查询？请帮忙.
我建议你重写代码：
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')
