Using Column Variables

This chapter discusses how to:

• Use a column variable in a condition.

• Change the column variable name.

Using a Column Variable in a Condition

You can name database columns with variables and use their values in conditions and commands.

When you select columns from the database in a select paragraph, you can immediately print them by using a position. For example:

begin-select
phone (,1)
   position (+1)
from customers
end-select

This example shows how to use the value of phone for another purpose, for example, in a condition:

begin-program
   do list_customers
end-program
begin-procedure list_customers
begin-select
phone
   if &phone = ''
      print 'No phone' (,1)
   else
      print &phone (,1)
   end-if
   position (+1)
from customers
end-select
end-procedure ! list_customers

The phone column is a SQR column variable. Precede column variables with an ampersand (&).

Unlike other program variables, column variables are read-only. You can use their existing value, but you cannot assign a new value to a column variable.

In the sample program, &phone is a column variable that you can use in SQR commands as if it were a string, date, or numeric variable, depending on its contents. In the condition, &phone is compared to ' ', which is an empty string. If &phone is an empty string, the program prints No phone instead.

Changing the Column Variable Name

Note that the &phone column variable illustrated in the previous section inherited its name from the phone column. This is the default, but you can change it, as the following example demonstrates:

begin-select
phone &cust_phone
   if &cust_phone = ''
      print 'No phone' (,1)
   else
      print &cust_phone (,1)
   end-if
   position (+1)
from customers
end-select

One reason for changing the name of the column variable is to use a selected column in an expression that has no name. For example:

begin-select
count(name) &cust_cnt (,1)
   if &cust_cnt < 100
      print 'Less than 100 customers'
   end-if
   position (+1)
from customers 
group by city, state
end-select

In this example, the expression COUNT (name) is selected. In the program, you store this expression in the &cust_cnt column variable and refer to it afterwards by that name.