Query with multiple ands (macros)

[Alex2027]

Hi,

I have a query like:

SELECT * FROM table_name
WHERE col1 = "123"
AND col2 = "456"
AND col3 = "789"
AND col4 = "101112"

But the users will be able to search by any combination of the columns e.g. col1 and col4 or just
col 3.

Currently using macros I have:

SELECT * FROM table_name
&WHERE
&COL1
&AND1
&COL2
&AND2
&COL3
&AND3
&COL4

but turning on and off this macros in code is a bit messy and as they request more columns
in the query its only going to get worse, is there a better way to handle this?

Thanks

Alex

[AndreyZ]

Hello,

You can use the AddWhere method instead of macros. Here is a code example:

Code: Select all

MyQuery1.SQL.Text := 'select * from tablename';
for i := 0 to FieldsCount - 1 do // FieldsCount is a number of fields that user can search by
  if IsFieldSelected(FieldNames[i]) then // IsFieldSelected checks if user wants to use a field in searching, FieldNames contains name of fields that user can search by
    MyQuery1.AddWhere(FieldNames[i] + '=' + FieldValues[i]); // FieldNames contains field values that user wants to search by

This way, there can be any number of fields in a dataset. This code is just an example, you should tweak it for your way of storing user-input information.

[Alex2027]

Hi Andrey,

This is perfect thanks.

I'm using the SQL stored in the component, Is there a way remove the wheres after you are done? I tried closing the connection and reopening and it runs it again with the added wheres.

Alex

[AndreyZ]

You should use the DeleteWhere method like this:

Code: Select all

MyQuery1.DeleteWhere;

[Alex2027]

works great, thanks for the help.

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.