select null is NULL

Does anybody have a good replacement for sql server 2000 for the the following



which you can test by hard coding the value for x. eg.


and



All the above sql statements work in MS ACCESS, but not MS SQL 2000

my MS SQL 2000 version is


I always like to keep my SQL as portable as possible. Any body got a portable replacement for the above? What do other databases use?

portability is probably maximized if you use standard sql, and CASE is standard

It doesn't seem to be standard in Access, unless I'm mistaken.

I have used the form "case when exp then statement end"
is this standard?
I wasn't aware of this form until recently. I'm more used to
"case exp when expr2 then statement end" where exp is compared with expr2 as apposed to the above where it is used just like an if statement

if exp then statement endif
case when exp then statement end

"It doesn't seem to be standard in Access, unless I'm mistaken."

you are not mistaken, ms access is not standard

like, that's a surprise?

i do not have a copy of the standard, but i'm pretty sure one of the standard CASE forms is

CASE WHEN condition THEN expression ELSE expression END

where the WHEN/THEN part can be repeated

i think another form is

CASE expression WHEN value THEN expression ELSE expression END

where again the WHEN/THEN parts can be repeated, but they're all operating on the expression after the CASE keyword

rudy

You are right about the case being standard, but the statements



and



are also standard compliant, but not supported by many dbms. So there is no single portable statement that does what you want.

So why don't you do



and then check for null in the application?


aside:



is not a st.andard compliant statement.

Not sure about what sql server is.. nor if this is any different.. but I would type it as:

select x
from tbl
where x is null

double post

will ALWAYS return a boolean value of true or false or in the case of sql server 0 or 1 which are the boolean values


will only produce a boolean result if x is boolean and if the value of x is false it will return false when x is null!

Its not the same thing sorry.