SELECT when one column might be NULL

[former_member1021736]

Hi,

Inside of a stored procedure I'm doing the following

SELECT T.LineId,T.Qty

FROM TransactionRows AS T

WHERE T.ArticleId = @ArticleId AND T.LotNumber = @LotNumber;

@ArticleId contains the "article number" that I want to select, and @LotNumber contains the "lot number".

The problem is that @LotNumber might be an actual value, OR IT MIGHT BE NULL.

If @LotNumber is NULL, the SELECT above FAILS.   However, if it is NOT NULL, it doesn't fail.

After many years working with databases I hadn't realized this until now ....

To get the correct row(s), if @LotNumber is null, I would have to write:

SELECT T.LineId,T.Qty

FROM TransactionRows AS T

WHERE T.ArticleId = @ArticleId AND T.LotNumber IS NULL

The complete statement would be:

IF @LotNumber IS NULL THEN

  SELECT T.LineId,T.Qty

  FROM TransactionRows AS T

  WHERE T.ArticleId = @ArticleId AND T.LotNumber IS NULL;

ELSE

  SELECT T.LineId,T.Qty

  FROM TransactionRows AS T

  WHERE T.ArticleId = @ArticleId AND T.LotNumber = @LotNumber;

END IF;

Is there a more elegant way of doing this?

Thanks,

Edgard

[former_member194571]

Without further information I think the shortest form besides Koichi's ansinull approach would be:

  SELECT T.LineId,T.Qty

  FROM TransactionRows AS T

  WHERE T.ArticleId = @ArticleId

    AND (T.LotNumber = @LotNumber or (T.LotNumber IS NULL and @LotNumber IS NULL))

If there is an out-of-range value, you can shorten that to (assuming -7 is out-of-range)

  SELECT T.LineId,T.Qty

  FROM TransactionRows AS T

  WHERE T.ArticleId = @ArticleId

    AND ISNULL (T.LotNumber, -7) = ISNULL (@LotNumber, -7)

If you need the restriction on T.LotNumber to be an indexed SARG for performance reasons, either of these approaches will fail to do the job. Thern the options are:

- use the ansinull approach or

- if there is an out-of-range value

  -- create an index on the ISNULL expression instead of the plain column value, which would improve the variant with the ISNULL function calls or

  -- populate the rows with this value instead of NULL. You can continue to use the ISNULL (@LotNumber, ...) expression for the parameter, the optimizer only requires a suitable SARG on the column side of the condition.

I expect the expression index approach the one with the lightest impact on the behavior of existing operations.

HTH

Volker

[VolkerBarth]

When using SQL Anywhere 12 or above, you can use the standard IS NOT DISTINCT FROM predicate to handle NULL values as "equal" within comparisons without having to add OR clauses or find an "out-of-range" value, as Volker has suggested.

col1 IS NOT DISTINCT FROM col2 is evaluated as

(col1 = col2) OR ( col1 IS NULL AND col2 IS NULL )

Note that IS NOT DISTINCT FROM is sargable and therefore should perform as well as a normal equal comparison.

So, that would simplify the query to the minimal (and elegant, I would say):

SELECT T.LineId,T.Qty

  FROM TransactionRows AS T

  WHERE T.ArticleId = @ArticleId

    AND T.LotNumber IS NOT DISTINCT FROM @LotNumber;

[former_member188493]

CREATE TABLE TransactionRows (
   ArticleId INTEGER,
   LotNumber INTEGER,
   LineId    INTEGER,
   Qty       INTEGER );

INSERT TransactionRows VALUES ( 1, NULL, 5, 55 )
INSERT TransactionRows VALUES ( 1, 10,   6, 66 );
INSERT TransactionRows VALUES ( 1, 20,   7, 77 );
COMMIT;

BEGIN 
DECLARE @ArticleId INTEGER;
DECLARE @LotNumber INTEGER;
SET @ArticleId = 1;
SET @LotNumber = 10;
SELECT T.LineId,T.Qty
FROM TransactionRows AS T
WHERE T.ArticleId = @ArticleId 
  AND ( T.LotNumber IS NULL OR T.LotNumber = @LotNumber );
END;

LineId,Qty
5,55
6,66

[former_member182948]

Hi Edgard,

You can use ansinull option.

When you set 'off' to the option, an equality (=) comparison of two NULL values yields true.

However, I can't recommend this option.

This is a risky option.

You should read the following document carefully.

"ansinull option"

http://dcx.sap.com/index.html#sqla170/en/html/812c77506ce21014ba0ab42eec157010.html

(The option is supported in the old versions.)

Regards,

Koichi