At 09:01 AM 3/06/2004 +0800, you wrote:

>Hi Helen
>
>Helen Borrie wrote:
>
> > At 01:43 PM 2/06/2004 +0800, you wrote:
> > >Hi guys,
> > >
> > >I have this statement execute in ibexpert and the result was the one I
> > >expected when all parameters are null. But in IBO this statement fetches
> > >all the records from the table? Is this kind of statement not valid?
> > >Please advise thanks
> > >
> > >select *
> > >from customer
> > >where status != 'CAN' and (name containing :null or name starts with
> > :null2)
> >
> > >name containing :null or name starts with :null2
> >
> > What is being passed in these parameters?
> >
> > Helen
> >
>The value being pass is from a editbox.text which is null.

OK, then, to endorse Svein's comments...

1. editbox.text will never be null. If it is blank, its value is EmptyStr.
The SQL that is passed here will fetch every row whose status is not 'CAN'

2. All equality comparisons with NULL result in false. There are only two
NULL tests that are capable of returning true: IS NULL and IS NOT NULL.

3. CONTAINING NULL and STARTING WITH NULL are not valid tests. A string
is either null or not null. NULL is not a value. It is a state that
indicates "no known value".

Now, back to your statement. If you want your search to find all rows
where status is not 'CAN' and name is null, take out the parameters and use
this statement:

select * from customer
where status <> 'CAN'
and name is null

Helen