SQL文が上手く作成できない

Question

質問内容

SQL初心者で、初めて質問を登録しました。
oracleのテーブルから条件に合わせて、値を出したいのですが、SQL文が上手く作成出来ません。
下記にテーブル情報と、試しているSQL文を記載します。

お手間を取らせまして大変申し訳ございません。
解決案をご教示頂きたく、お願い致します。

テーブル情報

|STAFFCODE|LICENSECODE1|LICENSEDATE1|LICENSECODE2|LICENSEDATE2|
|11101|99-9010|2018/04/01|99-9009|2017/01/01|
|22202|99-9012|2014/01/01|99-9002|2017/01/01|
|99901|99-9010|2019/04/01|99-9009|2017/01/01|

STAFFCODE　　 型：VARCHAR2(10)
LICENSECODE1　型：VARCHAR2(16)
LICENSEDATE1　型：DATE
LICENSECODE2　型：VARCHAR2(16)
LICENSEDATE2　型：DATE

各データはサンプルデータになります。

試しているSQL文

select
 CS.STAFFCODE,
 case
  when LICENSEDATE1 >= SYSDATE then LICENSECODE1
  else LICENSECODE2
  end as LICENSECODE
from
 COMSTAFF CS
where
 CS.STAFFCODE = :STAFFCODE

※ cseを使用してテストしています

実現したいこと

1レコードの中で、2つの日付情報を比較し、新しい情報を出すことは出来たのですが
新しい情報が、今日より未来日の際は、古い情報を出すように出来ますでしょうか。
※ 値が入っていなかったり、条件に合致しない場合は、値無し（null）になればと思います。

例）今日(2019/3/13)に実行した場合
11101の場合：99-9010を返す
22202の場合：99-9002を返す
99901の場合：99-9009を返す

その他に必要な情報がありましたら、お教え頂きたくお願い致します。

Comments

[m.ts10806]

テーブル情報については整列させる必要があるのでインデントを使うよりマークダウンのテーブル機能を使われたほうが綺麗に揃います。
https://teratail.com/help#about-markdown
※コードブロック内ではマークダウンききません。そこは質問プレビューを見て調整してください

[Orlofsky]

質問にCREATE TABLEとINSERT を https://teratail.com/help#about-markdown の[コードを入力] で載せると適切な回答が付きやすいです。

[yambejp]

「新しい情報が、今日より未来日の際は、古い情報」とのことですが新しい情報も古い情報も未来日だったときはどうするのでしょうか？
また「値が入っていなかったり、条件に合致しない場合」の具体的データの例示もお願いします

[ogrean21]

皆さま
初めての投稿でご迷惑をお掛けしまして、申し訳ございません。
マークダウンをよく確認して、直して行きたいと思います。

yambejp 様
>「新しい情報が、今日より未来日の際は、古い情報」とのことですが新しい情報も古い情報も未来日だったときはどうするのでしょうか？
⇒値無しになればと思います。
ただ、新しい情報も・古い情報も未来日になることは無い運用になっています。

[Orlofsky]

＞値が入っていなかったり、条件に合致しない場合は、値無し（null）になればと思います。
ってデータも用意するべきでしょう。

[ogrean21]

Orlofsky様
申し訳ございませんでした。今後投稿する際は、用意するように致します。

Answer 1

日付のうち新しい方を選択する場合に、今日より未来日やNullの場合は除外するとして、除外したい場合を入力範囲外の過去の日付に置き換えます。
新しい方を選択するにはGREATESTを利用します

select staffcode
     , case greatest(edit_LICENSEDATE1, edit_LICENSEDATE2)
         when to_date('1900/01/01','yyyy/mm/dd') then Null
         when LICENSEDATE1 then LICENSECODE1
         when LICENSEDATE2 then LICENSECODE2
       end LICENSECODE
from (
  select cs.*
       , case when LICENSEDATE1 >= SYSDATE or LICENSEDATE1 is Null
              then to_date('1900/01/01','yyyy/mm/dd') else LICENSEDATE1
         end AS EDIT_LICENSEDATE1
       , case when LICENSEDATE2 >= SYSDATE or LICENSEDATE2 is null
              then to_date('1900/01/01','yyyy/mm/dd') else LICENSEDATE2 
         end AS EDIT_LICENSEDATE2
  from COMSTAFF CS
  where STAFFCODE = :STAFFCODE
) step1

Comments

[sazi]

Nullの扱いを修正

[ogrean21]

sazi様

ご回答ありがとうございます。
先ほど、テスト環境でSQLを実行してみたところ、希望通りに動いているように見受けられました。

[sazi]

ご自身の環境（特にデータ）で特に問題ないのであれば、こちらでも検証済みですので、特に支障は無いかと思います。
内容を理解された上で、参考にされて下さい。

[ogrean21]

sazi様
ありがとうございました。

Answer 2

試しに

create table tbl(code int,license_a varchar(20),date_a date,license_b varchar(29),date_b date);
insert into tbl values
( 1,'a','2017-01-01','b','2018-01-01'),/* 両方過去 a<b 結果:b */
( 2,'a','2018-01-01','b','2017-01-01'),/* 両方過去 a>b 結果:a */
( 3,'a','2018-01-01','b','2018-01-01'),/* 両方過去 a=b 結果:a */
( 4,'a','2020-01-01','b','2018-01-01'),/* aのみ未来    結果:b */ 
( 5,'a','2018-01-01','b','2020-01-01'),/* bのみ未来    結果:a */
( 6,'a','2020-01-01','b','2021-01-01'),/* 両方未来 a<b 結果:NULL */
( 7,'a','2021-01-01','b','2020-01-01'),/* 両方未来 a>b 結果:NULL */
( 8,'a','2019-03-13','b','2020-01-01'),/* a当日,b未来  結果:a */
( 9,'a','2019-03-13','b','2018-01-01'),/* a当日,b過去  結果:a */
(10,'a','2020-01-01','b','2019-03-13'),/* b当日,a未来  結果:b */
(11,'a','2018-01-01','b','2019-03-13'),/* b当日,a過去  結果:b */
(12,'a','2019-03-13','b','2019-03-13');/* a,b当日      結果:a */ 

上記を冗長に命題に当てはめると

select code,case
when date_a<='2019-03-13' and date_b<='2019-03-13' and date_a<date_b then license_b
when date_a<='2019-03-13' and date_b<='2019-03-13' and date_a>=date_b then license_a
when date_a<='2019-03-13' and date_b>='2019-03-13' then license_a
when date_a>='2019-03-13' and date_b<='2019-03-13' then license_b
when date_a>='2019-03-13' and date_b>='2019-03-13' then null
end as lincese
from tbl

結果：b-a-a-b-a-null-null-a-a-b-b-a

上記まとめると

select code,case
when least (date_a,date_b)>'2019-03-13' then null
when not(date_a>=date_b)=('2019-03-13'>=greatest(date_a,date_b)) then license_b
else license_a
end as lincese
from tbl

※こっちもXORを排除

日付NULLあり

create table tbl(code int,license_a varchar(20),date_a date null,license_b varchar(29),date_b date null);
insert into tbl values
( 1,'a','2017-01-01','b','2018-01-01'),/* 両方過去 a<b 結果:b */
( 2,'a','2018-01-01','b','2017-01-01'),/* 両方過去 a>b 結果:a */
( 3,'a','2018-01-01','b','2018-01-01'),/* 両方過去 a=b 結果:a */
( 4,'a','2020-01-01','b','2018-01-01'),/* aのみ未来    結果:b */
( 5,'a','2018-01-01','b','2020-01-01'),/* bのみ未来    結果:a */
( 6,'a','2020-01-01','b','2021-01-01'),/* 両方未来 a<b 結果:NULL */
( 7,'a','2021-01-01','b','2020-01-01'),/* 両方未来 a>b 結果:NULL */
( 8,'a','2020-01-01','b','2020-01-01'),/* 両方未来 a=b 結果:NULL */
( 9,'a','2019-03-13','b','2020-01-01'),/* a当日,b未来  結果:a */
(10,'a','2019-03-13','b','2018-01-01'),/* a当日,b過去  結果:a */
(11,'a','2020-01-01','b','2019-03-13'),/* b当日,a未来  結果:b */
(12,'a','2018-01-01','b','2019-03-13'),/* b当日,a過去  結果:b */
(13,'a','2019-03-13','b','2019-03-13'),/* a,b当日      結果:a */
(14,'a',NULL,'b','2018-01-01'),        /* a NULL,b過去 結果:b */
(15,'a',NULL,'b','2019-03-13'),        /* a NULL,b当日 結果:b */
(16,'a',NULL,'b','2020-01-01'),        /* a NULL,b未来 結果:NULL */
(17,'a','2018-01-01','b',NULL),        /* b NULL,a過去 結果:a */
(18,'a','2019-03-13','b',NULL),        /* b NULL,a当日 結果:a */
(19,'a','2020-01-01','b',NULL),        /* b NULL,a未来 結果:NULL */
(20,'a',NULL,'b',NULL);                /* 両方 NULL    結果:NULL */

検索

select code,case
when date_a<='2019-03-13' and date_b is null then license_a
when date_a> '2019-03-13' and date_b is null then null
when date_b<='2019-03-13' and date_a is null then license_b
when date_b> '2019-03-13' and date_a is null then null
when date_a is null       and date_b is null then null
when date_a<='2019-03-13' and date_b<='2019-03-13' and date_a<date_b then license_b
when date_a<='2019-03-13' and date_b<='2019-03-13' and date_a>=date_b then license_a
when date_a<='2019-03-13' and date_b>='2019-03-13' then license_a
when date_a>='2019-03-13' and date_b<='2019-03-13' then license_b
when date_a>='2019-03-13' and date_b>='2019-03-13' then null
end as lincese
from tbl

結果
b-a-a-b-a-NULL-NULL-NULL-a-a-b-b-a-b-b-NULL-a-a-NULL-NULL

まとめると

select code,case
when least(coalesce(date_a,'9999-12-31'),coalesce(date_b,'9999-12-31'))>'2019-03-13' then null
when not(coalesce(date_a,'1900-01-01')>=coalesce(date_b,'1900-01-01'))=('2019-03-13'>=greatest(coalesce(date_a,'1900-01-01'),coalesce(date_b,'1900-01-01'))) then license_b
else license_a
end as lincese
from tbl

coalesceはnvlでもいけるかも

※oracleでは0年駄目なんですね？1900年に換えときました
※XORを別式に換えました

Comments

[sazi]

片方がNullのケースはあると思うんですよね。
また、oracleのgreatest()やleast()はNullが含まれるとNullを返却します。
ついでに、XORがあるのはMySQLだけですね。
http://cs.hatenablog.jp/entry/2013/07/19/154215

[sazi]

0年は使えません。１年とかなら大丈夫ですが。
（暗黙も含め）日付型として変換しようとするとエラーになります。

[yambejp]

saziさん色々ご助言ありがとうございます
とりえず一通り調整入れました

[sazi]

まだXORが

[yambejp]

おーそれだ、調整しました
（そかそかXORがないのね・・・）

[sazi]

> when !(coalesce(date_a,'1900-01-01')>=coalesce(date_b,'1900-01-01'))=('2019-03-13'>=greatest(coalesce(date_a,'1900-01-01'),coalesce(date_b,'1900-01-01')))
の部分がエラーになりますね。
!は使えません。それ以外でもエラーです。
pl/sql以外ではboolean型が使えないのでその為だと思いますけど。

[yambejp]

検証環境なくてすみません、notならいけますかね？

[sazi]

notやandに変えれば通りますけど、
when !(coalesce(date_a,'1900-01-01')>=coalesce(date_b,'1900-01-01'))=('2019-03-13'>=greatest(coalesce(date_a,'1900-01-01'),coalesce(date_b,'1900-01-01')))
の部分だけでは、elseに括られちゃいますのでNullの結果になるものがありません。

[yambejp]

> Nullの結果になるものがありません

Nullの結果になるものはその前のwhenですべて抽出済みでは？

[sazi]

Nullは'1900-01-01'に置き換えられているので、前のwhenには該当しません
前のwhenは何れもシステム日付より大きい場合となってますね。

[yambejp]

ごめんなさい、どこの何を指摘していただいているかわかりません

[sazi]

XORのリンク先はoracle12cですね。11gで確認してました・・・
すみません。12cの環境は無いのでその部分は検証できません。

[sazi]

> 前のwhenは何れもシステム日付より大きい場合となってますね。
when least(coalesce(date_a,'9999-12-31'),coalesce(date_b,'9999-12-31'))>'2019-03-13' then null

最小＞システム日付　なので両方ともシステム日付より大きい場合のみになりませんか？
あ、とち狂ってました。

[yambejp]

あれ？わたしの条件判断が間違えてますか？

nullになるのは
・a,bがともに未来日
・a,bがともにnull
・aが未来日でbがnull、またはその逆
だという認識なのですが、違いますかね？
上記whenはnull値を未来日に寄せて、両方未来日ならnullを実現しています

[yambejp]

もしかしたら、aかbのどちらかが空ならnullという条件なのでしょうか？
（質問者さんの定義がよくわからない）

[sazi]

いえ、
when least(coalesce(date_a,'9999-12-31'),coalesce(date_b,'9999-12-31'))>'2019-03-13' then null
上記Nullの置き換えを'1900-01-01'の置き換えに脳内で勝手に変換してました。すみません。

[sazi]

で、肝心の部分が11gの環境しか無く検証出来ません。
質問者の環境もどうなんでしょうね。

[ogrean21]

sazi様、yambejp様

質問者のogrean21です。
沢山のご意見、ありがとうございます。
また、返信が遅くなりまして、大変申し訳ございません。

>質問者の環境もどうなんでしょうね。
⇒環境は、電子カルテシステムのDBで、Oracel11gになります。
LICENSEDATEとLICENSECODEは、主キーでは無いです。
その為、STAFFCODEのみ入力しているレコードも実際にはあります。

>もしかしたら、aかbのどちらかが空ならnullという条件なのでしょうか？
>（質問者さんの定義がよくわからない）
⇒aとbのどちらかが空の場合、値が入っている方でシステム日付と比較して値を返したいです。

sazi様、yambejp様のやり取りが高度すぎて、私は正直ちんぷんかんぷんです・・・
不足する情報がございましたら、お教え頂きたくお願い致します。
出来得る限り、こちらに記載したいと存じます。

[sazi]

@ogrean21さん
私は、yambejpさんの意地にお付き合いしている訳ですけど、11gではXOR表現が使えませんし、また、検証環境が無いとの事なので、完成しないかもしれません。

[yambejp]

たしかにoracleで使える関数に無知なのは申し訳ない次第ですが
「意地」とかいわれる筋合いはないと思いますけどね

要は出力がlicense_a,licens_b,nullの3つなのですから
条件式は2つで済むはずだと言いたいだけです。
理解いただけないならそれでも結構です

case
when 条件１ then ・・・
when 条件２ then ・・・
else ・・・
end

[sazi]

@yambejpさん
お気に障ったようで、申し訳ない。
回答として決着させる意思を感じて「意地」と表現したので、悪い意味として使ったのではありません。
誤解されるかなとは、ちらと思いましたが。すみません。
「矜持」の方が良かったかな。すみません。

[ogrean21]

sazi様、yambejp様

質問者のogrean21です。
この度は、ご対応頂きまして、ありがとうございました。
SQLド素人の私では、解決出来ない内容で、大変助かりました。
また、ご教示頂きましたSQL文は、検証出来る限り行ってみます。

今後も、質問を投稿させて頂くことがあるかと存じます。
拙い知識で大変恐縮ではございますが、ご対応頂けますと幸甚に存じます。