Null via SQL case statement not working

[patrickbachmann]

Hi folks,

I have a simple field XYZ that has null values and the null values display as a question mark.

Now I have SQL querying this field that looks like this;

SELECT

myTable.XYZ,

CASE WHEN myTable.XYZ is Null Then 0 else 1 End As MyFlag

FROM... ETC

Of course the first column displays all question marks as expected....

But MyFlag field instead of displaying a list of zero's I'm getting all question marks.

What am I missing here?

Thanks!

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

I always thought the case above should throw an error.

What's happening is that any evaluation against NULL returns false, *unless* a function is used which specifically handles NULL values.

SQL below should help.

-- NULL always fails, regardless of what the check is, unless specific NULL-related function is used.
-- CASE statement will fail, thus returning 'ISNT NULL'
SELECT CASE F NULL THEN 'IS NULL' ELSE 'ISNT NULL' END AS IS_NULL
FROM
(
  SELECT NULL AS F FROM DUMMY
);


-- Use some other function like IFNULL(), COALESCE, or something else to specifically handle NULL
SELECT
  CASE IFNULL(F, 'N') WHEN 'N' THEN 'Do whatever you want with NULLs' ELSE 'Not null' END
FROM
(
  SELECT NULL AS F FROM DUMMY
);

[Former Member]

Hi Patrick,

I think you are getting no rows retrieved when you fired that query else, you should have got 0 since you have used ISNULL function.

Can you try this,

Select case when X is null then 0 else 1 end as MyFlag from (Select (Select mytable.XYZ from mytable) as X from dummy);

Regards,

Akshay

[patrickbachmann]

Sorry guys I'm not ignoring your replies I'm just trying to understand them.  It's not making sense to me the select within select.  But let me tinker with your suggestions today and get back to you.

-Patrick

[former_member182302]

Hi Patrick,

It seems strange.

I know its redundant but normally for the kind of query you were writing we use COALESCE

SAP HANA Reference: COALESCE

Can you give a try with that and check?

Regards,

Krishna Tangudu