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

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

"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

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
>
>
>

"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

"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)