[oracle]下記selectのwhere区について

Question

お世話になります。
ORACLEは初心～中級レベルぐらいだと思うのですが、
次のことが実現できません。
ネットで検索し、関連サイトの説明を見てもさっぱりなので知っている方は即回答できるとおもわれますので教えてください。

[やりたいこと]
TABLEから入力IN( :IN )が1のときにT.COL1 = '100'
2のときにT.COL1 != '100'
という条件の下でSELECTしたい。

SELECT
*
FROM
TABLE T
WHERE
☆ここ☆

上記の☆ここ☆の部分の具体例を教えてください。

Answer 1

関数もあると思いますが、基本的な構文で。。。

where (:IN = 1 and T.COL1  = '100') or (:IN = 2 and T.COL1 != '100')

Comments

[tokyocats]

シンプルながら、これでも実行できますね。
ありがとうございます。

Answer 2

私なら次のように書きます。

SELECT *
FROM T
WHERE 1 =
	CASE
		WHEN :Nyuryoku = 1 THEN decode(T.COL1, '100', 1, 0)
		WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)
		ELSE NULL
	END;

※バインド変数を:INとすると、「ORA-01745: ホスト/バインド変数名が無効です。」が発生するため、
ここでは:Nyuryokuとしました。

WHERE 1 =

としているのは結果が1であればTrue、そうでなければFalseと判断させるためです。
判断はそれ以下のCASE WHENで表現します。

WHEN :Nyuryoku = 1 THEN decode(T.COL1, '100', 1, 0)

の行は:Nyuryoku = 1の時はdecodeでT.COL1の値を判定します。
T.COl1が'100'であれば1(True)を、そうでなければ0を返します。

WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)

の行は1つ目のWHENとは逆にT.COL1の値が'100'の場合に0を、そうでなければ1を返します。

ELSE NULL

この行はもし:Nyuryokuが1または2以外の値が指定された部分の対処をする部分です。
1または2以外ではNULLを返していますので1 = Nullの結果はTrueでもFalseでもないUnknownとなり、
検索対象になりません。

Comments

[退会済みユーザー]

これだと
:Nyuryoku =2の場合に、T.COL1 が null
の行が抽出されてしまいます。

質問者は
:Nyuryoku =2 の場合は、T.COL1 != '100' の行を
抽出したいと書いてますので

要件を満たさないと思います。

[退会済みユーザー]

HibinoMeguru さんのコメントについて　指摘です。

T.COL1 の null が　T.COL1 != '100' の条件で抽出されても良いと思いますが、、
そもそも、　COL1 がNULLなのか　NOT　NULL　のカラムなのかの情報がないです。

HibinoMeguruのコメントは、いつも因縁つけているようです。

[nabe3]

HibinoMeguruさんご指摘ありがとうございます。

terionさんのおっしゃるように
「T.COL1 の null が T.COL1 != '100' の条件で抽出されても良いと思います」
にも一理あります。

前提条件としてT.COL1にnot null制約が付いているか否かは明記されていませんが、
もしnullが入り込む可能性がある、かつT.COL1 != '100' の条件でnullが出てくるのを
避けたいのでしたら次のように変更することで対応可能です。

変更前) WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)
変更後) WHEN :Nyuryoku = 2 THEN decode(nvl(T.COL1, '100'), '100', 0, 1)

この変更でもしT.COL1がnullだった場合は、
入力INの値が1、2、nullに関わらず抽出されなくなります。

[tokyocats]

返信がおそくなりまして申し訳ありません。
ご回答有難うございます。

SELECT *
FROM T
WHERE 1 =
CASE
WHEN :Nyuryoku = 1 THEN decode(T.COL1, '100', 1, 0)
WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)
ELSE NULL
END;

上記ＳＱＬを実行してみたところ、次のようにエラーが出ました。

ORA-00904: "T"."COL1": 無効な識別子です。
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

2つめのT.COL1を指しています。

お手数ですが、解決版のＳＱＬの記述をお願いします。

[退会済みユーザー]

エラーコードの意味くらい調べようぜ

[nabe3]

ご質問時のテーブル名「T」と列名「COL1」を用いてサンプルSQLを組み立てました。

エラーの内容は「TというテーブルにCOL1という列は存在しないですよ。」という意味です。
「ORA-00942: 表またはビューが存在しません。」が出ていないことからすると、
"T"というテーブルは存在するようですのでご自身の環境に合わせて便宜
"COL1"を置き換えて実行してください。

[tokyocats]

確認したのですが、どうみても存在するんですよね・・・
ちなみにiwamoto_takaakiさんの回答ではきちんと目的達成することができました。

[nabe3]

ご質問の内容を再度読み直しました。
初めにSQLの例示について「記述間違いだろう」と思い込んでしまっていましたが、
もしやテーブル名の部分
> FROM
> TABLE T
はテーブル名が予約語の「TABLE」で「T」がテーブル別名
としているのではなかろうかと思いました。
（もしくはテーブル名が「TABLE T」でテーブル別名をつけていない。）
そうなると私が書いたSQLのコピペでは動かず、
次のように書き換える必要があります。

テーブル名が【TABLE】で別名に【T】をつけている場合
修正前）FROM T
修正後）FROM "TABLE" T

テーブル名が【TABLE T】だった場合
修正前）FROM T
修正後）FROM "TABLE T" T

テーブル名をダブルクォートでくくっている部分がポイントです。
何度もお手間をおかけしますが再度ご確認をお願いします。

以下のサイトにこの事の解説があります。
[ Oracle ] テーブル名やカラム名に SQL 予約語を使用する
http://hensa40.cutegirl.jp/archives/1259

[tokyocats]

nabe3さん

ご返信おそくなり大変申し訳ありません。

TABLEは例で使っただけですので実際に使っているテーブル名は予約後ではありません。
また、既存で使っているSQLのwhere句に
AND　1 =
CASE
WHEN :Nyuryoku = 1 THEN decode(T.COL1, '100', 1, 0)
WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)
ELSE NULL
END
を追加したイメージです。
既存のSQLのWHERE区に元々、
WHERE
AND～
AND～
AND～
とありまして、ここに追加しただけになります。

[nabe3]

ORA-00904: "T"."COL1": 無効な識別子です。
が発生する可能性について考えました。

(1)テーブル名に別名"T"を付けていないのに CASE WHEN で T.COL1 としている。
SELECT *
FROM TBL
WHERE 1 =
CASE
WHEN :Nyuryoku = 1 THEN decode(T.COL1, '100', 1, 0)
WHEN :Nyuryoku = 2 THEN decode(T.COL1, '100', 0, 1)
ELSE NULL
END;
→テーブル別名に"T"を付けるか、
CASE WHEN のT.COL1を[テーブル名].[カラム名]と変更してみてください。

(2)"COL1"という列名は実は副問合せの結果に別名を付けたものである。
SELECT (SELECT COL1 FROM TBL) "T.COL1"
FROM DUAL
WHERE 1 =
CASE
WHEN :Nyuryoku = 1 THEN decode(TBL.COL1, '100', 1, 0)
WHEN :Nyuryoku = 2 THEN decode(TBL.COL1, '100', 0, 1)
ELSE NULL
END;
→副問合せの内側に CASE WHEN を入れるか、
もう1レベル上で CASE WHEN を使用してみてください。

(3)どちらでもない場合。
→既存のSQLを提示して頂ければ解決できる可能性が高いです。
テーブル名はTBL1, TBL2、カラム名はCOL1, COL2のように
伏せて(置き換えて)頂いて結構です。

[tokyocats]

ご回答ありがとうございます。
私事ではありますが現在検証の余力がないのと、シンプルであるべきという理由で今回のベストアンサーはiwamoto_takaakiさんの回答とさせていただきます。
何度もご相談にのっていただき、また丁寧に回答いただき大変感謝しております。
また、質問させていただいたときに目に触れる事がありましたら、そのときはまた宜しくお願い致します。