質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.04%

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

解決済

回答 2

投稿 ・編集

  • 評価
  • クリップ 0
  • VIEW 935

score 13

質問内容

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を返す

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ogrean21

    2019/03/13 10:34

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

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

    キャンセル

  • Orlofsky

    2019/03/13 21:21

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

    キャンセル

  • ogrean21

    2019/03/13 22:15

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

    キャンセル

回答 2

checkベストアンサー

+2

日付のうち新しい方を選択する場合に、今日より未来日や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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/13 11:25

    Nullの扱いを修正

    キャンセル

  • 2019/03/13 15:18

    sazi様

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

    キャンセル

  • 2019/03/13 15:29 編集

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

    キャンセル

  • 2019/03/13 22:11

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

    キャンセル

+1

試しに

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を別式に換えました

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/13 19:27

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

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

    case
    when 条件1 then ・・・
    when 条件2 then ・・・
    else ・・・
    end

    キャンセル

  • 2019/03/13 20:55 編集

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

    キャンセル

  • 2019/03/13 22:17

    sazi様、yambejp様

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る