ginsonic
asked on
Multiple DB filtering
I use next code to filter my table :
table1.filter := 'GRUPA= ''A05AA02'' or GRUPA= ''A06AD11'' or GRUPA= ''A07AA11'' or GRUPA= ''C03AA03'' or GRUPA= ''C03CA01'' or GRUPA= ''C03DA01'' or GRUPA= ''C07AA05'' or GRUPA= ''H02AB04'' or GRUPA= ''J01CA01'' or GRUPA= ''J01MA02'' or GRUPA= ''J01MA06'' or GRUPA= ''J05AF05'' or GRUPA= ''L04AX01'' or GRUPA= ''V03AC01'' or GRUPA= ''H02AB07''';
table1.filtered := true;
The problem is that my string is longer than 256 characters and I get an error message when compile the project.
Any suggestion how can I step over this problem?
table1.filter := 'GRUPA= ''A05AA02'' or GRUPA= ''A06AD11'' or GRUPA= ''A07AA11'' or GRUPA= ''C03AA03'' or GRUPA= ''C03CA01'' or GRUPA= ''C03DA01'' or GRUPA= ''C07AA05'' or GRUPA= ''H02AB04'' or GRUPA= ''J01CA01'' or GRUPA= ''J01MA02'' or GRUPA= ''J01MA06'' or GRUPA= ''J05AF05'' or GRUPA= ''L04AX01'' or GRUPA= ''V03AC01'' or GRUPA= ''H02AB07''';
table1.filtered := true;
The problem is that my string is longer than 256 characters and I get an error message when compile the project.
Any suggestion how can I step over this problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It also works when splitting up the string in your code without the use of variables:
Table1.Filter := 'first part < 255 charachters' +
'second part < 255 charachters' +
'third part < 255 charachters' +
'etc...';
Regards Jacco
Table1.Filter := 'first part < 255 charachters' +
'second part < 255 charachters' +
'third part < 255 charachters' +
'etc...';
Regards Jacco
With complex filters that change often it might also be better to use the OnFilterRecord event.
Regards Jacco
Regards Jacco
i would use a tquery instead a ttable,
if there are such complex (where) clauses
affected
meikl ;-)
if there are such complex (where) clauses
affected
meikl ;-)
ASKER
kretzschmar can you tell me more ? Untill now I have used just table+dataSource+DBGrid in my projects :(
I wish to say that I have couple situations when I use ~50 conditions for filtering .
I wish to say that I have couple situations when I use ~50 conditions for filtering .
ASKER
simonet , your suggestion get me an error message alike 'Operation not applicable' for this.
guessing simonets suggestion
is not supported by your database :-(
you can do the same as the ttable with a tquery+tdatasource+tdbgrid +tupdatesq l (as updateobject of the tquery, if editing, deletes and inserts may possible)
lets say we have a sql-statement like
select * from atable
this shows all entries of atable
if you want to filter, you modify the sql-statement like
select * from atable where condition
you can do this on the fly like
function get_filter_sql(atable, awhereclause : String)
const
sqltextnowhere := 'select * from atable %s';
sqltextwhere := sqltextnowhere+' where %s';
begin
if awhereclause = '' then
result := format(sqltextnowhere,[ata ble])
else
result := format(sqltextwhere,[atabl e,awherecl ause])
end;
usage sample
query1.close;
query1.sql.text := get_filter_sql(yourtable,y ourfilter) ;
query1.open;
hope this helps
meikl ;-)
is not supported by your database :-(
you can do the same as the ttable with a tquery+tdatasource+tdbgrid
lets say we have a sql-statement like
select * from atable
this shows all entries of atable
if you want to filter, you modify the sql-statement like
select * from atable where condition
you can do this on the fly like
function get_filter_sql(atable, awhereclause : String)
const
sqltextnowhere := 'select * from atable %s';
sqltextwhere := sqltextnowhere+' where %s';
begin
if awhereclause = '' then
result := format(sqltextnowhere,[ata
else
result := format(sqltextwhere,[atabl
end;
usage sample
query1.close;
query1.sql.text := get_filter_sql(yourtable,y
query1.open;
hope this helps
meikl ;-)
ASKER
Look a little more complicated . What's the difference between my filtering and the query's one ?
What gain using a query ?
What gain using a query ?
usual on sql-databases it is recommended to work with queries
-lower overhead
-faster results
-you can order the result by any field(s) with an order by clause
there should be no difference
in the result of the query
and the filtering
in your case, you use dbase,
i guess from previous q's,
it is a workaround
meikl ;-)
-lower overhead
-faster results
-you can order the result by any field(s) with an order by clause
there should be no difference
in the result of the query
and the filtering
in your case, you use dbase,
i guess from previous q's,
it is a workaround
meikl ;-)
HI, ginsonic,
If you don't want to use TQuery component and use TTable one
for your purpose it would be reasonable to use OnFilterRecord
event.
Create TStringList, fill it with your specific strings
('A05AA02', ...) and write the following
Accept:=StringListName.Ind exOf(GRUPA .AsString) <>-1.
That's all.
Sincerely,
Nestorua.
If you don't want to use TQuery component and use TTable one
for your purpose it would be reasonable to use OnFilterRecord
event.
Create TStringList, fill it with your specific strings
('A05AA02', ...) and write the following
Accept:=StringListName.Ind
That's all.
Sincerely,
Nestorua.
yep, this may also a solution, Nestorua
ASKER
I choice a different way to step over my problem ( not proposed here ) but this is the first working solution .
Thanks to all experts,
Nick aka ginsonic
Thanks to all experts,
Nick aka ginsonic
table1.filter := '( GRUPA IN (''A05AA02'', ''A06AD11'', ''A07AA11'', ''C03AA03'' , ''C03CA01'', ''C03DA01'', ''C07AA05'', ''H02AB04'', ''J01CA01'', ''J01MA02'', ''J01MA06'', ''J05AF05'', ''L04AX01'', ''V03AC01'', ''H02AB07''))';
table1.filtered := true;
if the code above works it will not only sole the large string problem, but the filtering will also be faster.
Yours,
Alex