All Forums Database
Kishore_1 166 posts Joined 03/10
27 Oct 2010
coalesce and format function

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?

KeithJones 3 posts Joined 07/10
27 Oct 2010

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.

You must sign in to leave a comment.