Using SQL and Substitution Variables

SQR uses the value of a substitution variable to complete the select paragraph at compile time. Because the select paragraph is complete at compile time, SQR can check its syntax before running the program. From this point on, the value of {my_order} cannot change and the SQL statement is considered static.

In the following program, the ASK command in the SETUP section prompts the user at compile time. The value that the user enters is placed in a special kind of variable called a substitution variable. This variable can be used to substitute any command, argument, or part of a SQL statement at compile time. This example is less common, but it demonstrates the difference between compile-time and runtime substitutions:

Program ex16c.sqr
begin-setup
  ask my_order 'Enter the column name to sort by (name or city)'
end-setup
begin-program
   do list_customers_for_state
end-program
begin-procedure give_warning
   display 'Database error occurred'
   display $sql-error
end-procedure ! give_warning
begin-procedure list_customers_for_state
begin-select on-error=give_warning
name (,1)
city (,32)
   position (+1)
from customers
order by {my_order}
end-select
end-procedure ! list_customers_for_state

In this case, the ASK command prompts the user for the value of the {my_order} substitution variable, which is used to sort the output. If the argument is passed in the command line, no prompt appears. When you run this program, enter name, city, or both (in either order and separated by a comma). The program produces a report that is sorted accordingly.

You can use the ASK command only in the SETUP section. SQR processes ASK commands at compile time before running the program. Therefore, all ASK commands are run before any INPUT command.

INPUT is more flexible than ASK. You can use INPUT inside loops. You can validate the length and type of data input and reprompt if it is not valid. The sample program at the beginning of Using SQL and Substitution Variables topic contains an example of reprompting .

ASK can be more powerful. Substitution variables that are set in an ASK command enable you to modify commands that are normally quite restrictive. The following code shows this technique:

begin-setup
   ask hlines 'Number of lines for heading'
end-setup
begin-program
  print 'Hello, World!!' (1,1)
end-program
begin-heading {hlines}
   print 'Report Title' () center
end-heading

In this example, the {hlines} substitution variable defines the number of lines that the heading will occupy. The BEGIN-HEADING command normally expects a literal and does not allow a runtime variable. When a substitution variable is used with this command, its value is modified at compile time.

See Creating and Running a Sample SQR Program and SQR for PeopleSoft Implementation.