I thought this was kind of odd.

If I have a TEDBQuery and put a filter on it in Delphi, like:

Filter := 'Cust_Id=123';
Filtered := true;

then the query works as long as Cust_Id is in the column list that is returned by the query.

select Cust_Id, Cust_Name from MyTable;  --The filter works ok

But if the filter column(s) do not appear in the column list, then I get a 401 "Column "Cust_Id" does not exist in the table".

select Cust_Name from MyTable;  --The filter fails with a 401

Is this the way it is suppose to work?

Barry

On 12/17/2013 6:26 PM, Barry wrote:
> If I have a TEDBQuery and put a filter on it in Delphi, like:
>
> Filter := 'Cust_Id=123';
> Filtered := true;
>
> then the query works as long as Cust_Id is in the column list that is returned by the query.
>
> select Cust_Id, Cust_Name from MyTable;  --The filter works ok
>
> But if the filter column(s) do not appear in the column list, then I get a 401 "Column "Cust_Id" does not exist in the table".
>
> select Cust_Name from MyTable;  --The filter fails with a 401
>
> Is this the way it is suppose to work?

I believe so - the filters work on query results sets (not underlying
data) so column needs to be returned.

I believe this is also client side processing (not 100% sure though) in
C/S case.

Since it's a query you can just include the where clause in the SQL to
accomplish what you want :

select Cust_Name from MyTable where Cust_Id=123;

Raul

Raul wrote:
>I believe so - the filters work on query results sets (not underlying
>data) so column needs to be returned.

>I believe this is also client side processing (not 100% sure though) in
>C/S case.

I am using C/S so I thought the filters were always done on the server, where it has access to all of the columns. I definitely don't want the filter to be executed on the client machine because it is a large table.

>Since it's a query you can just include the where clause in the SQL to
>accomplish what you want :

>select Cust_Name from MyTable where Cust_Id=123;

Sure. I thought I'd experiment with a Filter because I won't have to re-open the query when the filter changes.
But I'll end up using a parameterized query which will ensure the Where clause gets processed on the server.

Thanks for the feedback.

Barry

Raul,

<< I believe this is also client side processing (not 100% sure though) in
C/S case. >>

Server-side.  The client-side actually does very little, other than handle
the OnFilterRecord stuff (which it has to, obviously).

Tim Young
Elevate Software
www.elevatesoft.com

Tim,
<< I believe this is also client side processing (not 100% sure though) in
C/S case. >>

<Server-side.  The client-side actually does very little, other than handle
the OnFilterRecord stuff (which it has to, obviously).>


Sooooooo, why does TEDBQuery.Filter require the columns in the Filter also be in the column list of the Select statement? If the filtering is done on the server, why can't it access the filter columns in the table?

I can certainly understand this requirement if I was doing an OnFilterRecord event, but I was using the Filter Property of the TEDBQuery.

Inquiring minds want to know.

Barry

Barry,

<< Sooooooo, why does TEDBQuery.Filter require the columns in the Filter
also be in the column list of the Select statement? If the filtering is done
on the server, why can't it access the filter columns in the table? >>

Because you're filtering the query result set, not the source tables.  If
you want to filter the source tables, then you need to add the filtering to
the original query.  Filters are *not* additional WHERE clause expressions
for queries, they are applied to the query result set cursor with no
knowledge that the underlying rows came from a particular source.

Tim Young
Elevate Software
www.elevatesoft.com