Firedac multiparametric query with macro

Question

I created the main query that returns values for a whole, with 2 secondary conditions to restrict choice to be taken in the side combo box.

Everything works with the set parameters. I wish I could turn off or turn on these conditions with side combo box, how should I proceed?

my code is in Delphi:

procedure TForm1.Button3Click(Sender: TObject);
      begin
                  FDQuery3.Close;
                  FDquery3.Params[0].Value := Datetimepicker1.Date;
                  FDquery3.Params[1].Value := Datetimepicker2.Date;
                  FDQuery3.Params[2].Value := Combobox3.Items [Combobox3.Itemindex];
                  FDQuery3.Params[3].Value := Combobox5.Items [Combobox5.Itemindex];
                  FDQuery3.Open;

      end;

SQL text is:

select
  G.NUM_PROG,T.DATA,T.ORA,C.DESCRIZIONE, 
  (select DESKEY from ANAFORN where CODKEY=T.CODICE ) as Cliente,
  O.NOMINATIVO, T.TERMINALE,T.INCASSO 
from LG_RIGHE G 
  inner join LG_TESTA T on G.NUM_PROG =T.NUM_PROG 
  inner join OPERATORI O on T.OPERATORE = O.CODICE 
  inner join LG_CAUSA C on T.CAUSALE = C.CODICE 
where T.DATA >= :data1 
  and T.DATA <= :data2 
  and T.INCASSO = :pagamento 
  and T.TERMINALE = :terminale 
order by G.NUM_PROG  

i want turn on/off only Params[2][ and Params[3] (name: pagamento, terminale)

Answer 1

1) The typical way to optionally ignore a condition is to add one more "toggle" parameter. Consider this Delphi code

Result := True; // or False. Actually - some previous part of formula
If Need_Check_Option_A then
   Result := Result and ( Option_A > 20 ); 
If Need_Check_Option_B then
   Result := Result and ( Option_B < 10 ); 

Got the idea? But very long that is, is there a more concise way to write it ?

Result := .....some other parts.... 
   and (Ignore_Option_A or (Option_A > 20 ))
   and (Ignore_Option_B or (Option_A < 10 ))
   and ....

Now let's re-phrase it from Delphi to SQL WHERE clause

WHERE (.......) and (......)
  AND ( ( :Use_pagamento = 0 ) or ( T.INCASSO = :pagamento ) )
  AND ( ( :Use_terminale = 0 ) or ( T.TERMINALE = :terminale ) )

Whether you set that USE_xxxx parameter to zero (similar to false) then the second check would be shortcut out, ignored.

And the calling code would be something like

FDquery3.ParamByName('data1').AsDate := Datetimepicker1.Date;
FDquery3.ParamByName('data2').AsDate := Datetimepicker2.Date;
FDQuery3.ParamByName('pagamento').AsString := Combobox3.Items [Combobox3.Itemindex];
FDQuery3.ParamByName('terminale').AsString := Combobox5.Items [Combobox5.Itemindex];
FDQuery3.ParamByName('Use_pagamento').AsSmallInt := Ord( CheckBox3.Checked );
FDQuery3.ParamByName('Use_terminale').AsSmallInt := Ord( CheckBox5.Checked );

Some more suggestions follow:

2) using names like ComboBox3 are bad. You would not understand what they mean, what was they intended to be for. Look at your SQL - you give names there! You do not make it like

SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD3 < :PARAM1

And you have to give reasonable names to your Delphi objects too!
That FDQuery3, that Checkbox3 that Combobox5 - rename them all, give them some meaningful names!

3) you have a nested select there as the Cliente column. Unless very special circumstances that is slow and inefficient - change it to JOIN too (maybe to LEFT JOIN, if sometimes there is no matching value)

select
  G.NUM_PROG,T.DATA,T.ORA,C.DESCRIZIONE, 
--  (select DESKEY from ANAFORN where CODKEY=T.CODICE ) as Cliente,
  A.DESKEY as Cliente,
  O.NOMINATIVO, T.TERMINALE,T.INCASSO 
from LG_RIGHE G 
  inner join LG_TESTA T on G.NUM_PROG =T.NUM_PROG 
  inner join OPERATORI O on T.OPERATORE = O.CODICE 
  inner join LG_CAUSA C on T.CAUSALE = C.CODICE 
  /* left */ join ANAFORN A on A.CODKEY=T.CODICE 
where T.DATA >= :data1 
  and T.DATA <= :data2 
  AND ( ( :Use_pagamento = 0 ) or ( T.INCASSO = :pagamento ) )
  AND ( ( :Use_terminale = 0 ) or ( T.TERMINALE = :terminale ) )
order by G.NUM_PROG  

4) Depending on the circumstances you may just want to alter the SQL text. If the parameter would be ignored - then simply remove it!

This option is not universal, it has good and bad sides though. But in your case it would rather do good or nothing - because you have human to re-open the query and human would not be able to do it more often than once per second.

Good: then the server gets your SQL text it prepares the QUERY PLAN. The internal program of how to fetch your data. And it does not know yet what your parameters would be, so it prepares the PLAN to always check those parameters. Even if you later would ignore them. Sometimes it might make server choose slow PLAN where it could choose faster one if it knew the parameter would be not used. Sometimes it would make no difference. Game of luck.

Bad: if you keep the SQL text the same, then you can PREPARE the query once and the server would not build different PLAN when you re-open the query with different parameters. But if you do change the SQL text, then server would have to parse that new query and PREPARE the PLAN again before it would give you data. Sometimes it would take considerable time when you open-close queries, say, 1000 times per second. OF course, when you use a human to set those checkboxes, comboboxes and then press buttons, he would not do it that frequently, so in this case that risk is moot.

So in your case you might do something like this instead of introducing those toggle-parameters:

var qt: TStrings; // SQL query text
.....
qt := FDQuery3.SQL;

qt.Clear;  // changing SQL Text would auto-close the query
qt.Add('select G.NUM_PROG,T.DATA,T.ORA,C.DESCRIZIONE, ');
qt.Add('  A.DESKEY as Cliente, O.NOMINATIVO, T.TERMINALE,T.INCASSO ');
qt.Add('from LG_RIGHE G ');
qt.Add('  join LG_TESTA T on G.NUM_PROG = T.NUM_PROG ');
qt.Add('  left join ANAFORN A on A.CODKEY=T.CODICE');
qt.Add('  join OPERATORI O on T.OPERATORE = O.CODICE ');
qt.Add('  join LG_CAUSA C on T.CAUSALE = C.CODICE ');
qt.Add('where T.DATA >= :data1 and T.DATA <= :data2 ');

if CheckBox3.Checked then
   qt.Add('  and T.INCASSO = :pagamento ');
if CheckBox5.Checked then
   qt.Add('  and T.TERMINALE = :terminale ');

qt.Add('order by G.NUM_PROG'); 

FDquery3.ParamByName('data1').AsDate := Datetimepicker1.Date;
FDquery3.ParamByName('data2').AsDate := Datetimepicker2.Date;
if CheckBox3.Checked then
   FDQuery3.ParamByName('pagamento').AsString := Combobox3.Items [Combobox3.Itemindex];
if CheckBox3.Checked then
   FDQuery3.ParamByName('terminale').AsString := Combobox5.Items [Combobox5.Itemindex];

FDQuery3.Open;

In this option you do not introduce extra toggle-parameters, but instead you only add value-parameters when user checked to use them. If user unchecked them - then you do not include them into your SQL text and consequently you do not assign them any values (they would not be found anyway).

5) you may use BETWEEN - it may be easier to read.

...
where ( T.DATA BETWEEN :data1 AND :data2 )
  and T.INCASSO = :pagamento 
....