3 Types of SQR Variables in SQL Statements

SQR programs can execute SQL “select” statements with a “begin-select … end-select” block. They can execute any other SQL statements with a “begin-sql … end-sql” block. SQR has three ways to modify the literal text of those SQL statements.

SQR Variables As Bind Variables

The most common use of variables is to use runtime variables in place of literal values. Here is an example:

begin-program
let #number_of_people = 589141
let $name_of_city = ‘Boston’

begin-select
A.STATE
 from STATE_CAPITALS A
where A.CITY = $name_of_city
end-select

begin-sql
  update POPULATION
  set HEADCOUNT = #number_of_people
  where CITY = $name_of_city
  and STATE = &A.STATE
end-sql
end-program

When the SQR compiler processes the update SQL statement, it will replace #number_of_people, $name_of_city, and &A.STATE with SQL bind variables :1, :2, and :3. Then, when the SQR runtime processor executes this SQL statement, it will send the SQL statement to the SQR server and send the current values of those three variables to be inserted in place of the bind variables.

SQR handles date variables with advanced, telepathic, “do what I mean” functionality. When a date variable appears in an SQL statement, its value is sent to the database in the database’s format. When SQR outputs it to the “DOS” window or the log file (with display or show), it’s formatted according to the date-edit-mask in the sqr.ini file or the alter-locale command. SQR stores it in a YYYY-MM-DD format that allows comparisons in evaluate, if, and while commands.

Substitution Variables

Another use of variables is to use preprocessor or compile-time variables in place of literal values. Here is an example:

#define a-number 7
#define tablename CUSTOMERS
#define and-clause and EFFDT = '01-JAN-2009'

begin-setup
  ask a-string 'Enter customer ID '
end-setup

begin-program
begin-sql
  update {tablename}
  set PRIORITY = {a-number}
  where CUSTOMER_ID = {a-string}
  {and-clause}
end-sql
end-program

Preprocessor and compile-time variables are more flexible than run-time variables because they can replace part of a command, not just a literal value. They are less flexible than run-time variables because they can only be set once, at the beginning of a program. Here is the update SQL statement formed by the compiler, assuming the user entered 001234

update CUSTOMERS
set PRIORITY = 7
where CUSTOMER_ID = '001234'
and EFFDT = '01-JAN-2009'

Dynamic Variables

Dynamic variables combine the benefits of run-time variables and compile-time substitution variables. They can contain values developed during program execution and they can represent any part of the SQL statement. A dynamic variable should be a string variable (I can’t imagine how a number or date would work) and it should be enclosed in square brackets.

Here’s an example of four reports in one. The user can specify active employees or employees on leave and sorting by name or by department and ID number.

if #status_choice = 1
  let $empl_status = '''A'''
else
  let $empl_status = '''L'',''P'''
end-if
if #sort_choice = 1
  let $sort = 'NAME'
else
  let $sort = 'DEPTID, EMPLID'
end-if

begin-select
EMPLID (+1, 1)
DEPTID ( ,15)
NAME ( ,30)
 from PS_EMPLOYEES
where EMPL_STATUS in ([$empl_status])
order by [$sort]
end-select

Note that we can avoid a dynamic variable for the case of active employees. We could write:

let $empl_status = 'A'

where EMPL_STATUS = $empl_status

Or
where EMPL_STATUS in ($empl_status)

But that doesn’t work when we want to select two values for EMPL_STATUS.

Maybe we want to work with the DEPTID or maybe we want to work with the JOBCODE. We can put a column name in a dynamic variable. SQR requires that we specify a column variable (&DJ_FIELD) to hold the value of the field, and indicate whether the contents will be a char, number, or date.

if #field_choice = 1
  let $dept_or_job = 'DEPTID'
else
  let $dept_or_job = 'JOBCODE'
end-if

begin-select
EMPLID
[$dept_or_job] &DJ_FIELD=char

from PS_EMPLOYEES
end-select

Taking this example another step, suppose we’re working with DEPTID or JOBCODE and we want the description of whichever code we choose. Put aside the fact that those fields are already in PS_EMPLOYEES; this is a learning exercise. We want to join PS_EMPLOYEES either to PS_DEPT_TBL or to PS_JOBCODE_TBL.

In order to assist SQR in interpreting this statement, we offer a clue. We enter a table as “[PS_DEPT_TBL : $prompt_table].” This says, for the purpose of this SQL statement, refer to PS_DEPT_TBL for the properties of the columns of the table named by $prompt_table. The select statement refers to SETID, EFFDT, DESCR, and a column to be named later (by $dept_or_job). The first three columns appear in PS_DEPT_TBL and PS_JOBCODE_TBL.

if #field_choice = 1
  let $dept_or_job = 'DEPTID'
  let $setid_field = 'SETID_DEPT'
  let $prompt_table = 'PS_DEPT_TBL'
else
  let $dept_or_job = 'JOBCODE'
  let $setid_field = 'SETID_JOBCODE'
  let $prompt_table = 'PS_JOBCODE_TBL'
end-if
let $e_dept_or_job = 'E.' || $dept_or_job

begin-select
E.EMPLID
[$e_dept_or_job] &DJ_FIELD=char
X.DESCR

 from PS_EMPLOYEES E, [PS_DEPT_TBL : $prompt_table] X
where X.SETID = E.[$setid_field]
and X.[$dept_or_job] = E.[$dept_or_job]
and X.EFFDT = (select max(EFFDT)
              from [PS_DEPT_TBL : $prompt_table]
              where SETID = X.SETID
              and [$dept_or_job] = X.[$dept_or_job]
              and EFFDT <= SYSDATE)
end-select

Note that we were able to concatenate an alias and a dynamic variable in the where clause (”where X.SETID = E.[$setid_field]) but not as a column name. We had to create a new variable ($e_dept_or_job). I discovered this limitation while preparing this example. It is challenging for SQR to interpret an SQL statement that can have dynamic variables contain any portion of the text. Sometimes it takes several tries to get it to work.

Looking Ahead

In my February 1, 2009 post, SQR Evaluate Versus C/Java Switch, Part J*, I mentioned a technique for encapsulating the process of receiving run control parameters. I think the world has waited long enough. Next week, we’ll discuss how to read a run control table without knowing its name.

3 Comments

  1. Bob Josephson says:

    What, no brain teaser?

    • administrator says:

      My brain couldn’t think of one this week. Don’t worry, there are still several brain teasers on previous posts that nobody has answered.

  2. John says:

    Thanks a ton for this post. For whatever reason the documentation for SQR did not turn this up but your blog post bubbled to the top in Google.