全件取得または値が０以外のレコードを取得する

[スポンサードリンク]

CASE文、ABS、LIKE演算子で実現する。

要件

パラメータにより、レコード全件を取得するか、値が０以外のレコードを取得する。
この場合、商品名と在庫数量の２つの列を持つテーブルで考えて見る。

パラメータの選択値により、
・「在庫数量が０以外の（在庫がある）商品」を取得
・全件取得
を切り替えたい。

これは、WHERE句に書く条件の演算子自体が変わるため、変数にSQLを入れて実行するか、下記の条件文で対応できる。

対応策

対応策としては、比較対象となる在庫数量を文字列に変換して、NOT LIKEで比較した。

数値項目が1つの場合

-- フラグ０＝０以外　フラグ１＝全件
DECLARE @flg int = 0
DECLARE @table table (
    ITEM_NM varchar(50),
    QTY int
)

INSERT INTO @table VALUES ('商品A',100)
INSERT INTO @table VALUES ('商品B',0)
INSERT INTO @table VALUES ('商品C',0)
INSERT INTO @table VALUES ('商品D',200)

SELECT
    ITEM_NM AS 商品名,
    QTY AS 数量
FROM
    @table
WHERE
CAST(QTY as varchar) NOT LIKE (
    CASE WHEN @flg = 0 THEN '0'
    ELSE '' END)

-- フラグ０＝０以外　フラグ１＝全件

DECLARE @flg int = 0

DECLARE @table table (

ITEM_NM varchar(50),

QTY int

)

INSERT INTO @table VALUES ('商品A',100)

INSERT INTO @table VALUES ('商品B',0)

INSERT INTO @table VALUES ('商品C',0)

INSERT INTO @table VALUES ('商品D',200)

SELECT

ITEM_NM AS 商品名,

QTY AS 数量

FROM

@table

WHERE

CAST(QTY as varchar) NOT LIKE (

CASE WHEN @flg = 0 THEN '0'

ELSE '' END)

フラグが0（0を排除）

フラグが1（全件出力）

数値項目が複数の場合

例えば在庫数量という1つの数値項目だけでなく、複数の数値項目を判断する場合は、下記のようなソースになる。

この例では「商品名」「売上実績数量」「返品数量」の3カラムで、数値項目は2カラムである。

-- フラグ０＝０以外　フラグ１＝全件
DECLARE @flg int = 0
DECLARE @table table (
    ITEM_NM varchar(50),
    URI_QTY int,
    HEN_QTY int
)

INSERT INTO @table VALUES ('商品A',100,-100)
INSERT INTO @table VALUES ('商品B',0,0)
INSERT INTO @table VALUES ('商品C',200,1)

SELECT
    ITEM_NM AS 商品名,
    URI_QTY AS 売上数量,
    HEN_QTY AS 返品数量
FROM
    @table
WHERE
CAST(URI_QTY + HEN_QTY as varchar) NOT LIKE (
    CASE WHEN @flg = 0 THEN '0'
    ELSE '' END)

-- フラグ０＝０以外　フラグ１＝全件

DECLARE @flg int = 0

DECLARE @table table (

ITEM_NM varchar(50),

URI_QTY int,

HEN_QTY int

)

INSERT INTO @table VALUES ('商品A',100,-100)

INSERT INTO @table VALUES ('商品B',0,0)

INSERT INTO @table VALUES ('商品C',200,1)

SELECT

ITEM_NM AS 商品名,

URI_QTY AS 売上数量,

HEN_QTY AS 返品数量

FROM

@table

WHERE

CAST(URI_QTY + HEN_QTY as varchar) NOT LIKE (

CASE WHEN @flg = 0 THEN '0'

ELSE '' END)

売上実績数量と返品数量を足したもので判断している。

フラグが0（0を排除）

フラグが1（全件出力）

しかし、この場合、仕様的に不具合となる場合もある。

「商品A」のように売上実績数１００と、返品数が－１００（マイナス値）相殺されて0になったものも出力しなくていい場合はこのソースでもよい。

※「返品数がマイナスっておかしくね？」という突っ込みは無視する、実際にそう表示する要件もある（例えば売上伝票の値と取消伝票の値も1行に一緒に出力する場合もある）

これが、「相殺されても、売上と返品は実際にあったので出力する」という要件だった場合。

売上数量と返品数量が両者純粋に「0」のもののみ除外しなければならない。

この場合は下記のようなソースになる。

-- フラグ０＝０以外　フラグ１＝全件
DECLARE @flg int = 0
DECLARE @table table (
    ITEM_NM varchar(50),
    URI_QTY int,
    HEN_QTY int
)

INSERT INTO @table VALUES ('商品A',100,-100)
INSERT INTO @table VALUES ('商品B',0,0)
INSERT INTO @table VALUES ('商品C',200,1)

SELECT
    ITEM_NM AS 商品名,
    URI_QTY AS 売上数量,
    HEN_QTY AS 返品数量
FROM
    @table
WHERE
CAST(ABS(URI_QTY) + ABS(HEN_QTY) as varchar) NOT LIKE (
    CASE WHEN @flg = 0 THEN '0'
    ELSE '' END)

-- フラグ０＝０以外　フラグ１＝全件

DECLARE @flg int = 0

DECLARE @table table (

ITEM_NM varchar(50),

URI_QTY int,

HEN_QTY int

)

INSERT INTO @table VALUES ('商品A',100,-100)

INSERT INTO @table VALUES ('商品B',0,0)

INSERT INTO @table VALUES ('商品C',200,1)

SELECT

ITEM_NM AS 商品名,

URI_QTY AS 売上数量,

HEN_QTY AS 返品数量

FROM

@table

WHERE

CAST(ABS(URI_QTY) + ABS(HEN_QTY) as varchar) NOT LIKE (

CASE WHEN @flg = 0 THEN '0'

ELSE '' END)

フラグが0（0を排除）

フラグが1（全件出力）

これは、売上数量と返品数量の相殺を防ぐため、「ABS関数」で絶対値を求め足している。
これにより、マイナス値がなくなり、売上数量と返品数量の両者が純粋に0のものだけを排除できる。

[スポンサードリンク]

IT☆ぱらだいす

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