- Table, Parameter, and Context

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.