Posted by Serge Rielau on 05/24/05 06:15

ryan.mclean@gmail.com wrote:
> Hi all, first, let me preface this by saying that I am very new to sql
> server, coming from oracle.
>
> Here is my problem: I would like to have a case statement (similar to
> decode in oracle) that will test a query for a null value. Here is my
> statement:
>
> SELECT
> CASE
> (SELECT MAX(SEQ) + 1
> FROM [TRANSACTION]) WHEN NULL
> THEN 0
> ELSE
> (SELECT MAX(SEQ) + 1
> FROM [TRANSACTION]) END
>
> It functions correctly if there is a value for MAX(SEQ) + 1, otherwise
> it returns null. It's as if the test for null fails. I hope that
> makes sense.
1. newer versions of Oracle also support CASE-Expression (it's not a
statement, btw - CASE-Statement is part of SQL/PSM in the same bucket as
IF THEN ELSE)
2. SQL Server behaves properly here. The reason is that "simple case"
uses equality, but NULL = NULL is UNKNOWN, which is NOT TRUE. Thus the
system will fall into the ELSE.
If you want to write this right (and still use CASE) you need to use a
"searched case":
CASE WHEN (SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) IS NULL
THEN 0
ELSE (SELECT MAX(SEQ) + 1
FROM [TRANSACTION])
END

Cheers
Serge

PS: I presume SQL Server supports IS NULL or a similar predicate.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

• Next in forum: Re: Insert performance different between two servers
• Prev in forum: Re: Subquery in case statement
• Thread view: Re: Subquery in case statement

[Reply to this message]