SQR Data Elements

Each SQR data element begins with a special character that denotes the type of data element.

This section discusses:

  • Columns

  • Variables

  • Literals

Columns are fields that are defined in the database.

The ampersand character (&) begins a database column or expression name. It can be any type of column, such as character, number, or date. Columns that are defined in a query are declared automatically, except for dynamic columns and database or aggregate functions.

Variables are storage places for text or numbers that you define and manipulate. Variables begin with special characters:

  • $ begins a text or date variable.

  • # begins a numeric variable.

  • % begins a list variable.

  • @ begins a variable name for a marker location.

    Marker locations identify positions to begin printing in a BEGIN-DOCUMENT paragraph.

Variable Rules

The following rules govern the use of variables in SQR:

  • Variables can be almost any name of almost any length—for example, $state_name or #total_cost.

  • Do not use an underscore (_) or colon (:) as the first character of a two-variable name.

    See Using Hyphens and Underscores.

  • Variable names are not case-sensitive.

    That is, you can use a name in uppercase on one line and lowercase on the next; both refer to the same variable.

  • SQR for PeopleSoft initializes variables to null (text and date) or zero (numeric).

  • A command can grow to whatever length the memory of your computer can accommodate.

  • Numeric variables can be one of three types: float, integer, or decimal.

    See DECLARE-VARIABLE.

  • Variables and columns are known globally throughout a report, except when used in a local procedure (one with arguments or declared with the LOCAL argument), in which case they are known in that procedure only.

    See BEGIN-PROCEDURE.

SQR Reserved Variables

When you create multiple reports, the variables apply to the current report. SQR for PeopleSoft reserves a library of predefined variables for general use.

The following table describes the SQR-reserved variables:

Variable

Description

#current-column

Current column on the page.

$current-date

Current date and time on the local machine when SQR for PeopleSoft starts running the process.

#current-line

Current line on the page. This value is the physical line on the page, not the line in the report body.

Line numbers are referenced in PRINT and other SQR commands that are used for positioning the data on the page. Optional page headers and footings, which are defined with BEGIN-HEADING and BEGIN-FOOTING commands, have their own line sequences. Line 2 of the heading is different from line 2 of the report body or footing.

#end-file

See READ.

#page-count

Current page number.

#return-status

Value to return to the operating system when SQR for PeopleSoft exits. This can be set in the report. #return-status is initialized to the success return value for the operating system.

#sql-count

Count of the rows that are affected by a SELECT paragraph (INSERT, UPDATE, or DELETE). This is equivalent to ROWCOUNT in Oracle.

$sql-error

Text message from the database explaining an error. This variable is rewritten when a new error is encountered.

#sql-status

The value of #sql-status is set whenever a BEGIN-SELECT command is run. Normally this variable is checked from inside an ON-ERROR procedure, so its value describes the error condition (whereas the $sql-error variable contains the error message). The actual meaning of #sql-status is database-dependent. Therefore, consult the proper database manual to fully interpret its meaning.

$sqr-encoding-console

{sqr-encoding-console}

Name of encoding for character data that is written to the log file or console.

$sqr-encoding-database

{sqr-encoding-database}

Character data that is retrieved from and inserted into the database.

$sqr-encoding-file-input

{sqr-encoding-file-input}

Name of encoding for character data that is read from files that are used with the OPEN command.

$sqr-encoding-file-output

{sqr-encoding-file-output}

Name of encoding for character data that is written to files that are used with the OPEN command.

$sqr-encoding-report-output

{sqr-encoding-report-output}

Report that is generated by SQR for PeopleSoft (for example, a .lis file or a PostScript file).

$sqr-encoding-source

{sqr-encoding-source}

Name of encoding for SQR source files and include files.

$sqr-database

{sqr-database}

Database type for which SQR was compiled. Values are ORACLE, DB2 and ODBC.

$sqr-dbcs

{sqr-dbcs}

Specifies whether SQR for PeopleSoft recognizes double-byte character strings. Values are YES and NO.

$sqr-encoding

{sqr-encoding}

Name of the default encoding as defined by the ENCODING environment variable when SQR for PeopleSoft is invoked.

$sqr-hostname

{sqr-hostname}

Name of the computer on which SQR for PeopleSoft is currently running.

$sqr-locale

Name of the current locale that is being used. A plus symbol (+) at the end of the name indicates that an argument that is used in the locale has changed.

#sqr-max-lines

Maximum number of lines, as determined by the layout. When a new report is selected, this variable is automatically updated to reflect the new layout.

#sqr-max-columns

Maximum number of columns, as determined by the layout. When a new report is selected, this variable is automatically updated to reflect the new layout.

#sqr-pid

Process ID of the current SQR process. #sqr-pid is unique for each run of SQR. This variable is useful for creating unique, temporary names.

$sqr-platform,

{sqr-platform}

The hardware or operating system type for which SQR was compiled. Values are MVS, Windows, and UNIX/Linux.

$sqr-program

Name of the SQR process file.

$sqr-ver

Text string that is shown with the -ID flag, SQR version.

$username

Database username that is specified on the command line.

$sqr-report

Name of the report output file . $sqr-report reflects the actual name of the file to use, as specified by the -F flag or NEW-REPORT command.

List Variables

List variables contain an ordered collection of SQR variables and are nonrecursive, that is, you cannot nest lists inside lists.

Indicate list variables with the percent symbol (%). Create list variables with the LET command and a list of variables. For example: 

let %list1 = list (num_var1|str_var1, num_var2|str_var2,...)

Working with list variables includes the following tasks:

  • Defining a list variable.

    You can use a list variable to hold multiple rows of information. Before you assign a list variable, define it by using the following syntax:

    let %listname=list(col_var|num_var|str_var|str_lit|num_lit[,...])

    or

    let %listname[num_lit]=list(NUMBER|DATE|TEXT$colname
|'.colname'[,...])

  • Assigning a list variable.

    Assign a list variable by using the following syntax:

    let %listname|%listname[num_var|num_lit]=list(col_var|str_var
|num_var|str_lit|num_lit[,...])

  • Accessing a list variable.

    Access a list variable by using the following syntax:

    let str_var|num_var=%listname[num_var|num_lit].#colname

List Variable Arguments

The value between the brackets indicates either the number of rows in the list for the definition case or the row in the list to modify or assign.

If no brackets are present, you do not need to predefine; assign the types based on the given variable types. For multi-row lists, the assignment must be compatible with the types that are given in the definition.

A NUMBER field has the same characteristics as an undeclared #var. The underlying storage depends on the contents, and the DEFAULT-NUMERIC setting applies.

The usual SQR rules for variable assignment apply to list access. Assignment is prohibited only between date and numeric types. Assignment of a numeric column to a string variable returns the string representation of the numeric value; assignment of a date variable to a string variable returns the default-edit-mask representation of the date.

Literals are text or numeric constants:

  • A single quote begins and ends a text literal . For example:

    'Hello'

  • Numerals 0−9 begin numeric literals.

Numerals that include digits with an optional decimal point and leading sign are acceptable numeric literals, for example, -543.21. Numeric literals can also be expressed in scientific form, for example, 1.2E5.