COALESCE VS ZEROIFNULL VS CASE statement

Database
Enthusiast

COALESCE VS ZEROIFNULL VS CASE statement

Hi All,

I have a decimal column.
Whenever value is NULL in the column, I want to make it 0.
Which option is better? why?
1. COALESCE(col_name,0)
2. ZEROIFNULL(col_name)
3. CASE WHEN col_name IS NULL THEN 0 ELSE col_name

 

Thanks,

Sankalp

Tags (2)
9 REPLIES
rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

Sankalp,

The result datatype will be the same as your decimal column for zeroifnull and coalesce but for case it will be byteint.

Other than that the performance will be similar.

 

 

Rglass

Junior Contributor

Re: COALESCE VS ZEROIFNULL VS CASE statement

Why do you think that CASE differs? COLASCE is just a simplified CASE.

rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

It differs in the way I indicated on 15.0

I consider a datatype change a difference. 

Junior Contributor

Re: COALESCE VS ZEROIFNULL VS CASE statement

SELECT 1245.567 AS col_name
    ,TYPE( CASE WHEN col_name IS NULL THEN 0 ELSE col_name end)
    ,TYPE(Coalesce(col_name,0))
    ,TYPE(ZeroIfNull(col_name))

1.245,567	DECIMAL(7,3)	DECIMAL(7,3)	DECIMAL(7,3)

Teradata 15.10.02.06
rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

Ok,

I missed the else clause.

 

SyntaxEditor Code Snippet

create volatile table nulltest (b char(1), a decimal(7,3)) on commit preserve rows;
insert into nulltest values('a',);
insert into nulltest values('b',9999.999);

SELECT 
     TYPE( CASE WHEN a IS NULL THEN 0 end)
    ,TYPE(Coalesce(a,0))
    ,TYPE(ZeroIfNull(a))From nulltest;


BYTEINT    DECIMAL(7,3)    DECIMAL(7,3)BYTEINT    DECIMAL(7,3)    DECIMAL(7,3)
Teradata Employee

Re: COALESCE VS ZEROIFNULL VS CASE statement

SELECT TYPE( CASE WHEN a IS NULL THEN 0000.000 end)

returns "Decimal(7,3)"

Enthusiast

Re: COALESCE VS ZEROIFNULL VS CASE statement

Result for CASE may be ByteInt but i,m to store the result(i.e., 0) in the same Decimal column(DECIMAL(18,2)), so it will take up the defined decimal space, right? That means, we can choose any as there is no performance benefit also of using one over the others.

Enthusiast

Re: COALESCE VS ZEROIFNULL VS CASE statement

Hi All,

 

columns : 

LN_SUBS_ID,LN_SUBS_NM 

  LN_SUBS_ID have 00000000  than update LN_SUBS_NM is 0

elso no need to change anyvalue.

 

can you help on the above sinario.

 

Thanks

Teradata Employee

Re: COALESCE VS ZEROIFNULL VS CASE statement

Do you mean "Update <tablename> SET LN_SUBS_NM = '0' WHERE LN_SUBS_ID = '00000000';"?