ado.filter problem

[news.krz.org]

Hello,

I have a tricky problem using ADO.Filter in Delphi. (DBGo)

I want to set a little complex filters like that:

Rs.Filter:='([Col1]=1 OR [Col1]=2) AND [Col2]=3'

I already found out that this is not supported by ADO. I have to use:

Rs.Filter:='([Col1]=1 AND [Col2]=3) OR ([Col1]=2 AND [Col2]=3)'

because OR has to be in the outermost Filter-Loop. (Don't ask me why - this
is ADO regularity).

So far so good: My Problem: I have to set filters on at leaset four columns.
And to meet the specification of ADO I have to create cartesic products of
all Filter-states (and this can be hundrets).

So I am looking for a new solution!

My suggestion was to filter a recordset for one column, then transfer the
filtered rows to a second recordset, then filter for the next column, and so
on.

Rs1.Filter:='[Col1]=1 OR [Col1]=2'
Rs1.Filtered:=true
Rs2.Recordset:=Rs1.Recordset
Rs2.Filter:='[Col2]:=3'
Rs2.Filtered:=true
...

Unfortunately this doesn't seem to work because
Rs2.Recordset:=Rs1.Recordset
seems to transfer all rows to Rs2. My hope was that only the filtered rows
are transfered.
So after Rs2.Recordset:=Rs1.Recordset the recoredsets are exactly the same
(including the Filter).

So I am out of ideas. Do you have any? Is there a possibility to copy only
the filtered rows to a new recoredset?

Greetings,
Freddy

[Bob Barrows]

news.krz.org wrote:
> Hello,
>
> I have a tricky problem using ADO.Filter in Delphi. (DBGo)
>
> I want to set a little complex filters like that:
>
> Rs.Filter:='([Col1]=1 OR [Col1]=2) AND [Col2]=3'

Is this a simplification of your actual problem? If not, this might
suit:

Rs.Filter:= '[Col1] <= 2 AND [Col2] = 3'

Of course, if Col1 could contain values that are less than 1, then you
could do this:
Rs.Filter:= '[Col1]>=1 AND [Col1] <= 2 AND [Col2] = 3'

If fractional values are present, then I take this all back. The point
is, have you eliminated all possibilities of avoiding OR?

>
> I already found out that this is not supported by ADO. I have to use:
>
> Rs.Filter:='([Col1]=1 AND [Col2]=3) OR ([Col1]=2 AND [Col2]=3)'
>
> because OR has to be in the outermost Filter-Loop. (Don't ask me why
> - this is ADO regularity).
>
> So far so good: My Problem: I have to set filters on at leaset four
> columns. And to meet the specification of ADO I have to create
> cartesic products of all Filter-states (and this can be hundrets).
>
> So I am looking for a new solution!

Can this filtering be pushed back to the source database? That would be
my preference if possible.

>
> My suggestion was to filter a recordset for one column, then transfer
> the filtered rows to a second recordset, then filter for the next
> column, and so on.
>
> Rs1.Filter:='[Col1]=1 OR [Col1]=2'
> Rs1.Filtered:=true
> Rs2.Recordset:=Rs1.Recordset
> Rs2.Filter:='[Col2]:=3'
> Rs2.Filtered:=true
> ...
>
> Unfortunately this doesn't seem to work because
> Rs2.Recordset:=Rs1.Recordset
> seems to transfer all rows to Rs2. My hope was that only the filtered
> rows are transfered.
> So after Rs2.Recordset:=Rs1.Recordset the recoredsets are exactly the
> same (including the Filter).
>
> So I am out of ideas. Do you have any? Is there a possibility to copy
> only the filtered rows to a new recoredset?
>

Check out the Clone method. Use Clone to create a new recordset (make
sure the recordset is disconnected from the source database), then
delete all the records from the clone, then add the filtered records to
it by looping through the original filtered recordset. Create a new
empty clone. Apply the new filter to the first clone, then loop through
it to add the records to the second clone. Then ... ughhh! Are you sure
you can't do this filtering in the database?

--
HTH,
Bob Barrows

[news.krz.org]

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> schrieb im Newsbeitrag
news:eq1JWiWa...@TK2MSFTNGP02.phx.gbl...

Hello Bob,

thanks for your hints. I will check out the clone method. Maybe this
works...?

Indeed this was a simplification: The real filter will look like:
Example:
Filter:='([Col1]=1 OR [Col1]=2 OR [Col1]=3 OR [Col1]=4) AND ([Col2]=X OR
[Col2]=y OR [Col2]=z) AND ([Col3]=q OR [Col3]=w OR [Col3]=z OR [Col3]=g)
([Col4]=1 OR [Col4]=2 OR [Col4]=3 OR [Col4]=4)'

And the filter-statement is highly dynamical. Because the user can modify
this with only one mouseclick.

Ok, sourcing the database directly is a solution that will definitely work.
Problem: The row number is mentionable so this solution is time critical
(and network capacity also problem).
Using the ADO-DataSet-Filter method would be very, very fast :-)))) -> Very
smart :-)

regards,
Freddy