Dynamic parametric queries

As I wrote the other day, there is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 rows in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. Although I have sorted out the problem of transferring that data to Excel, there remains the thorny question do I need to display all 4,000 rows every time I display the report? What if I only want receipts which have been issued since 01/03/2013 or cash receipts over 1,000 NIS?

What is needed is some form of preliminary screen with edit boxes which correspond to fields in the report. If the user places a value in an edit box, then the report (or more accurately, the query) has to take this value into account, whereas if the edit box is empty, then it should be ignored.

After some cogitation, I decided to use place a page control component on my form, with two different tab sheets. The first sheet would hold my preliminary, 'parameters', components whereas the second would hold the 'results' grid. I worked up a simple example with two edit boxes whose titles are 'from date' and 'to date'. The parameters sheet also has a 'show' button which builds the query, opens it and displays the results sheet.

My original query was in the form 'select <nine fields> from <some tables>'. The query has no parameters which is why 4,000 rows were being returned. I very naively wrote something like the following pseduocode which is executed when the 'show' button is pressed.

check whether 'fromdate' is empty or holds a valid date
if not, exit
check whether 'tilldate' is empty or holds a valid date
if not exit

whereflag:= false;
copy the original query to string 'newquery'
if 'fromdate' is not empty then
 begin
  if whereflag is false then  
   begin
    whereflag:= true
    append ' where' to newquery
    end
   else append ' and' to newquery
  append ' receipts.curdate ' to newquery

  if 'tilldate' is not empty then
   begin
    append '  between :p1 and :p2' to newquery
    parambyname ('p1').asdate:= strtodate (fromdate)
    parambyname ('p2').asdate:= strtodate (tilldate)
   end
  else
   begin
    append ' >= :p1' to newquery
    parambyname ('p1').asdate:= strtodate (fromdate)
   end
 end
else if 'tilldate' is not empty then
 begin
  if whereflag is false then
   begin
    whereflag:= true
    append ' where' to newquery
   end
  else append ' and' to newquery
  append ' receipts.curdate >= :p2' to newquery
  parambyname ('p2').asdate:= strtodate (tilldate)
 end

This mess did actually work but it was clear that it was far too wordy. As I wanted two paired values (dates and amounts) and two non-paired values (receipt number and payment type), it was clear that the above was not amenable to expansion. My first optimisation was to remove the 'whereflag' variable: it was needed so that I could append 'where' to the standard query for the first new statement, whereas every following statement would use 'and'. I realised that if I added the meaningless statement 'where 1 = 1' to the standard query, then every dynamic statement could start with 'and'.

The second optimisation came when I realised that being too clever and that using 'between' was counter productive: it would be easier to build the dynamic query if I had a separate clause for each edit box. In other words, instead of writing

and receipts.curdate between :p1 and :p2

I could write

and receipts.curdate >= :p1
and receipts.curdate <= :p2

This lead to the third optimisation which was checking all the edit boxes in one loop to see which were empty and which had values. This required setting the 'tag' value of each edit box. Once all these changes had been incorporated, the final code is as follows

procedure TReceiptsList.ShowBtnClick(Sender: TObject);
const
 maxflags = 6;

var
 fdt, tdt: tdatetime;
 i, tag: integer;
 flags: array [1..maxflags] of boolean;
 s: string;

begin
 if (edFromDate.text <> '' ) and 
     not TryStrToDate (edFromDate.text, fdt) then
  begin
   errflag:= true;
   EdFromDate.setfocus
  end
 else if (edTillDate.text <> '' ) and 
          not TryStrToDate (edTillDate.text, tdt) then
  begin
   errflag:= true;
   EdTillDate.setfocus
  end;

 if errflag then
  begin
   laErrText.caption:= date_errtext;
   laErrText.Color:= clWhite;
  end
 else
  begin
   for i:= 0 to componentcount - 1 do
    if components[i] is TLabeledEdit then
     begin
      tag:= TLabeledEdit (components[i]).Tag;
      flags[tag]:= TLabeledEdit (components[i]).text <> ''
     end;
   flags[6]:= rg.ItemIndex > 0;

   s:= '';
   for i:= 0 to querytext.Count - 1 do s:= s + querytext[i];
   for i:= 1 to maxflags do
    if flags[i] then
     case i of
      1: s:= s + ' and receipts.kabnum = :p1';
      2: s:= s + ' and receipts.curdate >= :p2';
      3: s:= s + ' and receipts.curdate <= :p3';
      4: s:= s + ' and receipts.price >= :p4';
      5: s:= s + ' and receipts.price <= :p5';
      6: s:= s + ' and receipts.cash = :p6';
     end;

   with qReceiptsList do
    try
     disablecontrols;
     close;
     sdsReceiptsList.commandtext:= s;
     for i:= 1 to maxflags do
      if flags[i] then
       case i of
        1: sdsReceiptsList.parambyname ('p1').asstring:= edKabnum.Text;
        2: sdsReceiptsList.parambyname ('p2').asdate:= fdt;
        3: sdsReceiptsList.parambyname ('p3').asdate:= tdt;
        4: sdsReceiptsList.parambyname ('p4').asinteger:= 
                                       strtoint (edFromRCP.text);
        5: sdsReceiptsList.parambyname ('p5').asinteger:= 
                                       strtoint (edTillRCP.text);
        6: sdsReceiptsList.parambyname ('p6').asinteger:= rg.itemindex;
       end;
     open;
    finally
     enablecontrols;
     dbgrid1.columns[prevcol].title.font.color:= clRed;
    end;
   pc.tabindex:= 1;
  end;
end;

I think that the loop which adds the conditions to the query should be merged with the loop which set the query parameters. It also occurs to me whilst editing this entry that instead of converting string values to integers (for the receipt amounts), I could pass them as strings and write parambyname ('p5').asstring:= edFromRCP.text, thus saving an unnecessary conversion. Improvements never end.

I should mention that this arrangement of preliminary parameters screen and final results screen works very well. The user can switch back and forth between the screens and issue new queries regarding receipts which return results at the speed of lightning (unless the user ignores all the parameters and requests everything!). There is at least one other screen in the program which will greatly benefit from this technique, but first I have to show it to the OP.

Blogger note: I am now using the <pre> HTML command to preserve code formatting. This saves me a great deal of time and means that I can use angle brackets without fear of them being replaced.