Hello

I have an OnDataChange event where I examine the Master table, then generate some SQL to select a detail table. It is something like SELECT FName, LName, DoB from CLIENT WHERE FName = :FName AND LName = :LName AND Dob = :DoB. Both Querys are referencing the same table - the detail displays duplicates.

It works great, unless one or more fields are NULL. Using D7 I have tried...

If EDBQ1.FieldByname('FName').IsNull then
  EDBQ2.ParamByName('FName').Clear
else EDBQ2.ParamByName('FName').AsString := EDBQ1.FieldByname('FName').AsString;

on both the FName & DoB fields but it will not find the records that I know are there. It is complicated by having a GROUP BY at the end of the SELECT, which includes the 2 fields that I know contain NULLS in a couple of records, so is that the issue? If so how does one handle a NULL in a GROUP BY?

Thanks in advance

Peter

Peter


First a plea - please include the version of ElevateDB you're using.

Secondly an answer. If you're using a version prior to 2.04 then you'll need to use syntax such as

WHERE (FName = :FName OR FName IS NULL)

If you're using 2.04 (I haven't installed it yet) then there's an engine property to use the equals operator, but since I haven't used it I'm not sure if you need

WHERE (FName = :FName OR FName =NULL)

or just

WHERE FName = :FName

Burt since you're having trouble I suspect either you still need both tests or you're using a version prior to 2.04

Roy Lambert [Team Elevate]

Roy

Good point - I am still using 2.03.

Your response is correct in the context of an SQL statement, but how do I pass that via a parameter?

Regards

Peter

Peter


Do you want just the NULLs or the the NULLs as well.


If the latter then as my last post. If the former then alter the SQL to

COALESCE(FName,'') = :FName

and pass '' as the parameter

Roy Lambert Roy Lambert [Team Elevate]

Roy

Of course, that makes sense. Thanks for the tip. But how do I do a similar thing with a date field?

I tried a number of combinations, but failed...
AND COALESCE(DoB, DATE '1899-01-01') = :DoB

etc etc.

Regards

Peter

Peter


Being telepathic I can tell you're just sending in a null parameter

Try this

select _HIdealarm, coalesce(_hidealarm,timestamp '1899-01-01') from calls
where
coalesce(_hidealarm,timestamp '1899-01-01')
=
coalesce(:INDATE,timestamp '1899-01-01')

I used a timestamp column cos it was the first one I found with nulls but the principle should be the same.

Roy Lambert

Peter,

> I tried a number of combinations, but failed...
> AND COALESCE(DoB, DATE '1899-01-01') = :DoB

BTW: Date '1899-12-30' is the equivalent of 0.

Uli

Roy

<<Being telepathic I can tell you're just sending in a null parameter >>

Sorry, I still haven't explained the situation despite your questions. The whole SQL statement involves the Client table looking for records that match the client's FName, LName and DoB fields. I know that there are records that contain NULLs in both the FName and DoB fields, but the current parameterised query does not find those records.

So, yes, the parameter in each case is sometimes NULL. The FName now works a treat, but I cannot resolve how to select the 2 records that I know to exist, where both the FName and DoB fields are NULL. I wouldn't have thought of COALESCEing the parameter, and it doesn't blow up, but it doesn't find the records either

       '  AND  (COALESCE(P.FName, '''') = :FName)'+
       '  AND  (COALESCE(P.DOB, DATE ''1899-12-30'') = (COALESCE(:DOB, DATE ''1899-12-30'')))'+

. I tried a number of treatments on the Parameter too, such as

 if EDBQP.FieldByName('DOB').IsNull then
  EDBQ1.ParamByName('Dob').AsDateTime := 0
 else EDBQ1.ParamByName('Dob').AsDateTime := EDBQP.FieldByName('DOB').AsDateTime;

which I thought would be the same as Uli's suggestion, and the traditional

 if EDBQP.FieldByName('DOB').IsNull then
  EDBQ1.ParamByName('Dob').Clear

...to no avail.

*Interestingly*, if I transfer the statement to the EDBManager, and pass the valid LName of the client whom I know has duplicates, and pass an empty string as the parameter for the FName field, then pass a date parameter of 1899-12-30 for the DoB field, I see the 2 records!

If I change the FName parameter in EDBManager to NULL, I get no records. So I assume my D7 code that uses

EDBQ1.ParamByName('FName').AsString  := UpperCase(EDBQP.FieldByName('FName').AsString);

has the appropriate effect in that it passes an empty string.

Its a tough one, and thanks for your help so far.

Regards

Peter

Peter


>
>*Interestingly*, if I transfer the statement to the EDBManager, and pass the valid LName of the client whom I know has duplicates, and pass an empty string as the parameter for the FName field, then pass a date parameter of 1899-12-30 for the DoB field, I see the 2 records!

I only tried the statement I posted in EDBManager. I do remember a post a while ago about how to get null into a query as a parameter (I think)

One suggestion would be an external function, a second would be to move to 2.04 where the non-standard syntax can be used (so field = NULL would work).

Can you post a demo db/app into the binaries. My brain works much better when I have something real to work on.

Roy Lambert [Team Elevate]

Peter,

it might be different in 2.04, but the way I use in such cases is:

(pseudo-code, not tested)

sql.add('Select * from MyTable where true ');

if FName is NULL then
  sql.add('and FName is NULL')
else
begin
  slq.add('and FName = :ParamFName');
  ParamByName('ParamFName').asString := FName;
end;
if LName is NULL then
  sql.add(' and LName is NULL ')
else
begin
  slq.add(' and LName = :ParamLName ');
  ParamByName('ParamLName').asString := LName;
end;

etc.

Regards Uli