oralse SQL文 WHERE句内のCASE文について（日付変更線が6:00:00のシステム）

Question

前提・実現したいこと

oracleのwhere句内のcase文について質問です。

SENDdatetimeというカラムの時間部分が0:00:00～05:59:59の場合は日付部分を-1した値で抽出し、6:00:00～23:59:59の場合は日付部分そのままの値で抽出したいです。

日付変更線が6時のシステムで、0:00:00～05:59:59は前日としたいのです。

「> '2020/07/31'」の不等号部分で以下のエラーメッセージが発生しました。
解決策をご教授ください。

発生している問題・エラーメッセージ

ORA-00905: キーワードがありません。

該当のソースコード

SELECT ID
FROM TabelA
WHERE ID = 0001
AND 
CASE 
 WHEN TO_CHAR(SENDdatetime, 'HH24:MI:SS') <= '05:59:59' 
              AND '00:00:00' <= TO_CHAR(SENDdatetime, 'HH24:MI:SS') 
  THEN TO_CHAR(SENDdatetime, 'yyyy/mm/dd') - 1 >= '2020/07/31'
 WHEN TO_CHAR(SENDdatetime, 'HH24:MI:SS') <= '23:59:59'
              AND '06:00:00' <= TO_CHAR(J.MEASURE_SEND_DATE, 'HH24:MI:SS') 
  THEN TO_CHAR(SENDdatetime, 'yyyy/mm/dd') >= '2020/07/31'
END

試したこと

SQL文の中ではなくプログラム内（C#）で書けたらよかったのですが、様々な条件で難しいです。

Comments

[Orlofsky]

'2020/07/31' は常に固定値でしょうか？
質問にCREATE TABLE, INSERTを何件か、希望するSELECT結果をMarkdownの[コード]で追記してください。

[taka_pig]

情報が足りずすみません。
次回質問するときはもっと詳細を書きます。

Answer 1

日付変更線が6時のシステムで、0:00:00～05:59:59は前日としたいのです。

日時を6時間ずらすと簡単に判断できます。

SELECT ID FROM TabelA
WHERE ID = 0001
  AND TO_CHAR(SENDdatetime - 6/24, 'yyyy/mm/dd') >= '2020/07/31'

ただ、こういった場合、インデックスが適用されるように項目の編集行わずに条件を加工するようにします。

SELECT ID FROM TabelA
WHERE ID = 0001
  AND SENDdatetime >= (to_date('2020/07/31','yyyy/mm/dd') + 6/24)

Comments

[Orlofsky]

saziさんのSQLの方がシンプルですね。

[taka_pig]

ありがとうございました。
'2020/07/31'はユーザーが入力するDateTimePickerの値です。
その値を翌日の5:59:59にして判断したらよかったのですね！

SENDdatetime > (to_date('2020/07/31','yyyy/mm/dd') + 1 + 21599/86400)

でやりたいことができました。
左辺に計算を書くとOracleサーバー監視者がすっ飛んでくるので、そこも悩みでした。解決です。
今日は定時に帰れそうです。
ありがとうございました。

[sazi]

> '2020/07/31'はユーザーが入力するDateTimePickerの値です。
どうして一日足すのですか？
足すのは別にしても、秒に直して大なりにする必要は無いと思いますけど。
ユーザ入力の条件が2020/7/31以降ならそれは、2020/7/31 6:00:00以降ですので、敢えて一秒前にして大なりにする必要はないし、逆に、SENDdatetime がミリ単位も保持しているなら正しく抽出できません。

[sazi]

なんとなくですけど、ユーザー入力は月を指定で、それを月末日に直すようなことを行っているから＋1日してるんですかね。
もしそうならわざわざ月末日を求める必要は無いと思いますけどね

[taka_pig]

>SENDdatetime がミリ単位も保持しているなら正しく抽出できません。
まさにこれで数時間苦しんでおりました。
右辺で計算することが可能となったので、SQLでは単純に

WHERE ID = 0001
AND SENDdatetime > :hogehoge

とし、プログラムでパラメーター:hogehogeを設定することにしました。
しかし「5:59:59.999」がパラメーターの型datetimeに入らず、stringだと入るし何となく動いてるけどそれでいいのか…？とリスクを感じたので、
最終的には、5:59:59ではなくおっしゃる通り6:00:00とし、SQLは

WHERE ID = 0001
AND SENDdatetime >= :hogehoge

で解決しました。

>どうして一日足すのですか？
ユーザーが設定する納品日が「2020/7/31」だとして、SENDdatetimeが「2020/08/01 6:00:00.000」より未来の場合は検索結果からはじきたいのです。（NOT EXISTSなので「>=」です）

以下余談。
Oracleサーバー監視者にNOT EXISTSはパフォーマンスが悪いので使うなと言われていますが、仕方ないで通します。
IDで絞ってるから大丈夫なはず。。

[sazi]

> ユーザーが設定する納品日が「2020/7/31」だとして、SENDdatetimeが「2020/08/01 6:00:00.000」より未来の場合は検索結果からはじきたいのです。（NOT EXISTSなので「>=」です）

それなら
SENDdatetime < (to_date('2020/07/31','yyyy/mm/dd') + 6/24)
とすればNot existsとする必要は無くなりますね

[sazi]

6:00:00が日の起点という事に囚われすぎていると感じます。
普通に日付で考えて、最後に＋6時間の調整を行うようにすると良いと思います。

多分他の処理でも、同様に見直せば、シンプルになる箇所があるのではないでしょうか。

[taka_pig]

>SENDdatetime < (to_date('2020/07/31','yyyy/mm/dd') + 6/24)
とすればNot existsとする必要は無くなりますね
ホントですね・・・
既存の「○○ならはじく」の条件が全てNot existsのサブクエリで連なっていたので、同じフォーマットにしてしまいました。

もうチェックインしてテストしちゃったので、次の機会の課題にします。
ありがとうございました。

Answer 2

保守を容易にするために、SQLに限らず常にシンプルなコードを書くように心がけてください。
動作確認していませんが、

SELECT ID
FROM TabelA
WHERE ID = 0001
AND
CASE
    WHEN TO_CHAR(SENDdatetime, 'HH24:MI:SS') <= '05:59:59' THEN SENDdatetime - 1
    ELSE SENDdatetime
END >= TO_DATE('2020/07/31', 'yyyy/mm/dd') ;

SQL文の中ではなくプログラム内（C#）で書けたらよかったのですが、様々な条件で難しいです。

SQLの中で必要なデータだけを抽出できた方がパフォーマンスが良いのでは？

Comments

[taka_pig]

回答とアドバイスありがとうございました！