Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
NULL in a parameter |
Tue, Sep 7 2010 6:48 AM | Permanent Link |
Peter | 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 |
Tue, Sep 7 2010 7:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Sep 7 2010 9:05 PM | Permanent Link |
Peter | 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 |
Wed, Sep 8 2010 3:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sun, Sep 12 2010 8:44 PM | Permanent Link |
Peter | 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 |
Mon, Sep 13 2010 3:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Sep 13 2010 3:16 AM | Permanent Link |
Uli Becker | 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 |
Mon, Sep 13 2010 8:32 PM | Permanent Link |
Peter | 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 |
Tue, Sep 14 2010 2:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Sep 14 2010 6:05 AM | Permanent Link |
Uli Becker | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |