UNIT 5

Embedded Variables

A powerful feature of SQL/PRO is the ability to use variables within your SQL statements. What this means is that you can let the system either prompt you for a value or pass a value through a parameter and its value will be substituted into your SQL statement at execution time.

For example, you want your users to be able to create a list of customers for any state they choose. Instead of creating 50 different saved queries (one for each state) you can simply create one query that uses a variable in place of the state value. A variable is defined by starting a string with an '&'character. The following SQL statement would be all you would need to create the query for the above example:

SELECT * FROM CUSTMAST
     WHERE CMSTE = &STATE

A variable can be placed anywhere within an SQL statement (in place of a column name, a file name, an operator or a compare value just to mention a few), but no more than 50 can be used. The user gains the added benefit of being able to run the query in batch.

At execution time the system will prompt the user for a value for variable STATE. Since CMSTE is a character data type it is required that the value be submitted within apostrophes.

If an SQL statement containing variables is saved, values can be passed to it through the STRQMAQRY command. This way the system doesn't need to prompt you for the value/s at execution time. To illustrate, here is the STRQMQRY command necessary to run the above statement using AZ as the state:

STRQMQRY QMQRY (LSTCSTMST)
                      OUTPUT (*PRINT)
                      SETVAR ((STATE ''''AZ''''))

Since the command processor removes the outer apostrophes and converts embedded double apostrophes to single the SQL statement resolves to look like this:

SELECT * FROM CUSTMAST
       WHERE CMSTE = 'AZ'

A CL variable could be used in place of the character constant. The format for the CHGVAR command could look like this:

DCL VAR("E) TYPE(*CHAR) LEN(2) VALUE('''')
DCL VAR(&STATE) TYPE(*CHAR) LEN(4)

CHGVAR VAR (&STATE) VALUE ("E |< 'AZ' |< "E)
NOTE: "|<" is a concatenation symbol

The CHGVAR command (as well as any command that accepts character strings) strips the outermost single apostrophes and converts each inner set of two apostrophes to one. Therefore, the &STATE variable ends up with "AZ" as we desire.

The STRQMQRY command could now be coded as:

STRQMQRY QMQRY (LSTCSTMST)
                      OUTPUT (*PRINT)
                      SETVAR ((STATE &STATE))

This method also allows you to replace the character constant with a CL variable so that different values can be entered at run time from a display file or another program.

Passing numeric values is much easier since apostrophes aren't required. To pass a variable to the following SQL statement:

SELECT * FROM CUSTOMAST
       WHERE CMPDUE = &PDCTR

This segment of CL code could be used:

CHGVAR VAR (&PASTDUECTR) VALUE (6)

STRQMQRY QMQRY (LSTCSTMST)
                      OUTPUT (*PRINT)
                      SETVAR ((PDCTR &PASTDUECTR))