■ エラー内容
SQL : ORA-01799: 列は副問合せに対して外部結合されません。
エラーが発生したSQL
SELECT
S2.YEAR AS PREV_YEAR,
S1.YEAR AS YEAR,
S2.SALE AS PREV_SALE,
S1.SALE AS SALE,
S1.SALE - S2.SALE AS DIFF,
CASE WHEN S1.SALE = S2.SALE THEN '→'
WHEN S1.SALE > S2.SALE THEN '↑'
WHEN S1.SALE < S2.SALE THEN '↓'
ELSE '-' END AS UP_OR_DOWN
FROM
SALES S1
LEFT OUTER JOIN
SALES S2
ON
S2.YEAR = (SELECT MAX(YEAR)
FROM SALES S3
WHERE S1.YEAR > S3.YEAR)
ORDER BY YEAR
発生環境
* OS : Windows10
* DB : Oracle 11g
■ 原因
Oracle は、 外部結合するテーブルの結合条件に副問合せが使えない
■ 解決案
* 「NOT EXISTS」を使う
SELECT
S1.YEAR AS YEAR,
CASE WHEN S1.SALE = S2.SALE THEN '→'
WHEN S1.SALE > S2.SALE THEN '↑'
WHEN S1.SALE < S2.SALE THEN '↓'
ELSE '-' END AS UP_OR_DOWN,
S1.SALE AS SALE,
S2.SALE AS PREVIOUS_SALE,
S1.SALE - S2.SALE AS DIFF
FROM
SALES S1
LEFT OUTER JOIN
SALES S2
ON
S2.YEAR < S1.YEAR
AND NOT EXISTS(SELECT 1 FROM SALES S3
WHERE S3.YEAR > S2.YEAR AND S3.YEAR < S1.YEAR)
ORDER BY YEAR;
