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
