QA@IT

【SQL】「レコードなし」を条件にしたい

17099 PV

お世話になっております。
以下のような動作を実現するSQL文を作成したいのですが、うまくいきません。
テーブルA、テーブルBがあり、テーブルAを利用したシステムへの初回入力の際、画面に
テーブルBの結果値を初期展開する。2回目以降は日付の新しい方の結果値を初期展開する。

うまくいかないSQL文
with ビュー1 as (select 結果値, 日付 from テーブルA),
ビュー2 as (select 結果値, 日付 from テーブルB)
select case
when exists (select * from ビュー1)
then case
when ビュー1.日付 > ビュー2.日付
then ビュー1.結果値
else ビュー2.結果値
end
else ビュー2.結果値
end as '結果値'
from ビュー1, ビュー2;

テーブルAに1行でもレコードが追加されれば、日付を比較し、新しい方の結果値を
初期展開する動きをしてくれるのですが、1度も入力がない状態(レコードなし)だと
ビュー2.結果値を出力するのではなく、選択行なしで出力されます。
exists (select * from ビュー1)を(select count(*) from ビュー1) > 0に
しても同じでした。
Oracle 11g & SQL Developerの環境でSQL文を作成しておりますが、エラーを出す
わけでもなく、どこでコケているのかわかりません。
テーブルAが「選択行なし」の時、テーブルBの結果値を出力するにはどうしたら
良いでしょう?

以上よろしくお願いします。

回答

レコードなしを条件にできないのではなくて、From句以降でもたらされる選択対象がそもそも 0件という状態になっています。

元のSQLにおいて、Where句や Join~On が指定されていないのでそれはCross Joinになります。
Cross Joinは掛け算ですので、双方に最低でも 1件はレコードがなければ結果は生成されません。

つまりテーブルAまたはテーブルBが0件であれば以下は0件です。

with ビュー1 as (select 結果値, 日付 from テーブルA),
     ビュー2 as (select 結果値, 日付 from テーブルB)
select * from ビュー1, ビュー2

これは以下と等価になります。

with ビュー1 as (select 結果値, 日付 from テーブルA),
     ビュー2 as (select 結果値, 日付 from テーブルB)
select * from ビュー1 cross join ビュー2

この状況でどのような条件を足しても from で提供される対象が 0件なのですから、結果は 0件です。

そもそも、このSQLではテーブルAの件数 × テーブルBの件数となってしまいます。
よくあるケースでは結合条件を指定して ビュー1の件数に依らず ビュー2がすべて出力されるような外部結合とするパターンが多いでしょう。
ただ、papidoggyさんが あとで回答として追加されたSQLを見るに、ビュー1は0件か1件しかあり得ない(2件以上はエラーです)様ですね。

ビュー2の全件の日付を ビュー1の0件または1件の日付と比べたいなら

with ビュー1 as (select '1' keyForJoin, 結果値, 日付 from テーブルA),
     ビュー2 as (select '1' keyForJoin, 結果値, 日付 from テーブルB)
select 
  case 
    when ビュー1.日付 is null
      then ビュー2.結果値
    when ビュー1.日付 > ビュー2.日付
      then ビュー1.結果値
    else ビュー2.結果値
  end as "結果値"
from ビュー2
left outer join ビュー1
on ビュー2.keyForJoin = ビュー1.keyForJoin;

と、結合条件となる列をビュー作成時に追加する方法もあります。

ビュー2も結果も最大 1件であるなら、ビュー1とビュー2の和集合のうち日付が最大の 1件として
以下の様なSQLも考えられます。

with ビュー1 as (select 結果値, 日付 from テーブルA),
     ビュー2 as (select 結果値, 日付 from テーブルB)
select 結果値 from (
  select ROW_NUMBER() over (order by 日付 desc) r_num, 結果値 from
    ( select * from ビュー1
        union all
      select * from ビュー2
     )
)
where r_num= 1
編集 履歴 (0)
  • ご回答頂きありがとうございます。
    最高にわかりやすく勉強になる回答です。
    koutajeroさんの回答もキーワードどおしが頭のなかでつながり
    理解できました。
    件のテーブルAとテーブルBは接点がないのですが、1つめの修正案
    なら、そのまま適用することができ、意図したとおりの動作をしました。
    -

その後、解決しました。
「レコードなし」の時点でSQL文がコケてしまうと考え、
以下のようにすることで「レコードなし」を回避したところ、
意図したとおりに動作しました。

select case
when exists (select * from テーブルA)
then case
when (select 日付 from テーブルA) > テーブルB.日付
then (select 結果値 from テーブルA)
else テーブルB.結果値
end
else テーブルB.結果値
end as '結果値'
from テーブルB;

結局、「レコードなし」を条件にすることはできず、
回避するしかないようです。

編集 履歴 (0)

when ビュー1.日付 > ビュー2.日付

この条件が成り立たないからだと思います。

外部結合(fuLL)などを使うと取得できると思います。

また、CASEの最初に
CASE ビュー1.日付 IS NULL THEN ビュー2.日付
条件を付け加えると取得できると思います。

編集 履歴 (0)
  • ご回答頂きありがとうございます。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。