sqlについての質問

質問

お世話になります。
下記コードにて、抽出されたデータに不備があります。
「q2.新新品番(+) = A.品番」としていますが、
完全一致しない品番が見受けられ、ｔｒｉｍを使ったら
良いのではと思うのですが、どこにtrimを入れたら
良いのか、又は考え方自体違うのか
現在テンパっている所でございます。
どなたか良いアドバイスを頂けたらと思い、
ご質問させて頂きました。
何卒、ご教示頂けます様、宜しくお願い致します。

　　　　　　　　　記

select case
when substr(q2.新新品番,8,1)='_' then
substr(q2.新新品番,1,7)
else
q2.新新品番
end as 品番
,SUM(q2.新新合格数) AS 生産合格数
,A.伝票ID,A.品番,A.新入庫数量 as 区画入庫数,A.区画番号
from
(
select case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end as 新新品番
,SUM(q1.新合格数) AS 新新合格数
from
(
SELECT 更新日時,
case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end as 新品番
,SUM(合格本数) AS 新合格数
FROM D最終検査履歴
WHERE 更新日時 BETWEEN '2012-5-31' and '2012-6-01'
group by 更新日時, case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end
)q1
group by case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end
)q2 ,
(select 伝票ID,品番,sum(入庫数量)as 新入庫数量,区画番号
from D屋外入庫
where 登録日時 between '2012-5-31' and '2012-6-01' and 伝票ID='SN'
group by 伝票ID, 品番, 区画番号
)A
where q2.新新品番(+) = A.品番
group by case
when substr(q2.新新品番,8,1)='_' then
substr(q2.新新品番,1,7)
else
q2.新新品番
end, A.伝票ID, A.品番, A.新入庫数量, A.区画番号

回答No.3

NoMusicNoLife49

こんにちは。

エラーの内容を詳しく知りたいです。

>where q2.新新品番(+) = A.品番」は、
>間違いでしょうか。
上記の結合条件であってるはずです。

回答No.2

Siegrune

>「q2.新新品番(+) = A.品番」としていますが、
>完全一致しない品番が見受けられ、ｔｒｉｍを使ったら
>良いのではと思うのですが、どこにtrimを入れたら
ということなら、
trim(q2.新新品番(+)) = A.品番
でいいはずですが。

## 以下はよけいなお世話ですけど。
1.q1とq2を分けている理由が不明。

select case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end as 新新品番
,SUM(q1.新合格数) AS 新新合格数
from
(
SELECT 更新日時,
case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end as 新品番
,SUM(合格本数) AS 新合格数
FROM D最終検査履歴
WHERE 更新日時 BETWEEN '2012-5-31' and '2012-6-01'
group by 更新日時, case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end
)q1
group by case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end
)q2 ,
ならば、
(
SELECT 更新日時,
case
when substr(q1.ハンドル,6,1)='_' then
substr(q1.ハンドル,1,5)
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end as 新新品番
,SUM(合格本数) AS 新新合格数
FROM D最終検査履歴
WHERE 更新日時 BETWEEN '2012-5-31' and '2012-6-01'
group by 更新日時, case
when substr(q1.ハンドル,6,1)='_' then
substr(q1.ハンドル,1,5)
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end
)q2 ,
と同じに見えますが。

2.いまどきは(+)よりleft(またはright) outer joinのほうが一般的では？
（コーディング規約上(+)を使わないといけないとかある場合がまだまだ多いので、
　一概には言えませんけど。）
・・・ q2 ,
(select 伝票ID,品番,sum(入庫数量)as 新入庫数量,区画番号
from D屋外入庫
where 登録日時 between '2012-5-31' and '2012-6-01' and 伝票ID='SN'
group by 伝票ID, 品番, 区画番号
)A
where q2.新新品番(+) = A.品番
は、
・・・ q2 right outer join
(select 伝票ID,品番,sum(入庫数量)as 新入庫数量,区画番号
from D屋外入庫
where 登録日時 between '2012-5-31' and '2012-6-01' and 伝票ID='SN'
group by 伝票ID, 品番, 区画番号
)A
on q2.新新品番 = A.品番

※trimつけるなら
on trim(q2.新新品番) = A.品番

3.なんか、
>完全一致しない品番が見受けられ
とある条件を正確に把握するのが先じゃないのかなと思ってしまいます。

8桁目に_なら、7桁目までで一致判定
6桁目に_なら、5桁目までで一致判定
ときて、次の_は7桁目に出て来そうな予感が・・・。

回答No.1

NoMusicNoLife49

こんにちは。

>A.伝票ID,A.品番,A.新入庫数量 as 区画入庫数,A.区画番号
の部分にNullを置換する関数とtrim関数を追加し、上位の
select文を追加すればokです。

DBの種類が解らなかったので、Nullを置換する関数とtrim関数
の名前が違っていたら修正してください。

select *
from(
select case
when substr(q2.新新品番,8,1)='_' then
substr(q2.新新品番,1,7)
else
q2.新新品番
end as 品番
,SUM(q2.新新合格数) AS 生産合格数
,trim(nvl(A.伝票ID,'')),trim(nvl(A.品番,'')),trim(nvl(A.新入庫数量,0)) as 区画入庫数,trim(nvl(A.区画番号,''))
from
(
select case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end as 新新品番
,SUM(q1.新合格数) AS 新新合格数
from
(
SELECT 更新日時,
case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end as 新品番
,SUM(合格本数) AS 新合格数
FROM D最終検査履歴
WHERE 更新日時 BETWEEN '2012-5-31' and '2012-6-01'
group by 更新日時, case
when substr(ハンドル,7,1)='_' then
substr(ハンドル,1,6)
else
ハンドル
end
)q1
group by case
when substr(q1.新品番,6,1)='_' then
substr(q1.新品番,1,5)
else
q1.新品番
end
)q2 ,
(select 伝票ID,品番,sum(入庫数量)as 新入庫数量,区画番号
from D屋外入庫
where 登録日時 between '2012-5-31' and '2012-6-01' and 伝票ID='SN'
group by 伝票ID, 品番, 区画番号
)A
where q2.新新品番(+) = A.品番
)NES
group by case
when substr(NES.新新品番,8,1)='_' then
substr(NES.新新品番,1,7)
else
NES.新新品番
end,NES.伝票ID,NES.品番,NES.新入庫数量,NES.区画番号

お礼

ご回答頂きありがとうございます。
しかしora00904のエラーが出てしまい
うまくいきませんでした。

今思いましたが、
「where q2.新新品番(+) = A.品番」は、
間違いでしょうか。

「where 品番(+) = A.品番」こうすべき
でしょうか。
今自宅なので試せませんが・・。