How to handle PocketQuery parameters with SQL "IN" clauses or table names

t is possible to use parameters (marked with a leading colon) in queries to make them more flexible and versatile. Parameter values can be set when adding the PocketQuery macro to a Confluence page.

For SQL queries, it is also possible to define the type of parameters. This changes the way the parameter value is embedded into the query. For example, strings are enclosed by quotes while integers are inserted without quotes. The default and most frequently needed parameter type is String.

If parameters are used within clauses like IN conditions it is necessary to set their type to ListOfStrings, if they are used within table names the type Constant is appropriate.

For a detailed explanation of the parameter types, please refer to the corresponding section within the PocketQuery Documentation.

Example: Contries from Continents

If you set up a query with an IN condition like this:

CODE

SELECT Name, Population, Continent
FROM Country
WHERE Continent IN (:continents)

...you will also need to set the type of the parameter "continents" as ListOfStrings:

When selecting this query within the PocketQuery macro, the user can define the parameter value as a simple, comma separated list (e.g. 'Europe, Asia'). Before executing the query, PocketQuery transforms this input into a list of strings (e.g. '"Europe","Asia"') and inserts it into the brackets of the IN condition.