db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: setNull in select doesn't work
Date Tue, 25 Jul 2006 13:49:12 GMT



>
> 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! :)
>
>   

As Craig points out, SQL is not totally portable, it is getting better, 
but you if you are writing to multiple backends, you need to either 
externalize the SQL or use a technology which generates it for you.


While the code above might work, i think it would be confusing for 
someone who has to maintain it.

Regards
Lance

Mime
View raw message