Replacing Null values in query

Hello,

I'm using the query wizard in VB.net to write a query for SQL CE. I want to replace null values with text. I expected the COALESCE function to do this but I get an error message saying its not a valid function. This is a sample.

Select COALESCE(table.Name,'No Name') as Name from table

Any help appreciated

Thanks

Wouldn't ISNULL() do the trick for you?|||I'm connected to SQL compact. isnull() will only return a logical value. NVL doesn't work in SQL compact and when I use coalesce the editor bangs square brackets around it and returns an error message|||

You can use something like

SELECT CASE WHEN c1 IS NULL THEN 'No name' ELSE c1 END AS EXPR1
FROM t1

COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!

|||

Pragya Agarwal [MSFT] wrote:

COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!

I checked in the newer Visual Studio 'Orcas' builds and this bug has already been fixed :- ).

|||

Many thanks for your help