SQL - Variables

Variables can be used to make your SQL statement more readable. It also helps when you are using Prompt Fields more than once in your SQL statement, it is cleaner to only execute the Prompt Field once and then use the variable in your SQL statement later on, as many times as needed.

The SETVARIABLE should appear at the top of SQL statement, and each entry must be on a line by itself. To use the variable in your code, simply prefix the variable with a ! (Exclamation Mark)

:::important There MUST only be a single space between SETVARIABLE and the variable name. Having more than one space or a line break will cause an SQL error. :::

SETVARIABLE POINTSPERSTAY=7.5

SETVARIABLE MINIMUMNIGHTS=0

SELECT NUMBER,
       LASTNAME,
       FIRSTNAME,
      (NIGHTS * !POINTSPERSTAY) AS 'REWARD POINTS'

FROM FOLIOHD 

WHERE NIGHTS > !MINIMUMNIGHTS

SETREPORTFOOTER = '!POINTSPERSTAY POINTS EARNED '

Other options for using variables is for Prompt Fields, to make your code easier to read as well as if you want to use the same prompt field more than once in your statement.

SETVARIABLE POINTSPERSTAY=7.5

SETVARIABLE MINIMUMNIGHTS={?ENTER MINIMUM NIGHTS}

SELECT NUMBER, LASTNAME, FIRSTNAME, (NIGHTS * !POINTSPERSTAY) AS 'REWARD POINTS'

               FROM FOLIOHD WHERE NIGHTS > !MINIMUMNIGHTS

SETREPORTFOOTER = '!POINTSPERSTAY POINTS EARNED (MIN !MINIMUMNIGHTS Nights) '

An unlimited number of variables can be used, provided they are all unique. They must appear on a different line.