Subject Re: [firebird-support] how NULL compares
Author Mauro Formigoni Junior
d_dude_2003 wrote:
> Hi there guys,
>
> I have a table like
>
> ID INTEGER
> TIMEIN TIME
> TIMEOUT TIME
>
> populates with records like
> 1 Time1 <Null>
> 2 <Null> Time2
> 3 Time3 <Null>
> 4 <Null> Time4 etc.
>
> If i use
>
> SELECT *
> WHERE (TIMEIN > Param1) AND (TIMEIN < Param2) AND (TIMEOUT > Param1)
> AND (TIMEOUT < Param2)
>
> will it return the right records and NULL is not being compared here?
>
> Hope u get it :)
>
> Thanx alot,
> Eugene.
>

NULL is not major nor minor nor equal to any value, this select will not
return nothing.

To work with NULL values use the coalesce function like:

select * where coalesce(timein, '01/01/1900') > Param1

The engine replaces the NULL values found in timein field with the
second parameter in the function ('01/01/1900' in this case).

So happens the following:

timein is null?
- if yes: '01/01/1900' > Param1?
- if no: timein > Param1?

if the "second" condition is True then return the record.

Mauro




_______________________________________________________
Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe! www.yahoo.com.br/messenger/promocao