Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
SQL Query with Parameters where a value is NULL? |
Wed, Jan 30 2008 7:22 PM | Permanent Link |
Dave | I have a Query
say SELECT Client, Company, TheDate from MyTable --> Works fine then if I SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<= :toDate MyQuery.ParamByName('fromDate').AsDate := DateTimePicker1.Date; MyQuery.ParamByName('toDate').AsDate := DateTimePicker2.Date; MyQuery.Open; Works fine BUT when I am using a Parameter Date search like this, if one of the records has a NULL value the Query hangs. I have tried SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<=:toDate AND TheDate IS NOT NULL But it still happens. If I set the date range to records where I know that TheDate is not NULL it works fine but as soon as TheDate has a NULL value it hangs. I need some records to have this NULL value and even when I include "TheDate IS NOT NULL" it still hangs. Am I doing something wrong? Thanks |
Wed, Jan 30 2008 7:58 PM | Permanent Link |
Dave | Dave <david@itfx.com.au> wrote:
I have a Query say SELECT Client, Company, TheDate from MyTable --> Works fine then if I SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<= :toDate MyQuery.ParamByName('fromDate').AsDate := DateTimePicker1.Date; MyQuery.ParamByName('toDate').AsDate := DateTimePicker2.Date; MyQuery.Open; Works fine BUT when I am using a Parameter Date search like this, if one of the records has a NULL value the Query hangs. I have tried SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<=:toDate AND TheDate IS NOT NULL But it still happens. If I set the date range to records where I know that TheDate is not NULL it works fine but as soon as TheDate has a NULL value it hangs. I need some records to have this NULL value and even when I include "TheDate IS NOT NULL" it still hangs. Am I doing something wrong? Thanks OK - If I remove the parameters and replace with WHERE TheDate >= Date'2001-01-01' AND TheDate <=Date'2008-01-01' Its works perfectly. Why wont the parameter query work for me with this? Thanks again |
Thu, Jan 31 2008 3:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dave
I don't know about the parameter part but if you want the null dates shouldn't it be an OR not an AND Roy Lambert |
Thu, Jan 31 2008 7:15 AM | Permanent Link |
Dave | Hi Roy,
I actually want to exclude NULL Dates, but was mainly doing it to try and get the Parameter working. The thing is I take out the Parameters and code in the Dates to the SQL and it works fine. Using the Params with Dates, and it hits a NULL it just hangs. Maybe it is just something I am doing wrong, but I am happy I have it working even though I had to remove the Params, but I think using parameters is more professional. Dave |
Thu, Jan 31 2008 5:05 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Works fine BUT when I am using a Parameter Date search like this, if one of the records has a NULL value the Query hangs. >> What version of EDB are you using ? There was an issue like this that was resolved recently: http://www.elevatesoft.com/scripts/incident.dll?action=viewrep&category=edb&release=1.06&type=f&incident=2493 The incident report describes accessing BLOB columns, but the issue could have cropped up in other scenarios because the main cause was the parameters and the insensitive (TEDBQuery.RequestSensitive:=False) result set. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 31 2008 5:26 PM | Permanent Link |
Dave | Hi Tim,
I have the latest version, BUT I have recently discovered I havent cleaned up the library paths before installing. So this could mean that even though I have installed the latest version, the library path if pointing to the wrong version. I might clean that up first. Thank you for all your help |
Fri, Feb 1 2008 1:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< So this could mean that even though I have installed the latest version, the library path if pointing to the wrong version. >> Yep, that will do it. I'll have to see about getting the uninstall to remove the old paths. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |