Hi can you help to write correct Query for Filter 3 Data fields
Hi can you help to write correct Query for Filter 3 Data fields
for Example
Data1 , Data2 , Data2
txt1 = Data1
txt2= data2
txt3 = data3
ex1: Data1 and Data 3
ex2: Data 3 and Data2
ex3: Data 1, Data 2 Data 3
Qury.Close;
Query.Sql.Clear;
Qury.Sql.Add (Select * From Table1);
If Not (txt1.text = ' ')then
Begin
Qury.Sql.Add(Format ('Where Data1= ' '%s' ' ',[txt1] ));
end;
If not (txt3.text = ' ') then
Begin
Qury.Sql.Add(Format ('and Data3= ' '%s' ' ',[txt1] ));
end;
lets say txt1 one is blank then how to write correct code ?
for Example
Data1 , Data2 , Data2
txt1 = Data1
txt2= data2
txt3 = data3
ex1: Data1 and Data 3
ex2: Data 3 and Data2
ex3: Data 1, Data 2 Data 3
Qury.Close;
Query.Sql.Clear;
Qury.Sql.Add (Select * From Table1);
If Not (txt1.text = ' ')then
Begin
Qury.Sql.Add(Format ('Where Data1= ' '%s' ' ',[txt1] ));
end;
If not (txt3.text = ' ') then
Begin
Qury.Sql.Add(Format ('and Data3= ' '%s' ' ',[txt1] ));
end;
lets say txt1 one is blank then how to write correct code ?
A common pattern is to use "select ... where 1=1", as you can then safely append "and ... " clauses as you please without worrying.
ReplyDeleteThough for user input I use parameters and write "and ((:Name is null) or (Name = :Name))". For our database the query optimizer handles this just fine.
Of course I take care to clear all param values after closing the dataset, so I can just set those I need to.
Like Asbjörn says... I use Firebird and i would write:
ReplyDeleteselect *
from table
where (:flda is null or flda = :flda) and
(:fldb is null or fldb = :fldb) and
(:fldc is null or fldc = :fldc);
Prepare that and then .clear the parameters that i do not want to use in the test. Execute, .Clear + Set, Execute... fast safe and efficient.
If you are using UniDAC, then the following would work:
ReplyDelete1) Assign base SQL query (without WHERE clauses) in the Object inspector or right in the code, like this:
QProduct.SQL.Text := 'select * from TABLE1';
2) When building the detailed query, use the already described procedure to add the necessary WHERE clauses:
QProduct.Active := FALSE;
// reset SQL to the base without any WHERE
QProduct.DeleteWhere;
if txt1.Text <> '' then QProduct.AddWhere('BrandName='+QuotedStr(txt1.Text));
// ... etc... (add other where's if necessary)
3) And finally reopen the query:
QProduct.Active := TRUE;