Limitations of TQuery.Filter property

[FrankThynne]

I have been caught by the limitation that TQuery.Filter expressions can compare columns with constants but not with other columns. They do not support arithmetic or calculated fields either.

The problem arises because I'm trying to produce a result set in which the user can tighten or relax the query conditions at run time. For example, on checking or clearing a check box the user should be able to add or remove the condition 'Columna = Columnb', or add or remove the condition 'Columna-Columnb-Columnc > 0'.

At present my only work-around is to have different queries for these conditions, which requires me to close and reopen the TQuery objects which introduces an unwelcome delay in responding to the user action.

I should explain that the 'columna=columnb' case involves additional terms in a join expression (which is a bigger overhead if I'm changing the SQL). The 'Columna-Columnb-Columnc>0' case applies to the data from one table. In both cases, I can't see why the filter (which is applied locally, I believe) should care how the columns are produced.

And, yes, I've tried calculating the arthmetic expression in the SQL and using the resulting column in the Filter: that isn't supported either!

 

[LucieLastic]

What about creating a view of the result set and selecting & filtering that in delphi.

What database are you using ?

lou

 

[Robertio]

The best solution will depend upon what database you are using, number of records you are processing and whether or not the result set is to be editable for the user. With a small set of results where losts of different filters are likely to be applied you would probably be best using a client dataset and filtering on that.

The easiest solution (assuming you are just using a TQuery component) would be to use the OnFilterRecord event, it allows you to compare fields (IIRC)

 

[FrankThynne]

Re: creating a view
Because of an existing environment, I'm using Paradox with BDE. I don't think this supports the CREATE VIEW command - or have I misunderstood your suggestion?

 

[FrankThynne]

I'll have a look at client datasets, but hoped to avoid the added layer with all the component set-up. I'll also look up to see what can be done in OnFilterRecord.

Thank you, weez and Robertio, for your suugestions.

 

[FrankThynne]

Thanks, weez, using the OnFilterRecord event did exactly what I needed although, for the time being, it has caused me to create some circular references between the form and its dataset. Removing these is on the ToDo list!