How to return default value for NULLs?

[sblue]

Hi,

I'm doing a left outer join and would like to replace the null values returned for the right table with a default value. Is that possible?

Blue

[mxstu]

The syntax depends on your database type. Try ..


SELECT COALESCE(aTextColumn, 'SomeDefaultValue') AS aTextColumn
SELECT COALESCE(aNumericColumn, 0) AS aNumericColumn
.......

[SQLMenace]

Just so that you know ISNULL() will do the same and is ANSI-SQL


SELECT ISNULL(aTextColumn, 'SomeDefaultValue') AS aTextColumn
SELECT ISNULL(aNumericColumn, 0) AS aNumericColumn

[jorgepino]

just to make sure NULL value and '0' value are not the same
if you want to the data from the table to to have a value of '0' when no
value is assigned
you should set the table to have a defaul value of zero instead of NULL it
will same time and code

[jarl@mimer.com]

COALESCE is ANSI SQL!!! (Feature 261-04, as specified by the ANSI/ISO
SQL-99 and SQL-2003 standards.)

ISNULL is vendor specific.


/Jarl

[philh]

jorgepino, the query is the result of an OUTER JOIN. There are no records in the table that match the query condition; that's why sblue has to handle NULL results.

[mxstu]

>Just so that you know ISNULL() will do the same and is ANSI-SQL

SQLMenace,

Actually I believe it COALESCE() that is ANSI-SQL and ISNULL() is the proprietary function.