ISNULL ( ) support in SQL Server mobile?
Is ISNULL ( check_expression , replacement_value ) supported in SQL Server 2005 Mobile edition.
If not, then why the following statement executes without any error: -
" select ISNULL(a,12) as test from tableA "
Where 'a' is int column.
However, even though i do not get an error, it doesn't behaves as expected. I mean the result which i get is : -
a. If the column is not null, the value in 'Test' (resultset) is False.
b. If the column is null, the value in 'Test' (resultset) is true.
The expected result was that the Null values would get replaced with the specified replacement value.
The Questions are: -
1. Is ISNULL( ) supported in SQL Server mobile edition?
2. If not, then why it gets executed without error?
3. If yes, then why are the execution results differing from the expected results?
[849 byte] By [
Abhi_7] at [2008-2-8]
Darren,
Thanx for the reply. Did u try out the issue yet? Am waiting for your response.
BTW What you said is absolutely true. Even the BOL for mobile edition doesnt shows this function in the list of supported function. The reason I am confused is because this function is still working without raising any parsing error. Thats why i thought it would be better to confirm whether it is supported or not?
Please check if there is some inconsistency?
Thanks in advance
Abhi
Abhi,
I did try this out and it appears to be working both from Query Analyzer 3.0 and from code. I ran "SELECT ISNULL(FieldSize, 12) As Test FROM QuestionRules" and for every row in my QuestionRules table that contains a "null" value for FieldSize, I got back a value of "True" in the resultset. The second parameter in the ISNULL function appears to get ignored but as you noticed, it does not cause any parsing or execution errors.
So you've found what appears to be an undocumented feature in SQL Mobile.
thanks,
Darren
Hi,
This appears to be a massive limitation in Mobile SQL. I pretty much use ISNULL religiously. I suppose there is good reason not to include it as it must have a massive performace hit. The undocumented ISNULL() call that seems to work OK is a solution but I am not happy using undocumented calls just incase later versions wipe it out. What would be the best way to achieve the following otherwise:
SELECT CASE ISNULL(DateRead) WHEN 1 THEN 'YES' ELSE 'NO' END FROM tbl_Messages WHERE JobClose IS NULL
TIA
Richard
Hi,
Well this has taught me a big lesson. Turns out that ISNULL() is not a widely recognised or used standard in SQL. It appears that the function COALESCE() is preffered and supported. Thats if this article I read is acurate:
http://www.bennadel.com/blog/195-SQL-COALESCE-Like-ISNULL-On-Steroids-With-Caveats.htm
Anyway heres what I've done now:
SELECT CASE COALESCE(DateRead,'01/01/1900') WHEN '01/01/1900' THEN 'NO' ELSE 'YES' END FROM tbl_Messages WHERE JobClose IS NULL
