Oracl[10g]の SQL文について(No.8)

質問

何度も恐縮です。前回の質問の続きとなるかと思いますが・・・
Oracl[10g]の SQL文について、教えて下さい。

[A]テーブルに [CODE](key) と [NAME]フィールドがあるとします。
内容は、
A-1 NAME-1
A-2 NAME-2
A-3 NAME-3 と、します。

[A2]テーブルに[NO](key) [CODE1] と [CODE2] と [CODE3]フィールドがあるとします。
内容は、
1 A-1 A-2 (null)
2 A-2 A-1 A-3
3 (null)A-3 A-2 と、します。

結果が、
1 A-1 NAME-1 A-2 NAME-2 (null) (null)
2 A-2 NAME-2 A-1 NAME-1 A-3 NAME-3
3 (null/null)A-3 NAME-3 A-2 NAME-2

と、なる様にする為のSQL文は、
>select a2.no, a2.code1,a_1.name, a2.code2,a_2.name, a2.code3,a_3.name
>from a2,
>(select code, name from a) a_1,
>(select code, name from a) a_2,
>(select code, name from a) a_3
>where
>a2.code1 = a_1.code(+) and
>a2.code2 = a_2.code(+) and
>a2.code3 = a_3.code(+)

と、教えて頂き、完成致しました。

これを、アレンジして、

SELECT A2.NO, A2x.N1, A2x.N2, A2x.N3
FROM A,A2 ,
(
select a2.no, a2.code1,a1x.name AS N1, a2.code2,a2x.name AS N2, a2.code3,a3x.name AS N3
from a2,
(select code, name from a) a1x,
(select code, name from a) a2x,
(select code, name from a) a3x
where
a2.code1 = a1x.code(+) and
a2.code2 = a2x.code(+) and
a2.code3 = a3x.code(+)
) A2x
WHERE A2.NO='1'
GROUP BY A2.NO, A2x.N1, A2x.N2, A2x.N3
ORDER BY A2.NO

と、作りました。つまり、

1 A-1 NAME-1 A-2 NAME-2 (null) (null)

と、言う結果が欲しいのです。

ところが、
1 (null) NAME-3 NAME-2
1 NAME-1 NAME-2 (null)
1 NAME-2 NAME-2 NAME-3

と、3行も出てしまいます。
よろしくお願い致します。

回答No.1

jch · ベストアンサー

SELECT K.NO,K.N1,K.N2,K.N3 FROM (SELECT B.NO NO,B.CODE_1,A1.NAME N1,B.CODE_2,A2.NAME N2,B.CODE_3,A3.NAME N3
FROM B,(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A1,
(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A2,
(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A3
WHERE A1.CODE(+) = B.CODE_1 AND A2.CODE(+) = B.CODE_2 AND A3.CODE(+) = B.CODE_3) K
WHERE K.NO='1'
GROUP BY K.NO, K.N1, K.N2, K.N3
ORDER BY K.NO

結果
1 NAME-1 NAME-2 NULL

お礼

落ち着いて、BをA2に置き換えるとできました。
すみません。

つまり、
SELECT K.NO,K.N1,K.N2,K.N3 FROM
(SELECT a2.NO NO,a2.CODE1,A1.NAME N1,a2.CODE2,A2.NAME N2,a2.CODE3,A3.NAME N3
FROM a2,
(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A1,
(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A2,
(select code, DECODE(CODE,NULL,NULL,NAME) NAME from A) A3
WHERE A1.CODE(+) = a2.CODE1 AND A2.CODE(+) = a2.CODE2 AND A3.CODE(+) = a2.CODE3) K
WHERE K.NO='1'
GROUP BY K.NO, K.N1, K.N2, K.N3
ORDER BY K.NO

と、なりました。1つ質問なのですが、
>DECODE(CODE,NULL,NULL,NAME)
この部分は、A2テーブルにCODE1-3と、3つある為で例えば5つならば、
[DECODE(CODE,NULL,NULL,NULL,NULL,NAME)]
と、なるのでしょうか ? 宜しくお願い致します。

補足

せっかく、ご回答を頂いたのですが、動きません。
「"B"."CODE_3":無効な識別子です」とエラーに
なります。今回、Bテーブルは無いのでA2テーブルに
置き換えて見ましたが出来ません。
続けてのご指導をお願い致します。

回答No.4

k_o_r_o_c_h_a_n

どう書いて良いか難しいのですが・・

最初の投稿にあった”教えて頂いた”ＳＱＬを基にして
書きましたが、このＳＱＬは、項目名に対し別名を付けて
いないが為、サブクエリ化すると、別名エラーが出るようです。

SELECT * FROM (
select b.no, b.code1,a_1.name NAME1, b.code2,a_2.name NAME2, b.code3,a_3.name NAME3
from tbl_b b,
(select code,name from tbl_a) a_1,
(select code,name from tbl_a) a_2,
(select code,name from tbl_a) a_3
where
b.code1 = a_1.code(+) and
b.code2 = a_2.code(+) and
b.code3 = a_3.code(+)
)
WHERE NO=1
;

のように、列名が同じにならないように、別名を付けて
サブクエリ化すれば、「列の定義が未確定です」とは
なりません。

お礼

ありがとうございました。お蔭様で、解決致しました。
今後とも、宜しくお願い致します。

回答No.3

k_o_r_o_c_h_a_n

＃２ですが、補足へのレスです。

＞教えていただいたSQL文ですが、「列の定義が未確定です」と、

これは、あなたの投稿した最初のＳＱＬが、元々「列の定義が未確定です」といわれる
間違ったＳＱＬなのか、検証するための転記で、記述ミスしているのかいずれかです。

select *
from
(
投稿ＳＱＬ
)
where 投稿ＳＱＬの結果の第１フィールド = '1'

としているので、改造部分でのエラーはありえません。

＞この方が、実行も早いと考えて良いのでしょうか ?

無駄な結合をして速くなることなどありえない。

補足

なんどもすみません。ありがとうございます。
>select *
>from
>(
>投稿ＳＱＬ
>)
>where 投稿ＳＱＬの結果の第１フィールド = '1'

投稿ＳＱＬをそのままコピーして実行しました。エラーは出ませんが
結果は、やはり3行になってしまいました。

出来れば、続けてのご指導を頂ければ幸いです。

回答No.2

k_o_r_o_c_h_a_n

SELECT A2.NO, A2x.N1, A2x.N2, A2x.N3
FROM A,A2 ,
(
　：
) A2x
WHERE A2.NO='1'　　　　　　　　　　　　 <== ココ注目！
GROUP BY A2.NO, A2x.N1, A2x.N2, A2x.N3
ORDER BY A2.NO

FROMに、サブクエリと実表の複数を書いているのに、
その結合条件を、何故書かないのですか？
適切な結合条件を書くことで、望む結果は得られると思います。

もっとも、どなたかに、教わったＳＱＬを抽出したいだけとも解釈できるので．．．

SELECT * FROM (
select a2.no, a2.code1,a_1.name, a2.code2,a_2.name, a2.code3,a_3.name
from a2,
(select code, name from a) a_1,
(select code, name from a) a_2,
(select code, name from a) a_3
where
a2.code1 = a_1.code(+) and
a2.code2 = a_2.code(+) and
a2.code3 = a_3.code(+)
)
WHERE NO=1

と書く方が、無駄な結合を増やさなくて済みます。

補足

ご回答をありがとうございます。

>もっとも、どなたかに、教わったＳＱＬを
>抽出したいだけとも解釈できるので．．．
その通りです。(^^;

教えていただいたSQL文ですが、「列の定義が未確定です」と、エラーになってしまいます。自分で考えたのですが、わかりません。すみません。

それと、
>と書く方が、無駄な結合を増やさなくて済みます。
この方が、実行も早いと考えて良いのでしょうか ?

続けての、ご指導をお願い致します。