Can coalesce and format function go together?
I want the output of a numeric field to be displayed in specific format ,and 0 when the value in the column is NULL.
So, I use
SELECT TRIM(COALESCE(Field,0) (FORMAT 'ZZZ,ZZZ,ZZ')) from Table;
where Filed is the column name and Table is table name.
But this doesn't work when the value in the field is NULL,i,e,it displays null for null values
even when coalesce is used
Why is it so.What is the solution to this problem?
I just tried it in V13 SQL Assistant and I don't get the NULL. When Field is NULL I'm getting 0. But your format statement will suppress the zero so all you see is spaces. Try ZZZ,ZZZ,Z9 instead and see if that works. But also if you are using an earlier version of SQL Assistant it is probably ignoring your format statements altogether. In that case you can cast the final formatted result to a character string.