[SQL Server]パラメータ値で抽出条件（値の有無）を切り替える

[スポンサードリンク]

例えば、商品の色が登録されているもの、登録されていないものなどを取得する場合

概要

以前の記事、「NULLと０または空文字を除外する」では、ある列の値がNULLや０、空文字でないレコードを取得する方法を記載した。

仕様により、検索条件のパラメータにより、「NULL、空文字を除外」したり、「NULL、空文字のみを抽出」するといった切り替えが必要な場合がある。

例えば、商品の色が登録されているものが欲しい、逆に商品の色が登録されていないものが欲しい。
配属先の決まった新人のデータが欲しい、逆に配属先の決まっていない新人のデータが欲しいなど。

検索条件は下図のような感じ。

これをVBや、C#などの言語上で行うなら、例をあげるとこのようなイメージになる。

// trueで色ありを取得 falseで色なしを取得
bool blnColor = true;
string strSQL = "";
strSQL += "SELECT * FROM SHOHIN ";
strSQL += "WHERE ";

if(blnColor == true)
{
    // 色あり
    strSQL += "ISNULL(COLOR,'') <> '' ";
}
else
{
    // 色なし
    strSQL += "ISNULL(COLOR,'') = '' ";
}

// trueで色ありを取得 falseで色なしを取得

bool blnColor = true;

string strSQL = "";

strSQL += "SELECT * FROM SHOHIN ";

strSQL += "WHERE ";

if(blnColor == true)

{

// 色あり

strSQL += "ISNULL(COLOR,'') <> '' ";

}

else

{

// 色なし

strSQL += "ISNULL(COLOR,'') = '' ";

}

あ、ちなみにifの中は
「if(blnColor)」とか「if(!blnColor)」とかは、私の場合実務ではあまり書きません。
実務では様々なレベルの技術者が半月後、数年後にソースコードを見ることになりますので、なるべく省略しないように書いてます。

SQLのみで行う場合

しかし、これをBIツール（例えばSSRSなど）で行う場合、SQLやストアドのみで行わなければならない。
この場合、TransactSQLで文字列変数にSQLを入れてIF条件分岐でSQLを作って実行する方法もあるが、
非常に面倒くさいため、LIKEでおこなう方が単純でおススメである。

-- テーブル
DECLARE @table table (
    ITEM_CD varchar(4)  ,   -- 商品CD
    ITEM_NM varchar(50) ,   -- 商品名
    COLOR   varchar(20)     -- 商品の色
)

-- 検索条件（色）：0=色ありのもの　1=色なしのもの
DECLARE @ColorBool varchar(1) = '1'

-- データ登録
INSERT INTO @table VALUES ('1001','入浴剤１',NULL)
INSERT INTO @table VALUES ('1002','入浴剤２','')
INSERT INTO @table VALUES ('1003','入浴剤３','ピンク')

SELECT
    ITEM_CD,
    ITEM_NM,
    COLOR
FROM
    @table
WHERE
    ISNULL(COLOR,'') LIKE 
    (CASE WHEN @ColorBool = '0'
        THEN '_%'   -- 色ありの場合
        ELSE ''     -- 色なしの場合
    END)

-- テーブル

DECLARE @table table (

ITEM_CD varchar(4) , -- 商品CD

ITEM_NM varchar(50) , -- 商品名

COLOR varchar(20) -- 商品の色

)

-- 検索条件（色）：0=色ありのもの　1=色なしのもの

DECLARE @ColorBool varchar(1) = '1'

-- データ登録

INSERT INTO @table VALUES ('1001','入浴剤１',NULL)

INSERT INTO @table VALUES ('1002','入浴剤２','')

INSERT INTO @table VALUES ('1003','入浴剤３','ピンク')

SELECT

ITEM_CD,

ITEM_NM,

COLOR

FROM

@table

WHERE

ISNULL(COLOR,'') LIKE

(CASE WHEN @ColorBool = '0'

THEN '_%' -- 色ありの場合

ELSE '' -- 色なしの場合

END)

9行目のパラメータ「@ColorBool」の値を「０」に設定すると色が設定されているレコードを取得する。

「’_%’」の意味は１文字以上の任意の文字列である。

「_」アンダーバーは、「何か１文字」という意味であるため、これだけでは２文字以上の値が格納されているとレコードを取得することができない。
ここで、「０文字以上」を表す「%」を後ろにくっつけると、列に「１文字以上」の値が入っているものを抽出する。

アンダーバーやパーセントなどのワイルドカードを使用しない場合は固定値を検索するので、色なしの場合は「”」普通に空文字を指定すれば良い。

ISNULLでNULLを空文字に変換しているので、NULLと空文字の両方を処理できる。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい