- Groups with multiple parameter fragments

Groups with multiple parameter fragments

In all of the above examples, the fragment groups have all contained two fragments. In the next example, the fragment group contains three fragments, two of which have parameters and one with no parameters:

SELECT A1, A2, A3, A4 FROM LIB.A [[{group1} INNER JOIN LIB.B ON A1 = B1 ]]

WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND

(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{group1} AND

(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND

(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]] AND

(A9 = ? OR CAST(??9 AS INTEGER) IS NULL) [[{group1} AND

B4 = ? ]]

ORDER BY A1

In order for the first fragment (the JOIN) to be included, one of the display parameters in either of the other two fragments must contain a value. So if B2 or B3 or B4 contains a value, the first fragment will be included. Only if none of those parameters have a value is the first fragment removed. The second and third fragments are removed as usual, when none of the display parameters in that particular fragment has a value.

To summarize, when a group contains multiple fragments with display parameters, if any of those fragments have values in their display parameters, then the group’s fragments without display parameters will be included.

Note that in the above example, WOW will properly adjust the parameter numbering when fragments are removed. So if the second fragment is removed, then the ??9 parameter would be changed to ??5 since there are four fewer parameters preceding it in the SQL.

Inner fragments

In some cases such as the following example, you may need to include fragments within other fragments:

SELECT A1, A2, A3, A4 FROM LIB.A [[{groupB} INNER JOIN LIB.B ON A1 = B1 ]]

[[{groupC} INNER JOIN LIB.C ON A1 = C1 [[{groupB} AND C6 = B6 ]] ]]

WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND

(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{groupB} AND

(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND

(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]] [[{groupC} AND

C2 = ?]]

ORDER BY A1

The fragment in purple is only included when groupB and groupC are both included. That fragment describes the join between tables B and C, and so it only should be included in the SQL if both of those tables are in the SQL. By directly assigning the purple fragment to groupB and then embedding it within a fragment from groupC, the purple fragment becomes part of both groups.

Fragments with multiple groups

A fragment can belong to multiple groups. In the above example this was done by embedded one fragment within another. An alternative way to assign multiple groups to a fragment is by listing out the various groups it belongs to:

SELECT A1, A2, A3, A4 FROM LIB.A [[{groupB} INNER JOIN LIB.B ON A1 = B1 ]]

[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]] [[{groupB,groupC} AND C6 = B6 ]]

WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND

(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{groupB} AND

(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND

(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]] [[{groupC} AND

(C2 = ? OR CAST(??9 AS CHAR(10)) IS NULL) ]]

ORDER BY A1

Here, the third fragment belongs to both groupB and groupC. This example will behave exactly like the previous one; the third fragment is only included when both groupB and groupC contain display parameters with values. (For example, if parameters B3 and C2 were given values by the user then the purple fragment would be included, but if B3 were the only parameter with a value then the fragment would be removed.) To summarize, when a fragment without display parameters belongs to multiple groups, then that fragment is only included when all of those groups are included.

Consider the following example, where the first fragment contains the A2 column:

SELECT A1, [[{groupA} A2,]] A3, A4 FROM LIB.A [[{groupB} INNER JOIN LIB.B ON A1 = B1 ]]

[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]] [[{groupB,groupC} AND C6 = B6 ]]

WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND

(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{groupA,groupB} AND

(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND

(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]] [[{groupA,groupC} AND

C2 = ? ]]

ORDER BY A1

In this example, that fragment will be included when one or more of the B2, B3, or C2 parameters is given a value by the user. Unlike the purple fragment which requires parameters from all of its groups to have values before it is included, the A2 fragment is included when any one (or more) of the B2, B3, or C2 parameters has a value. This is because it only belongs to a single group whereas the purple fragment belongs to multiple groups.