Dynamic Query - Last table in the query with additional wher

Posted by bfraser4070 on 23-Jan-2018 05:41


I am not an ace developer so I need some help.

I am building an application that creates dynamic queries on the fly.  The application builds the queries as follows:

querystring = " for each table1 where this equals that,
                              each table2 where table2.this equals table1.that,
                              each table3 outer-join where table3.this = table2.that".

Now I add some additional filtering like (table1.thisfield = "John")

so I add querystring = quesrystring + " and " +  (table1.thisfield = "John").

Then I use that querystring in a query, with set buffers, open query etc.....

The PROBLEM is that the additional "(table1.thisfield = "John")"  becomes part of the outer-join!

If there are no outer-joins the problem does not exists, or if an outer-join is anything but the last buffer.

Anyone have any suggestions.

Thank you in advance...



All Replies

Posted by Stefan Drissen on 24-Jan-2018 00:27

Parsing and adjusting text query strings is asking for trouble.

The query string should be 'built' by a query builder in which each buffer is passed and the where parts for each buffer - store this in for example a temp-table, the extra filtering can then be passed to the query builder. Adding a filter is nothing different than adding a where clause part since the query builder has knowledge of the individual parts of the query.

Ultimately the query can return the text query string or even better handle the actual queries and buffers.

Posted by Patrick Tingen on 24-Jan-2018 01:42

It sounds as if you should not ADD your extra criteria to the query, but you should rebuild it and insert the extra condition at the right spot.

This thread is closed