Creating SQR Select Paragraphs

The BEGIN-SELECT command is the principal method of retrieving data from the database and printing it in a report. Look again at the sample program for listing and printing data, in which the list_customers procedure starts with BEGIN-PROCEDURE and ends with END-PROCEDURE.

Note the comment following the END-PROCEDURE command. It indicates that the procedure is being ended, which is helpful when you have a program with many procedures. (You can also omit the exclamation point, for example, END-PROCEDURE main.)

The procedure itself contains a select paragraph, which starts with BEGIN-SELECT and ends with END-SELECT.

The select paragraph is unique. It combines a SQL SELECT statement with SQR processing in a seamless way. The actual SQL statement is:

SELECT NAME, CITY, STATE, PHONE
FROM CUSTOMERS

Syntax of the Select Paragraph

In an SQR select paragraph, the SQL statement SELECT is omitted, and no commas are between the column names. Instead, each column is on its own line. You can also place SQR commands between the column names, and these commands are run for every record that the select fetches.

Note: You must name each individual column in a table. The SQL SELECT * FROM statement is not allowed in SQR.

SQR distinguishes column names from SQR commands in a select paragraph by their indention. You must place column names at the beginning of a line. You must indent SQR commands at least one space. In the following example, the POSITION command is indented to prevent it from being taken as a column name. The word From must be the first word in a line. The rest of the SQR select paragraph is then written freely, after SQL syntax.

Think of the select paragraph as a loop. The SQR commands, including printing of columns, are run in a loop, once for each record that Select returns. The loop ends after the last record is returned.

Data Positioning

In a select paragraph, you see positioning after each column name. This positioning implies a PRINT command for that column. Omitting the line number in the position causes it to be set by default to the current line.

begin-select
name (,1)
city (,32)
state (,49)
phone (,55)
   position (+1)  ! Advance to the next line
from customers
end-select

The implied PRINT command is a special SQR feature that is designed to save you coding time. It works only inside a select paragraph.

After the last column is a POSITION command: POSITION(+1). The plus sign (or minus sign) indicates relative positioning in SQR. A plus sign moves the print position forward from the current position, and a minus sign moves it back. The +1 in the sample program specifies one line down from the current line. This command advances the current print position to the next line.

Note: When you indicate print positions by using plus or minus signs, be sure that your numbers do not specify a position outside of the page boundaries.