Using Inner Query Substitution Values
A new advanced feature has been added to the FAST Query Builder toolset.
This should only be used by advanced users, IT groups or Administrators writing complex custom SQL queries. The functionality does not exist as part of the Graphical Query Builder. If you have questions, please contact Customer Support for help with your queries.
Custom reporting pages can now be created with filter controls that are bound to variables in the inner query rather than appended to the outer WHERE clause.
For example, if you have a subquery or wrapped query that needs a variable substituted into a specific part of the query, you could use a placeholder with the # symbols, like this:
SELECT *
FROM ( SELECT col1 || col2 as col3 FROM table WHERE col4 = #SubstitutionValue#) a
WHERE a.col3 = 'X';
These #Subsitution# values can be used anywhere in the query. You can use these variables instead of hard-coded values. Since the query is processed by FAST before running in the Oracle database, we can use these variables to build column names and perform operations on them. For example: #FISCAL_YEAR#, #FISCAL_PERIOD#, #EFFDATE#, #ACTIVITYDATE#, etc. could be used both in the SELECT clause and WHERE clause at the same time, like this:
SELECT #FISCAL_YEAR# as txtFSYR, SUM(dblYTDPeriod14)
FROM FASTMCSL.VWFGREPORTTABLE a
WHERE a.txtFSYR = #FISCAL_YEAR#
GROUP BY #FISCAL_YEAR#
Additional Context
Using Substitution Values for Date vs. Number vs. Varchar Fields:
Substitution values are used literally within the query. This means your query will behave differently if the value is wrapped in quotes or not. If you want the value to be treated as a number or date, do not wrap it in quotes. Here are some examples to illustrate this:
WHERE a.txtFSYR = #FISCAL_YEAR# will become WHERE a.txtFSYR = 2017
WHERE a.txtFSYR = ‘#FISCAL_YEAR#’ will become WHERE a.txtFSYR = ‘2017’
When a query is built like this with a date field:
SELECT level_code, term_code, application_date
FROM faststu.synadmission_info
WHERE application_decision_date = #APPLICATION_DECISION_DATE#
The result when executing will become:
SELECT level_code, term_code, application_date
FROM faststu.synadmission_info
WHERE application_decision_date = TO_DATE(‘2017-03-28’, ‘YYYY-MM-DD’)
The front end will prompt the user in query builder/query viewer or page builder for a substitution for #APPLICATION_DECISION_DATE#. At this point we don't know the data type which is why the substitution dialog just shows a textbox input and not a date control or numeric box etc.
Sample Prompt in the FAST Query Builder/Query Viewer:
For date substitution values, when a user needs to test their query is written correctly they should just put "SYSDATE" in the textbox, or "TO_DATE('2017/03/28', 'YYYY/MM/DD')" if the results are important (which often they aren't since the exact purpose of the #Substitution# inner query filter is to bind it to a control on a reporting page i.e. the page builder wizard only needs to know the syntax is valid and that the #Subsitution# field exists to allow you to proceed through the wizard, it doesn't actually need to return the correct data until the report runs):
On the eventual Reporting Page, the filter control for #APPLICATION_DECISION_DATE# (with a name of Effective Date shown below) can be used like a regular date picker:
