副問い合わせ

今日もエラーが出ました。

ORA-01799: 列は副問合せに対して外部結合されません。

下のように解決しました。 (使っているデータベースは Oracle 10g か 11g のどっちかでした。)

事例 1

エラーが出たのは次の結合条件。

LEFT JOIN JNYUKA FNY
ON FNY.BUNNO = (SELECT MAX(SNY.BUNNO)
FROM JNYUKA SNY
WHERE SNY.BUNNO <= MAIN.BUNNO)

LEFT JOIN JNYUKA FNY

ON FNY.BUNNO = (SELECT MAX(SNY.BUNNO)

FROM JNYUKA SNY

WHERE SNY.BUNNO <= MAIN.BUNNO)

LEFT JOIN で結合するテーブルの結合条件に副問合せをつかうと駄目みたいです。

期待する結果

上のコードでやりたいのは、 JNYUKA.BUNNO がメインテーブルの値(MAIN.BUNNO)以下になっているもので、最大のものをとることです。下のように。

MAIN BUNNO	JNYUKA BUNNO	FNY BUNNO
1	1	1
2	2	2
3		2

上のコードではエラーがでます。 カーソルを使っていったんテーブルに入れるのも面倒です。そこで・・・

LEFT JOIN JNYUKA FNY
ON FNY.BUNNO <= MAIN.BUNNO
AND NOT EXISTS(SELECT 1
FROM JNYUKA SNY
WHERE SNY.BUNNO > FNY.BUNNO
AND SNY.BUNNO <= MAIN.BUNNO)

LEFT JOIN JNYUKA FNY

ON FNY.BUNNO <= MAIN.BUNNO

AND NOT EXISTS(SELECT 1

FROM JNYUKA SNY

WHERE SNY.BUNNO > FNY.BUNNO

AND SNY.BUNNO <= MAIN.BUNNO)

このようにすればエラーはでません。 BUNNOがメインテーブル以下のものをすべて結合した後で、最大のもののみに絞り込むイメージ。

事例 2

別の例があったので・・・

Oracleで子テーブルの最新を外部結合できない！？で次のコードを見つけました。

select member.*
from MEMBER member
left outer join MEMBER_LOGIN memberLogin
on member.MEMBER_ID = memberLogin.MEMBER_ID
and memberLogin.LOGIN_DATETIME
= (select max(MEMBER_LOGIN.LOGIN_DATETIME)
from MEMBER_LOGIN
where MEMBER_LOGIN.MEMBER_ID = 
member.MEMBER_ID)

select member.*

from MEMBER member

left outer join MEMBER_LOGIN memberLogin

on member.MEMBER_ID = memberLogin.MEMBER_ID

and memberLogin.LOGIN_DATETIME

= (select max(MEMBER_LOGIN.LOGIN_DATETIME)

from MEMBER_LOGIN

where MEMBER_LOGIN.MEMBER_ID =

member.MEMBER_ID)

この場合だと

select member.*
from MEMBER member
left outer join MEMBER_LOGIN memberLogin
on member.MEMBER_ID = memberLogin.MEMBER_ID
and not exists(select MEMBER_LOGIN.MEMBER_LOGIN_DATATIME
from MEMBER_LOGIN
where MEMBER_LOGIN.MEMBER_ID = 
member.MEMBER_ID
and memberLogin.LOGIN_DATATIME <
MEMBER_LOGIN.LOGIN_DATATIME)