Static PQL Functions
Description
Static PQL functions provide a lightweight way to check general properties of a data model or expression before actually executing a PQL query. For example, you can check the data type of a data model column and based on the type modify the actual PQL query to be executed.
The following static PQL functions are available:
ARGUMENT_COUNT counts the number of arguments passed to it.
COLUMN_TYPE returns the data type of a data model column.
IF evaluates a list of static conditions and replaces itself with the query for the first static condition that is true.
STATIC CASE WHEN evaluates a list of static conditions and replaces itself with the expression for the first static condition that is true.
Example - Preventing empty IN statements
Suppose you want to sum the total order amount for several countries (per country). The countries are selected from a drop-down menu and passed to the query as an array variable. If no country is selected, we want the total order amount for all countries. We can use the static PQL functions IF and ARGUMENT_COUNT to achieve this:
[1] Suppose the user selected the country 'DE'. | ||||||||||||||||||||
| ||||||||||||||||||||
|
[2] Suppose the user did not select any country. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
This is how the query would actually look like with an Analysis frontend variable:
IF ARGUMENT_COUNT ( <%=COUNTRIES%> ) > 0 THEN FILTER "Orders"."Country" IN ( <%=COUNTRIES%> ) ; END ;
Example - Converting an unknown type
[3] TO_STRING does not support FLOAT and STRING columns as inputs, and it requires an additional format argument for DATE inputs. With STATIC CASE WHEN and COLUMN_TYPE, we can write a string conversion query that is independent of the data type of a column. | ||||||||||||
| ||||||||||||
|