Question about ifnull() function

[former_member214945]

Dear colleague,

I am confused about ifnull() function

According to documentation,

IFNULL ( expression1, expression2 [ , expression3 ] )

Parameters

expression1 The expression to be evaluated. Its value determines whether expression2 or expression3 is returned.

expression2 The return value if expression1 is NULL.

expression3 The return value if expression1 is not NULL.

But when I test with it, if expression1 is not NULL, the returned value is the expression1 itself.

Here is my test ccl file:

**********************

CREATE INPUT STREAM inString SCHEMA (Col1 string);

CREATE OUTPUT STREAM outString

as select inString.Col1 Col1,

ifnull(inString.Col1, 'null value', 'NOT null value') Col2

   from inString;

CREATE INPUT STREAM inInteger SCHEMA (Col1 integer);

CREATE OUTPUT STREAM outInteger

as select inInteger.Col1 Col1,

ifnull(inInteger.Col1, 0, 2) Col2

   from inInteger;  

**********************

It does not seem correct to me:

with integer type

Col1	Col2
500	500
NULL	0

when the expression to be evaluated is not NULL (in this case, 500), the output should be the third expression (2), not the evaluated expression itself.

With string type:

Col1	Col2
string	string
NULL	null value

when the expression to be evaluated is not NULL (in this case, ‘string’), the output should be the third expression (‘NOT null value’), not the evaluated expression itself.

Could you please help to clarify?

Thanks,

Tao

[JWootton · Accepted solution]

Where did you find the documentation for ifnull()?  Was it from another product?

In CCL,  according to the CCL reference guide, ifnull() is an alias for firstnonnull(), which will return the value of the first non-null element in a list of elements.

See:  http://help.sap.com/saphelp_hana_options_sds_ccl/helpdata/en/e7/8ffc0c6f0f1014a8adf3bc084e371a/conte...

If this came from the ESP or SDS doc somewhere, then it would seem to be a doc error.  I didn't find it, but if it did, and you can point me to it, we can get it corrected.

[former_member214945 · Accepted solution]

My mistake. I came across the sybase sql ifnull() function. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1540/doc/html/san12...

Thanks. I will use a custom function then.

Best regards,

Tao

[JWootton · Accepted solution]

No need to write a custom function.  Sorry,  I should have included this in my reply....  you can achieve this using isnull() and the CASE expression, eg:

CREATE INPUT STREAM inInteger

SCHEMA (Col1 integer);

CREATE OUTPUT STREAM outInteger

as select

   inInteger.Col1 Col1,

  (CASE WHEN isnull(inInteger.Col1) THEN 0 ELSE 2 END) Col2  

from inInteger;