Filtering a TQuery using calculated fields

[manicfish]

I currently have a TQuery in which I would like to filter for min/max restrictions on a calculated field at runtime. I was hoping to use the OnFilterRecord event to do the comparison, but I forgot that calculated values are only calculated when necessary and not for an entire dataset at retrieval time (thus my FilterRecord procedure isn't accepting and rejecting records correctly). Is there a way to force the calculation before filtering, or is there another way around this?

 

[lespaul]

Could you do the calculation in the query itself and filter in the query?

For instance, if you need a count of a certain field and you only want to return records that have a count between 5 and 10:

SELECT Name, COUNT(RecordChanges)
FROM ChangeLog
GROUP BY Name
HAVING COUNT(RecordChanges) BETWEEN 5 AND 10

that way your recordset only has the correct information.

Leslie

 

[hilfy]

You can also order by the count so that you can see either the highest or lowest first. It looks something like this:

SELECT Name, COUNT(RecordChanges)
FROM ChangeLog
GROUP BY Name
HAVING COUNT(RecordChanges) BETWEEN 5 AND 10
ORDER BY 2

NOTE: In some databases the "Group By" will automatically order the data by the field(s) being grouped, but you can override this with "Order By".

-Dell

 

[manicfish]

Thankyou for your responses.

Unfortunately, I can't move the calculation to the query because it involves an IF statement to calculate the field I wish to apply the filter to (GrossProfit). The pricing for a specific product can be stored in a number of different ways (by lot, individual, carton, etc.) and thus the manner in which I calculate GrossProfit is dependent on the price type of a record (lotPrice, individualPrice x totalItemsProduced, totalItemsProduced / itemsPerCarton * cartonPrice, etc).

I could possibly do this by performing a union on separate queries based on price type, but I think that this would make the query too complicated (this is a fairly long query to begin with and I already have one union to account for a value not found in all records). To be honest, if there is no way that I can filter my dataset and still keep GrossProfit as a calculated field then I would rather not include the functionality.

 

[lespaul]

you can use a CASE statement in the SQL, here's a short example, without more details, I'm not sure if this will work:

case when TIMETYPE in ('OI','OO')
       then 'ORIENT'
       when TIMETYPE in ('TI','TO')
       then 'DLYPNL'
       else 'UNKNOWN' end    as newTIMETYPE

Leslie