- SQL Adjustments

SQL Adjustments

In some cases, adding fragments to your query allows you to remove redundant SQL from the statement.

For example:

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

[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]]

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)]]

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

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

ORDER BY A1

In this query, we can safely remove most of the checks which compare a parameter’s value to null.

The SQL uses those checks so that when the user doesn’t enter a value into a parameter, then that

parameter effectively matches all values in the database (in other words, that parameter isn’t used in

the search). However, enclosing a parameter in a fragment achieves the same thing, so the null

checks are no longer needed. This statement will act the same as the one above:

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

[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]]

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

[[AND A2 = ? ]]

[[{groupB} AND B2 = ? ]]

[[{groupB} AND B3 = ? ]]

[[{groupC} AND C2 = ? ]]

ORDER BY A1

Note that for this technique to work correctly each fragment needs to only contain a single display parameter,

since a fragment is included as long as any one of its display parameters has a value.

Extra care needs to be taken when all of a statement’s display parameters are in fragments. For example:

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

[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]]

WHERE 1 = 1

[[AND A1 = ? ]]

[[AND A2 = ? ]]

[[{groupB} AND B2 = ? ]]

[[{groupB} AND B3 = ? ]]

[[{groupC} AND C2 = ? ]]

ORDER BY A1

Here it is important to include the “1 = 1” following the WHERE. If the user chooses to leave all of the parameters

blank, then none of the fragments will be included in the SQL. In that case, the SQL will not be correct unless

there is a comparison of some sort following the WHERE.