isnull function in WHERE clause

Question

I am attempting to fix an issue in a stored procedure and have come across an issue that is vexing me.

Basically, isnull works as expected for one record in T0 but not in another, both where T0.FatherCard are NULL. I cannot see why.

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0189'

Returns a full row of data as expected.

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0817'

Returns nothing. I am expecting a full row of data.

In both cases, FatherCard = NULL

CardCode    FatherCard  Table
------------------------------
C0189       NULL        OINV
C0817       NULL        OINV

FatherCard and CardCode are both of the same type (nvarchar) and length (50).

If I remove the ISNULL function and simply select WHERE T0.CardCode = C0817 then it works as expected.

Is it possible T0.FatherCard is actually not NULL for the purposes of the ISNULL evaluation, and is returning some other value?

Is it NULL as in a null value or NULL as in the text string "NULL"? — shree.pat18, Nov 04 '16 at 05:38
Please run `SELECT *, isnull(T0.FatherCard,T0.CardCode) FROM OINV T0` and post what you get — Niyoko, Nov 04 '16 at 05:40
@NiyokoYuliawan - I can do that but the result table is huge. Are you looking for the complete row from each record in order to compare? — nickc, Nov 04 '16 at 06:00
@shree.pat18 Actually seems you are right, it is NULL value in one row and string value NULL in another. How the values are different I'm not sure, this is SAP database that has only been updated via the SAP client. — nickc, Nov 04 '16 at 06:10

score 2 · Accepted Answer · answered Nov 04 '16 at 06:01

2

There are 2 possibilities.

To check use:

SELECT  '[' + CardCode + ']', ISNULL(FatherCard, 'Yes is NULL')
FROM    OINV
WHERE   RTRIM(CardCode) = 'C0817'

answered Nov 04 '16 at 06:01

Disillusioned

Thanks Craig. I should have checked for this, but you are right, the value isn't actually `NULL` where `T0.CardCode = C0817` but it is where `T0.CardCode = C0189`. I have no idea what or why though, this is a SAP B1 database that is really pretty vanilla. – nickc Nov 04 '16 at 06:07
@nickc It can be easy to make a mistake about how to set a NULL value on text-type fields. 1) Accidentally putting quotes around the NULL keyword in an update statement. E.g. `UPDATE ... SET Col = 'NULL'` 2) If using an editor (grid/text box) to update the value you might make the mistake of typing the string "NULL". Some controls (e.g. Edit grid in SQL Server Management Studio) require a special technique to set NULL such as `Ctrl+0`. See also http://stackoverflow.com/q/444657/224704 – Disillusioned Nov 04 '16 at 07:26
As for using a CASE statement I'd suggest it's better to clean-up your data rather than writing obscure SQL every time you might need to handle edge cases. Note you might also want to consider whether an empty string is actually a valid value. I suspect in this case not, so those should also be updated to NULL. This is easily done with `UPDATE OINV SET FatherCard = NULL WHERE FatherCard IN ('NULL', '')`. To avoid future deterioration of data, you should ensure all input sources can set NULL correctly (and maybe even create CHECK constraints to prevent certain known invalid values). – Disillusioned Nov 04 '16 at 07:34

score 0 · Answer 2 · answered Nov 04 '16 at 05:53

0

Please use

where columnname is null

answered Nov 04 '16 at 05:53

Eralper

I could use an `if` statement to check if the first column `is null` and if it is then use the second column, but I thought that is what `isnull()` is supposed to do? – nickc Nov 04 '16 at 06:02

2 Answers2