Obtaining Totals

The ex5d.sqr program also prints a subtotal of customers in each state and a grand total of all customers. These calculations are performed with two numeric variables, one for the subtotal and one for the grand total. These variables are:

• #state_total

• #cust_total

SQR for PeopleSoft has a small set of variable types. The most common types are numeric variables and string variables. All numeric variables in SQR are preceded by a pound sign (#), and all string variables are preceded by a dollar sign ($). An additional SQR variable type is the date variable.

In SQR for PeopleSoft, numeric and string variables are not explicitly declared. Instead, they are implicitly defined by their first use. All numeric variables start out as zero and all string variables start out as null, so they do not need to be initialized. The string variables are of varying length and can hold long and short strings of characters. Assigning a new value to a string variable automatically adjusts its length.

In the list_customers procedure, #state_total and #cust_total are set to zero at the beginning of the procedure. This initialization is optional and is done for clarity only. The #state_total variable increments by 1 for every row that is selected.

When the value of state changes, the program calls the state_tot procedure and prints the value of #state_total. Note the use of the EDIT 999,999 edit mask, which formats the number.

This procedure also employs the LET command. LET is the assignment command in SQR for building complex expressions. Here, LET adds the value of #state_total to #cust_total. At the end of the procedure, #state_total is reset to zero.

The list_customers procedure contains an example of the SQR if-then-else logic. The condition starts with IF followed by an expression. If the expression evaluates to true or to a number other than zero, the subsequent commands are run. Otherwise, if the IF command has an ELSE command, then those commands are run. IF commands always end with an END-IF command.

In ex5d.sqr, the value of #cust_total is examined. If it is greater than zero, the query has returned rows of data, and the program prints the string Total Customers: and the value of #cust_total.

If #cust_total is zero, the query has not returned any data. In that case, the program prints the string No customers.