Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
A bit more help with the Where bit |
Tue, May 29 2007 3:23 AM | Permanent Link |
"Paul" | Hi All,
Robert helped me get this query working : Select *, IF(CURRENT_DATE - Stock.DateAcquired < 30 THEN 'A' ELSE IF(CURRENT_DATE - Stock.DateAcquired < 60 THEN 'B' ELSE 'C')) As AgeFlag From Stock WHERE (Stock.Status IN ('STOCK','SOLD')) ORDER BY AgeFlag, Stock.StockId This works fine, but I need to allow the user to choose which age groups they want to see, so I tried to add in the AgeFlag into the where, but it doesn't like it. The following doesn't work. Can anyone tell me how I should do this ? Select *, IF(CURRENT_DATE - Stock.DateAcquired < 30 THEN 'A' ELSE IF(CURRENT_DATE - Stock.DateAcquired < 60 THEN 'B' ELSE 'C')) As AgeFlag From Stock WHERE ((Stock.Status IN ('STOCK','SOLD')) and (AgeFlag IN ('A','B','C'))) ORDER BY AgeFlag, Stock.StockId Thanks, Paul |
Tue, May 29 2007 4:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Paul
Basically you need to repeat the IF statement eg Select *, IF(CURRENT_DATE - Stock.DateAcquired < 30 THEN 'A' ELSE IF(CURRENT_DATE - Stock.DateAcquired < 60 THEN 'B' ELSE 'C')) As AgeFlag From Stock WHERE ((Stock.Status IN ('STOCK','SOLD')) and ( IF(CURRENT_DATE - Stock.DateAcquired < 30 THEN 'A' ELSE IF(CURRENT_DATE - Stock.DateAcquired < 60 THEN 'B' ELSE 'C')) IN ('A','B','C'))) ORDER BY AgeFlag, Stock.StockId Roy Lambert |
Tue, May 29 2007 9:09 AM | Permanent Link |
"Robert" | "Paul" <paul@pacsoftware.com.au> wrote in message news:E2A73205-3C08-4D0C-95E0-38AF0DCEF4E6@news.elevatesoft.com... > > This works fine, but I need to allow the user to choose which age groups > they want to see, so I tried to add in the AgeFlag into the where, but it > doesn't like it. Use a filter Query.Filter := 'AgeFlag in (''A'',''B'',''C'')', query.Filtered := true; The Robert |
Wed, May 30 2007 5:19 AM | Permanent Link |
"Paul" | Hi Guys,
thanks for the help. I actually used the filter event to get it working. Is this just as good as keeping it all in the sql itself. Any there any performance problems by using the filter instead of having it all in the sql staement ? Cheers, Paul "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message news:63A8627D-FFF5-4D74-82AC-1A2D9B24FEFB@news.elevatesoft.com... > > "Paul" <paul@pacsoftware.com.au> wrote in message > news:E2A73205-3C08-4D0C-95E0-38AF0DCEF4E6@news.elevatesoft.com... >> >> This works fine, but I need to allow the user to choose which age groups >> they want to see, so I tried to add in the AgeFlag into the where, but it >> doesn't like it. > > Use a filter Query.Filter := 'AgeFlag in (''A'',''B'',''C'')', > query.Filtered := true; > > The > > Robert > > > |
Wed, May 30 2007 8:41 AM | Permanent Link |
"Robert" | "Paul" <paul@pacsoftware.com.au> wrote in message news:5CBB7CCB-86C9-4F54-B559-3CDA0A926463@news.elevatesoft.com... > Hi Guys, > > thanks for the help. I actually used the filter event to get it working. > Is this just as good as keeping it all in the sql itself. Any there any > performance problems by using the filter instead of having it all in the > sql staement ? > Try it with your data and see, but in general the performance of DBISAM filters is fantastic. You should see little if any difference. One additional advantage of the filter, of course, is that it lets you refine the search without re-running the query. If the user selected A and B and then wants to see only A, you change the filter property and bingo, instant gratification. Robert |
Thu, May 31 2007 8:42 AM | Permanent Link |
Chris Erdal | "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in
news:0565C9BD-181E-4083-AA2E-522583E09905@news.elevatesoft.com: > > "Paul" <paul@pacsoftware.com.au> wrote in message > news:5CBB7CCB-86C9-4F54-B559-3CDA0A926463@news.elevatesoft.com... >> Hi Guys, >> >> thanks for the help. I actually used the filter event to get it >> working. ... > > ... in general the performance of DBISAM filters is fantastic. Careful! The filter EVENT is not as quick as the filter property, since it acts on every record in the original SQL query result set, whereas the filter property modifies the query to give a smaller result set. This will only be a problem in large result sets, though. -- Chris (XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.03 build 1) |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |