Uli


That approach is fine when you build the SQL dynamically. If you want to use parameters (eg when the query is inside a loop) you loose all the advantages of preparing the query.

With that caveat it will certainly work - I know I have to do that with a number of queries (even some inside loops  )

Roy Lambert [Team Elevate]

Roy,

> That approach is fine when you build the SQL dynamically. If you want to use parameters (eg when the query is inside a loop) you loose all the advantages of preparing the query.
>
> With that caveat it will certainly work - I know I have to do that with a number of queries (even some inside loops  )

You are right, but I don't see another way to pass the NULL-parameters
except using 2.04 which I havent' tested yet.

Uli

I have a feeling I'm not awake enough to understand the problem, but
have you tried...

select lastname, firstname, dob from tablex where
coalesce(cast(dob as varchar(10)), '') = :d1

Set d1 (whose datatype is ftString) to '' to find nulls, or in the form
'YYYY-MM-DD' for non-nulls.

Charles


>select lastname, firstname, dob from tablex where
>coalesce(cast(dob as varchar(10)), '') = :d1
>
>Set d1 (whose datatype is ftString) to '' to find nulls, or in the form
>'YYYY-MM-DD' for non-nulls.

That's excellent.

Roy Lambert [Team Elevate]

All

I moved up to 2.04 but still didn't get the params to work in a fashion similar to the DBISAM manner. I didn't try the CAST method which looks good, as I had already decided to use dynamic SQL, where I format one of the arguments to be either AND (DoB IS NULL) or AND (DoB = DATE (FormatDateTime(... etc.

I'll try the CAST method when I get more time.

Thanks for your suggestions.

Regards

Peter