db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: setNull in select doesn't work
Date Tue, 25 Jul 2006 07:11:15 GMT
Craig L Russell <Craig.Russell@Sun.COM> writes:

> Hi Marieke,
>
> You have hit one of the big usability issues with SQL. While many
> vendors have implemented "WHERE value = ?" such that it behaves
> exactly like "WHERE value IS NULL" in case the parameter passed is
> null, it isn't required by the governing standard, and is not
> therefore a bug in an implementation but a feature.
>
> To be portable, you need to have different SQL statements in the case
> where the parameter is null versus not null. And if you have "n"
> parameters, you might have to have "2 ^ n" different SQL statements,
> depending on whether each of the parameters is null or not.
>
> Sadly, this is the state of the art in today's database world.

Hi Craig,

Let's not get too negative. I don't think you need more than one
statement.

This is the simplest one, but it requires two calls to setNull/setXXX
per parameter:

  select * from mytable where value = ? or (? is null and value is null)

Alternatively, this query requires only one setNull/setXXX per
parameter and handles null:

  select * from (values (cast (? as varchar(128)))) params(value),
                mytable
           where mytable.value = params.value or
                 (params.value is null and mytable.value is null)

Not exactly beautiful, but it works! :)

-- 
Knut Anders

Mime
View raw message