Using Column Variables

This chapter discusses how to:

• Use column variables in conditions.

• Change column variable names.

Using Column Variables in Conditions

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 content. In the example condition, &phone is compared to ' ', which is an empty string. If &phone is an empty string, then the program prints No phone.

Changing Column Variable Names

Note that the &phone column variable illustrated in the previous section inherited its name from the phone column. This value is the default, but you can change it, as this 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.