COALESCE function problem

Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

SELECT coalesce(test,NULL) from testtable

but it says

Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]

In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

thank you for suggestions

J.S.

The COALSECE finction works fine in SQL Compact 3.1, it is a problem with the tool you are using. Have successfully tested in SQL Server Management Studio SP2. It is a problem with the Visual Studio Server Explorer query parser.

|||But, I'am not using any tool - I am using it from application (.NET 2.0). I will make a sample...|||

Hmm, i dont't know where was the problem, but now problem is another:

The specified argument value for the function is not valid. [ Argument # = 2,Name of function(if known) = coalesce ]

my source code i used:

string fn = args[0];

string cs = "Data source='"+fn+"'";

SqlCeConnection conn = new SqlCeConnection(cs);

conn.Open();

//only if table does not exists

//SqlCeCommand cmd1 = new SqlCeCommand("CREATE TABLE TESTTABLE ( TEST NVARCHAR(200) NULL )", conn);

//cmd1.ExecuteNonQuery();

SqlCeCommand cmd2 = new SqlCeCommand();

cmd2.CommandText = "SELECT COALESCE(TEST,@.p1) FROM TESTTABLE";

cmd2.Parameters.AddWithValue("p1", "no value");

cmd2.Connection = conn;

SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd2);

DataSet ds = new DataSet();

adapter.Fill(ds);

another command

SELECT COALESCE(TEST,'no value') FROM TESTTABLE

is OK, but this is not my scenario...

I think the problem is in parameter - it cannot be used in COALESCE function ?

|||

Looks like you must change the statement to not use parameters!

|||

But this is against th best practices - to use parameters instead of literals...

Of course, I am using SqlServerCe as alternative data provider (user can configure SqlServer/SqlServerCe) and i dont want to write another (bad!) code to achive same functionality...

any ideas ? will this be supported in another version ?

thank you

|||

Hi Jan.

This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

Code Snippet

SqlCeConnection conn = new SqlCeConnection(@."Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd2 = new SqlCeCommand();
cmd2.CommandText = "SELECT COALESCE(Region,@.p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
cmd2.Parameters.AddWithValue("p1", "no value");
cmd2.Connection = conn;
object res;
res = cmd2.ExecuteScalar();

So time will mend all wounds!

|||

Oh ! Great, this is, what I want to read...

Thank you for your quick and brilliant answers.