Dynamic Queries in Progress ABL/4GL Based On Inputs

Often I run into queries where the “where” clause is put together by what has been entered by the user. These tend to be a complete mess in code by programmers not sure how to put this together.

One basically needs a flag to know what is being connected together and what is not.

Here is a “design pattern” of sorts one can use for these kinds of queries.

• Make up your basic query phrase, in this case “for each cust” where cust is the table name.

 QueryPhrase = "for each cust".

• Basically take in the values that could be present, in this case CustomerNumber and CustomerName.

 CustomerNumber = get-value("ByCustomerNumber").

 CustomerName = get-value("ByCustomerName").

• If any of the “where” clause information is present, append “ where “ to the query phrase. You know there is going to be a delimiter on the query by the mere existence of the values.

 if CustomerNumber <> "" or CustomerName <> "" then QueryPhrase = QueryPhrase + " where ".

• Next build your where clause. If the data is present, then append the where clause on to the query phrase and include that an and is need by setting a logical flag to yes. We use a logical flag because we don’t know yet if any of the other data is present, but we want to “remember” an “and” is needed when other data shows it’s self is there.

In the code below, I begin using the design on the construction of the QueryPhrase:

 if CustomerNumber <> "" then do:

   QueryPhrase = QueryPhrase + (if AndNeeded then " and " else "")

                 + ”cust.cust_num begins '" + CustomerNumber + "'".

   AndNeeded = true.

 end. // if CustomerNumber <> ""

That construct can be used for whatever fields are being used to restrict the data being returned.

Here it is in actual usable code (using Webspeed on the WDS application):

procedure DoLookup:

 define variable HdlTheQuery as widget-handle no-undo.

 define variable QueryPhrase as character no-undo.

 define variable CustomerNumber as character no-undo.

 define variable CustomerName as character no-undo.

 define variable FieldName as character no-undo.

 define variable AndNeeded as logical init false no-undo.

 define variable Counter as integer no-undo.

 define variable MaxCounter as integer init 100 no-undo.

 define variable H as lib.HTMLTools no-undo.

 H = new lib.HTMLTools().

 QueryPhrase = "for each cust".

 CustomerNumber = get-value("ByCustomerNumber").

 CustomerName = get-value("ByCustomerName").

 FieldName = get-value("FieldName").

 // Determine if we are going to have any where clauses

 if CustomerNumber <> "" or CustomerName <> "" then QueryPhrase = QueryPhrase + " where ".

 // Try clause one

 if CustomerNumber <> "" then do:

   QueryPhrase = QueryPhrase + (if AndNeeded then " and " else "")

                 + ”cust.cust_num begins '" + CustomerNumber + "'".

   AndNeeded = true.

 end. // if CustomerNumber <> ""

 // Try clause two

 if CustomerName <> "" then do:

   QueryPhrase = QueryPhrase + (if AndNeeded then " and " else "")

                 + "index(cust.name, '" + CustomerName + "') > 0".

   AndNeeded = true.

 end. // if CustomerName <> ""

 create query HdlTheQuery.

 HdlTheQuery:set-buffers (buffer cust:handle).

 HdlTheQuery:query-prepare (QueryPhrase).

 HdlTheQuery:query-open ().

 -->

 <table>

   <tr>

     <td>Customer Number</td><td>Customer Name</td>

   </tr> 

 <!--WSS

 repeat:

   HdlTheQuery:get-next().

   if HdlTheQuery:query-off-end then leave.

   Counter = Counter + 1.

   if Counter = MaxCounter then leave.

   -->

   <tr>

     <td>`H:AnswerHyperLink(FieldName, cust.cust_num, cust.cust_num)`</td>

     <td>`substitute("&1 &2", cust.name, cust.name2)`</td>

   </tr>

   <!--WSS

 end. // repeat

 -->

 </table>

 <!--WSS

 HdlTheQuery:query-close ().

 delete object HdlTheQuery.

end.

This is just a quick hint for those who may need to build query clauses on the fly.