I have a decimal column.
Whenever value is NULL in the column, I want to make it 0.
Which option is better? why?
3. CASE WHEN col_name IS NULL THEN 0 ELSE col_name
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.
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
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)
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.