Enterprise PeopleTools 8.51 PeopleBook: SQR Language Reference for PeopleSoft

Understanding SQR for PeopleSoft

This chapter discusses:

SQR for PeopleSoft tools.
The Structured Query Report (SQR) language.
SQR command line.
SQR data elements.
Sample reports.

SQR for PeopleSoft Tools

SQR for PeopleSoft is a powerful enterprise reporting system that provides direct access to multiple data sources. The SQR for PeopleSoft tools enable you to create clear, professional reports from complex arrays of information systems.

This reference describes the following SQR for PeopleSoft tools:

The SQR language, which is a flexible, fourth-generation reporting language with a lexicon of more than 110 commands.

The procedural design of SQR enables you to easily develop, implement, and distribute complex reports.
SQR Execute, which enables you to run previously compiled SQR programs.
SQR Print, which enables you to configure reports for most printers.
SQR Samples, a library of SQR programs and output that provides a framework for creating configured reports.

The SQR Language

This section provides an overview of the SQR language and discusses:

SQR program structure.
SQR syntax conventions.
Rules for entering SQR commands.

Understanding the SQR Language

SQR is a specialized programming language for accessing, manipulating, and reporting enterprise data. With SQR, you build complex procedures that perform multiple calls to multiple data sources and implement nested, hierarchical, or object-oriented program logic.

SQR provides several important benefits:

Flexibility and scalability.
Comprehensive facilities for combined report and data processing.
Multiple platform availability.
Multiple data source compatibility.

With SQR, you design reports by defining the page size, headers, footers, and layout . SQR enables you to generate a variety of output types , such as complex tabular reports, multiple page reports, and form letters. You can display data in columns; produce special formats, such as mailing labels; and create HTML, PDF, or configured output for laser printers and phototypesetters.

The high-level programming capabilities of SQR enable you to add procedural logic and control to data source calls. You can use SQR to write other types of applications, such as those for database manipulation and maintenance, table loading and unloading, and interactive querying and displaying.

SQR Program Structure

SQR for PeopleSoft processes source code from a standard text file and generates a report. The text file containing source code comprises a set of sections that you delimit with BEGIN-section and END-section commands. The following examples show the general structure of SQR:

The SETUP section describes overall characteristics of the report.
```
BEGIN-SETUP
 {setup commands}...
END-SETUP
```

The HEADING and FOOTING sections specify what information is printed in the header and footer on each page of the report.

BEGIN-HEADING {heading_lines}
 {heading commands}...
END-HEADING
BEGIN-FOOTING {footing_lines}
 {footing commands}...
END-FOOTING

The PROGRAM section runs the procedures in the report.
```
BEGIN-PROGRAM
 {commands}...
END-PROGRAM
```

The PROCEDURE section performs the tasks to produce the report.

BEGIN-PROCEDURE {procedure_name}
 {procedure commands}...
END-PROCEDURE

SQR Syntax Conventions

The following table describes the SQR syntax conventions:

Syntax Convention	Description
{ }	Braces enclose required items.
[ ]	Square brackets enclose optional items.
...	Ellipses indicate that the preceding parameter can be repeated.
\|	A vertical bar separates alternatives inside brackets, braces, or parentheses.
!	An exclamation point begins a single-line comment that extends to the end of the line. Each comment line must begin with an exclamation point.
'	A single quote starts and ends a literal text constant or any argument with more than one word. Important! If you are copying code directly from the sample programs, change the slanted quotes to regular quotes; otherwise, you will receive an error message.
,	A comma separates multiple arguments.
( )	Parentheses must enclose an argument or element.
UPPERCASE	SQR commands and arguments are uppercase within the text, but lowercase in the sample programs. (Note that these commands are not case-sensitive.)
Variable	Information and values that you must supply appear in variable style.
hyphen versus underscore	Many SQR commands, such as BEGIN-PROGRAM, contain a hyphen, whereas procedure and variable names contain an underscore. Procedure and variable names can contain either hyphens or underscores, but you should use underscores in procedure and variable names to distinguish them from SQR commands. This practice also prevents confusion when you mix variable names and numbers in an expression in which hyphens could be mistaken for minus signs.

Rules for Entering SQR Commands

Use these command rules as you develop SQR programs:

You can enter SQR commands in either uppercase or lowercase; they are not case-sensitive.

Many SQR programmers use uppercase for SQR commands, but SQR ignores case because it compiles source code.
You must separate command names and arguments by at least one space or tab character.
You must begin each command on a new line; however, you can develop commands that extend beyond one line.
You can break a line in any position between words except inside a quoted string.
You can use a hyphen (-) at the end of a line to indicate that it continues on the next line; however, SQR ignores hyphens and carriage returns in commands.
You must begin each comment line with an exclamation point (!).

Note. To display the exclamation point (!) or single quote (') symbols in a report, type the symbol twice to indicate that it is text. For example, DON'T is typed DON''T. This rule does not apply in the document paragraph of form-letter reports.

SQR Command Line

SQR for PeopleSoft comprises SQR, SQR Execute, and SQR Print . Each has a command-line interface.

To begin running SQR, enter the following command:

SQR [program][connectivity][flags...][args...][@file...]

Note. The executable name for SQR is SQR (SQRW for Microsoft Windows). The executable name for SQR Execute is SQRT (SQRWT for Microsoft Windows). The executable for SQR Print is SQRP (SQRWP for Microsoft Windows).

See Invoking SQR Execute, Using SQR Print.

SQR Command-Line Arguments

The following table describes the SQR command-line arguments:

Argument	Description
program	Name of the text file containing the source code. The default file type or extension is .sqr. If you enter this value as a question mark (?) or omit it, SQR for PeopleSoft prompts you for the report program name. On UNIX/Linux-based systems, if your shell uses the question mark as a wildcard character, you must precede it with a backslash (\).
connectivity	Information that SQR for PeopleSoft needs to connect to the database. If you enter this value as a question mark (?) or omit it, SQR for PeopleSoft prompts you for the information. IBM DB2: Subsystem name and Structured Query Language (SQL) authorization ID. Ssname/SQLid Microsoft Windows or IBM DB2: he name of the database, your username, and the password for the database. [Database] / [Username] / [Password] Informix: Name of the database. Database[/username/password] Open Database Connectivity (ODBC): The name that you give to the ODBC driver when you set up the driver, and your username and password for the database. This port has been certified against IBM DB2 and Microsoft SQL Server. Data_Source_Name/[Username]/[Password⇒ ] Oracle: Your username and password for the database, and an optional connection string for the database (for example, @sales.2cme.com). [Username]/[Password[@Database]] Sybase: Your username and password for the database. Username/[Password]
flags	See SQR Command-Line Flags.
args...	Arguments that SQR for PeopleSoft uses while the program is running. The ASK and INPUT commands use these arguments rather than prompting the user. You must enter arguments on the command line in the sequence that the program expects—first all ASK arguments, in order, followed by INPUT arguments.
@file...	File containing program arguments—one argument per line. Arguments that are listed in the file are processed one at a time: first all ASK arguments, in order, followed by INPUT arguments. You can specify the command-line arguments (program, connectivity, and args) in this file for non-Microsoft Windows platforms.

SQR Command-Line Flags

SQR supports a number of command-line flags. Each flag begins with a hyphen (-). When a flag takes an argument, the argument must follow the flag with no intervening space.

The following table describes the SQR command-line flags:

Flag	Description
-A	Appends the output to an existing output file carrying the same name as the source of the output. If the file does not exist, a new one is created. This flag is useful when you want to run the same report more than once but want only a single output file.
-Bnn	(Oracle, ODBC, and Sybase CT-Lib) Indicates the number of rows to buffer each time SQR for PeopleSoft retrieves data from the database. The default is 10 rows. Regardless of the setting, all rows are retrieved. When used on the command line, -B controls the setting for all BEGIN-SELECT commands. Inside a program, each BEGIN-SELECT command can also have its own -B flag for further optimization.
-BURST:{xx}	-BURST:T generates the table of contents file only. -BURST:S generates the report output according to the symbolic table of contents entries that are set in the program with the level argument of the TOC-ENTRY command. In -BURST:S[ {l} ], {l} is the level on which to burst. The -BURST:S setting is equivalent to -BURST:S1. See Bursting Reports. Note. -BURST:P and -BURST:S require -PRINTER:EH or -PRINTER:HT. The page range selection feature of -BURST:P requires -PRINTER:HT. -BURST:T requires -PRINTER:HT.
-C	(Microsoft Windows) Specifies that the Cancel dialog box appears while the program runs so that you can easily stop the program.
-CB	(Microsoft Windows, Callable SQR) Forces the communication box to use.
-Dnn	(Non-Microsoft Windows) Displays the report output on the terminal while it is being written to the output file. The value for nn is the maximum number of lines to display before pausing. If no number is entered after -D, the display scrolls continuously. Note. The printer type must be LP; otherwise, the display is ignored. If the program is producing more than one report, the display is for the first report only.
-DBdatabase	(Sybase) Forces the SQR program to use the specified database, which overrides any USE command in the SQR program.
-DEBUG [xxx]	See #DEBUG.
-DNT: {xx}	See DECLARE-VARIABLE.
-E[file]	Directs error messages to the named file or to the default file program.err. If no errors occur, no file is created.
-EH_APPLETS:dir	Specifies the directory location of the enhanced HTML applets . If you include an applet, SQR for PeopleSoft must know where it resides. SQR for PeopleSoft usually checks for the applet in a default directory; the default directory for these applets is IMAGES. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_BQD	Generates a {report}.bqd file from the report data. This flag also associates a query format file (BQD) icon with {report}.bqd in the navigation bar. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_BQD:file	Associates the BQD icon with the specified file. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_BROWSER:xx	Generates HTML, determines the browser, and displays HTML. When this flag is set to ALL, SQR for PeopleSoft generates frame.html, which contains JavaScript to determine the browser on the user’s machine (that is, the person reading the report, not the person writing it). When this flag is set to BASIC, SQR for PeopleSoft generates HTML that is suitable for all browsers. When this flag is set to IE, SQR for PeopleSoft generates HTML that is designed for Microsoft Internet Explorer. When this flag is set to NETSCAPE, SQR for PeopleSoft generates HTML that is designed for Netscape. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_CSV	Generates a {report}.csv file from the report data. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_CSV:file	Associates the CSV icon with the specified file. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_CSVONLY	Creates a .csv file, but does not create an HTML file. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_FULLHTML:xx	Specifies the level of the generated enhanced HTML code. This can be 30, 32, or 40. Note. For upward compatibility, a value of TRUE is equivalent to 40 and FALSE is 30.
-EH_Icons:dir	Specifies the directory in which the HTML should find the referenced icons. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_IMAGES:dir	Specifies the directory path for the .gif files that are used by the navigation bar. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_KEEP	Copies (does not move) the files when used in conjunction with -EH_ZIP. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_LANGUAGE:xx	Sets the language that is used for the HTML navigation bar. You can specify English, French, German, Portuguese, Spanish, Japanese, Simplified Chinese, or Korean. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_PDF	Associates a PDF icon with {report}.pdf in the navigation bar. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_Scale:{nn}	Sets the scaling factor to a value from 50 to 200. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_XML:file	Associates the XML icon with the specified file. Note. This flag is applicable only when you specify the -PRINTER:EH or -PRINTER:EP flag.
-EH_ZIP[:file]	Moves the generated files to the specified file or {report}.zip if {file} is not specified. Note. This flag is applicable only when you specify the -PRINTER:EH or the -PRINTER:EP flag.
-F[file \| directory]	Overrides the default output file name, program.lis . The default action places the program.lis file in the same directory as the program.sqr file. To use the current directory, specify -F without an argument. To change the name of the output file, specify -F with the new name. If the new name does not specify a directory, the file is created in the current directory. The output file is not created until data is actually printed on the page. If no data is printed, no output file is created. Specify these file names and directories for different operating systems: UNIX/Linux Directory character is / -F$HOME/reports/ IBM MVS Directory character is ( -FDSN:SQR.REPORTS(
-GPRINT=YES \| NO	(IBM MVS) -GPRINT=YES includes control characters in the first column of each record of the SQR report output file. -GPRINT=NO omits the control characters.
-Idir_list	Specifies the list of directories that SQR for PeopleSoft searches when processing the #INCLUDE directive when the include file does not exist in the current directory and no path is specified for the file. The directory names must be separated by commas (,) or semicolons (;). For UNIX/Linux-based systems, if your shell uses semicolons as command delimiters, you must precede each semicolon with a backslash (\). Always append the directory character to the end of each directory name. See the -F flag for a list of directory characters, sorted by operating system.
-ID	(Non-Microsoft Windows) Displays the copyright banner on the console.
-KEEP	See Printing with SQR.
-LL{s \| d}{c \| i}	Specifies the following LOAD-LOOKUP values: s: SQR d: DB c: Case sensitive i: Case insensitive See LOAD-LOOKUP.
-Mfile	Defines a startup file containing sizes to assign to internal parameters—extremely small, large, or complex reports. Mfiles are text files that have individual switches in the INI files that are unique to a run.
-NOLIS	Prevents the creation of .lis files, creating .spfs instead.
-O[file]	Directs log messages to the specified file or to program.log if no file is specified. By default, the sqr.log file is created in the current working directory.
-olim	Displays the SQR resources that are used by the SQR report. Limit is the programmatic limit, the maximum amount of memory that can be allocated to the variable. Defined is the value defined in the pssqr.ini file; this is a user-defined value. Actual is the actual amount of memory/space used by the variable at runtime.
-P	(IBM MVS) Suppresses printer control characters from column 1.
-PB	(Informix) Causes column data to preserve trailing blanks.
-PRINTER:xx	Uses printer type xx when creating output files . The xx represents: EH: Enhanced HTML -PRINTER:EH EP: Enhanced HTML or PDF -PRINTER:EP HP: HP LaserJet -PRINTER:HP HT: HTML 2.0 -PRINTER:HT LP: Line printer -PRINTER:LP PD: PDF -PRINTER:PD PS: PostScript -PRINTER:PS WP: Microsoft Windows -PRINTER:WP LP, HP, and PS produce files .lis files. EH and HT produce .htm files. HT produces version 2.0 HTML files with the report content inside <PRE></PRE> tags. EH produces reports in which content is fully formatted with version 3.0 or 3.2 HTML tags. On Microsoft Windows systems, WP sends the output to the default Microsoft Windows printer. To specify a Microsoft Windows printer that is not the default, enter -PRINTER:WP:{printer name}, where {printer name} is the name that is assigned to your printer. For example, to send output to a Microsoft Windows printer named New Printer, use -PRINTER:WP:NewPrinter. If the printer name has spaces, enclose the entire argument in quotes. To create an .spf file also, use -KEEP.
-RS	Saves the program in a runtime file. The program is scanned, compiled, and checked for correct syntax. Queries are validated and compiled. The executable version is saved in a file named program.sqt. Note. SQR for PeopleSoft does not prompt ASK variables after compilation.
-RT	Uses the runtime file that is saved with the -RS flag. This skips all syntax and query checking, and processing begins immediately. Note. SQR for PeopleSoft does not prompt ASK variables after compilation.
-S	Requests that the status of all cursors be displayed at the end of the report run. Status includes the text of each SQL statement, the number of times each was compiled and run, and the total number of rows that were selected. The output appears directly on the screen. This information can be used for debugging SQL statements, enhancing performance, and tuning.
-Tnn	Specifies that you want to test your report for nn pages. To save time during testing, SQR for PeopleSoft ignores all ORDER BY clauses in SELECT statements. If the program is producing more than one report, SQR for PeopleSoft stops after producing the specified number of pages defined for the first report.
-T{B}	(Microsoft Windows, IBM DB2, Sybase CT-Lib, and ODBC) Trims trailing blanks from database character columns. If the TB flag is set in DB2 database environment, SQR trims the all-blanks fields (fields that contain only spaces) to NULL values in the SQR buffers. Using the TB flag on IBM MVS and DB2 has no effect. IBM MVS and DB2 prevent SQR for PeopleSoft from removing trailing blanks from database character columns. Note. The -TB flag has an effect only if SQR is connecting to a DB2, Sybase CT-Lib, or ODBC (MSS) database. Confusingly, the behavior of the -TB command-line flag varies depending on the platform. If you are using one of the previously mentioned databases and are running SQR on z/OS, the -TB flag will act in the following way: If you do not use the -TB flag, trailing blanks are trimmed. If you do use the -TB flag, trailing blanks are not trimmed. If you are running SQR on any other platform, the behavior of -TB is the opposite as described in the following examples: If you do not use the -TB flag, trailing blanks are not trimmed. If you do use the -TB flag, trailing blanks are trimmed.
-T{Z}	(IBM MVS and DB2) Prevents SQR for PeopleSoft from removing trailing zeros from the decimal portion of numeric columns.
-Vserver	(Sybase) Uses the named server.
-XB	(Non-Microsoft Windows) Suppresses the SQR banner and the SQR.... End of Run message.
-XC	(Callable SQR) Suppresses the database commit when the report has finished running.
-XCB	(Microsoft Windows) Does not use the communication box. Requests for input are made in Microsoft Windows dialog boxes.
-XI	Prevents user interaction during a program run. If an ASK or INPUT command requires user input, an error occurs and the program ends.
-XL	Prevents SQR for PeopleSoft from signing in to the database . Programs that you run in this mode cannot contain SQL statements. -XL enables you to run SQR for PeopleSoft without accessing the database. You still must supply at least an empty slash on the command line as a placeholder for the connectivity information. For example: sqr myprog / -xl
-XLFF	Prevents a trailing form feed.
-XMB	(Microsoft Windows) Disables the error message display so that you can run a program without interruption from error message boxes. Error messages are sent to an .err file. See the -E flag.
-XNAV	Prevents SQR for PeopleSoft from creating the navigation bar in .htm files that are generated with -PRINTER:HT. This occurs when only a single .htm file is produced. Multiple .htm files that are generated from a single report always contain the navigation bar.
-XP	(Sybase) Prevents SQR for PeopleSoft from creating temporary stored procedures . See BEGIN-SELECT.
-XTB	Preserves the trailing blanks in a .lis file.
-XTOC	Suppresses the table of contents for the report. This flag is ignored when you specify either -PRINTER:EH or -PRINTER:HT.
-ZIF{file}	Sets the full path and name of the SQR initialization file, sqr.ini.
-ZIV	Invokes the SPF Viewer after generating the program.spf file. This flag implicitly invokes the -KEEP flag to create program.spf. In the case of multiple output files, only the first report file is passed to the viewer.
-ZMF{file}	Specifies the full path and name of the SQR error message file, sqrerr.dat.

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

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 a character , number , or date . Columns that are defined in a query are declared automatically, except for dynamic columns and database or aggregate functions.

Variables

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 footers, 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 and Sybase.
$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, ODBC, SYBASE, and INFORMIX.
$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 in the argument, you do not need to predefine; assign the types based on the given variable types. For multirow 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

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.

Sample Reports

For an overview of how an SQR report looks, view the sample reports that are stored in the SQR for PeopleSoft directory <PS_HOME>\bin\sqr\<database_platform>\SAMPLE (or SAMPLEW, for Windows). You can modify these reports to meet your needs.