Embedded Files
Table Parameters
?~<table_name>
A table parameter is used when you want to allow the user to specify the table or tables to run an SQL statement
against. For example, you might have multiple tables containing customer orders - every table would have the same
structure but be specific to a single customer. You could then build a query which could apply to any of the
tables - the user will pick the exact table to run the query against at run-time:
SELECT * FROM ?~PLANETJ.CUSTOMER WHERE ORDER_NUMBER = ?
A table parameter begins with a question mark followed a tilde (~) and includes the name of a table; in the above
statement ?~PLANETJ.CUSTOMER is the table parameter. At run-time, the statement will be executed against
whatever table the user specifies, which may or may not be the PLANETJ.CUSTOMER table.
However, the PLANETJ.CUSTOMER table will be used to identify the field descriptors which will be used to display
the parameter prompts to the user. The prompt for the table parameter will be based off of the table descriptor for
PLANETJ.CUSTOMER - this table descriptor can be used to specify a display name and a list of possible table values
for the user to choose from.
The prompt for the second parameter will be based off of the ORDER_NUMBER field
descriptor in the PLANETJ.CUSTOMER table - even if this is not the table the user specifies for the actual statement
execution.
Parameter Parameters
??<parameter number>
A Parameter parameter is a parameter which gets its value from another parameter in the same statement.
Parameter parameters are used when multiple parameters in a statement must all have the same value. For
example if you wanted to look up customer balances that are within $200 of a certain value, your query
might look like this:
SELECT * FROM PLANETJ.CUSTOMER WHERE BALANCE +200 > ? AND BALANCE – 200 < ??1
The first question mark is a normal SQL prompt parameter - the user will be prompted for this value. The second
pair of question marks is immediately followed by a number, indicating that it is a Parameter parameter. The user
will not be prompted to supply a value for this parameter. Instead it will have the exact same value as the first
parameter in the statement.
In general, a Parameter parameter is denoted by two question marks followed by a number. The number indicates
which parameter in the statement should be used to supply the value (in the above example, the number 1
indicates that the first parameter should be used to supply the value).
Context Parameter Parameters
??&<parameter number>
A Context Parameter parameter is a parameter that is similar to a Parameter parameter, but rather than
getting its value from another parameter in the same statement, it gets it’s value from a parameter (search)
in an associated statement. When Context Parameter parameters are used, parameters in an association
need to have the same value as the parameters in the original SQL.
For example, an original query might show a summary of a customer’s balance between a certain date range.
The query would also contain an association (using a derived field descriptor) that gets transaction details for
that customer. The association (2nd SQL listed below) would thus need to use the same search date range:
SELECT CUSTOMER_NAME, SUM(AMOUNT), !!DETAILS FROM PLANETJ.CUSTOMER_TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN ? AND ?
SELECT TRANSACTION_ID, AMOUNT FROM PLANETJ.CUSTOMER_TRANSACTIONS
WHERE CUSTOMER_NAME = ??CUSTOMER_NAME AND TRANSACTION_DATE BETWEEN ??&1 AND ??&2
In the association, the first parameter is a Row parameter used to ensure the proper customer information is
retrieved. The last two parameters are the context parameter parameters used to get the same date range
to search on as the original query.
In general, a Context Parameter parameter is denoted by two question marks followed by an ampersand ('&')
and a number. The number indicates which parameter in the original statement should be used to supply the value.
Page updated
Report abuse