Integrate Software AG Products Using Digital Event Services 10.4 | Integrate Software AG Products Using Digital Event Services | MashZone NextGen Help | Appendix | Legacy Presto components | MashZone NextGen Analytics | RAQL Queries | Creating Dynamic RAQL Queries
 
Creating Dynamic RAQL Queries
At its simplest, dynamic queries use input parameters to provide the values used in Where clause conditions using dynamic mashup expressions. See Parameters in Where Clauses for an example.
But sometimes, you need more flexibility to make other clauses be dynamic. For these more demanding cases, you can build entire RAQL queries using the EMML <assign> statement, the concat() XPath function, and other EMML statements.
The following example builds the entire RAQL query based on an input parameter that chooses the time period to use for grouping stock volumes from the example dataset introduced in Getting Started.
This example uses that parameter to determine both the functions and aliases for fields to use in the Select clause as well as functions used in the Group By and Order By clauses. To do this, the mashup:
*Defines am input parameter, queryScope, with the <input> statement to get the time period of interest. In this case it can be either quarter or month.
You can also use other EMML statements to provide the dynamic values needed.
*Defines a variable, thisQuery, in a <variable> statement with a string datatype to hold the full query string.
You can also define the literal portions of the query in other string variables by assigning literal content as their default value.
*Defines string variables for the alias names to be used in the query. Both volumeAlias and periodAlias have default values that correspond with the default value for queryScope.
These variables are altered to appropriate month names in the <if> statement when the queryScope parameter is set to month.
*Combines the literal portions of the query with the dynamic portions using the <assign> statement and the concat() XPath function.
For the literal portions of the query, you can supply this directly as a string in the arguments to concat(), as this example does. Or you can refer to other variables that contain this text.
You refer to variables in the function, or in other EMML statements, using $variable-name.
*Refers to the variable with the completed query, using $query-variable-name, in the query attribute of the <raql> statement.
Depending on the value of the queryScope parameter, the actual RAQL query that this mashup uses is either:
select symbol, extract_year(datetime) as yr,
quarter(datetime) as qtr, CAST(avg(volume) AS DOUBLE) as this_qtr
from stocks
group by symbol, extract_year(datetime), quarter(datetime)
order by symbol, yr, this_qtr
or
select symbol, extract_year(datetime) as yr,
"month"(datetime) as mnth, CAST(avg(volume) AS DOUBLE) as this_month
from stocks
group by symbol, extract_year(datetime), "month"(datetime)
order by symbol, yr, mnth

Copyright © 2019 | Software AG, Darmstadt, Germany and/or Software AG USA, Inc., Reston, VA, USA, and/or its subsidiaries and/or its affiliates and/or their licensors.
Innovation Release