SQR Manual

SQ R What Wh at is is SQR? •

Structured Query Report Writer (SQR) is a programming pro gramming language that combines the power of Structured Query Language (SQL) queries, the sophistication of procedural logic, and the freedom freedo m of multiple-platform multiple-platform development. develop ment.1

Program Structure •

•

The basic structure of a SQR program consists of five sections: o Program (or Report) o Setup o Heading o Footing o Procedures Rules for Entering Commands: o All SQR commands are case insensitive. o Command names and arguments are separated by at least one space, tab, or carriage return character. o Most commands may consist of multiple lines, but each new command must begin on a new line. o Except where explicitly noted otherwise, you can break a line in any position between delimiters or words except within a quoted string. o You can optionally use a hyphen (-) at the end of a line to indicate the following line is a continuation. co ntinuation. o An exclamation point (!) begins a single-line comment that extends to the end of the line. line. Each comment line line must must begin with with an exclamation point. If you you need to display an exclamation point or single quote in your report, type it twice to indicate it is text.

1

Landres, G. and Landres, V. 1999. SQR in PeopleSof PeopleSoftt and Oth er Applications Applications. Greenwich: Manning Publications.

Page 1

•

Program File Header block and Pro gram Modification History block  o Must be placed at the beginning of each program. o Program naming conventions § First two characters (XX) represent the scope prefix § Second two characters (YY) represent the Product/Module qualifier § Fifth character (S) represents this as an SQR program (.sqr). Use an “I” to represent SQR include files (.sqc). § Sixth through eighth characters are sequential numbering !******************************************************************** ! XXYYSnnn.sqr:



*

!******************************************************************** ! ! Description: !

*

* *

!

*

! Input Parms:



*

!



*



! ! Output Parms:

*

-

! ! Tables:

*

! ! Notes:

*

* *

None

*

!

*

!******************************************************************** !

Modification History

! ! ProjectRef#

* *

Date

Who

Description

*

! ---------- ----------

---

-------------- --------------- -----*

! XXXXXXXXXX MM/DD/YYYY



Original Program Creation

*

!********************************************************************

•

Must Have: PeopleSoft begins each SQR program by setting setting SQL platform-specific environments. #includ #includ e ‘setenv.sqc’ ‘setenv.sqc’

!Set environm ent

The setenv.sqc file uses #define commands to define substitution variables that identify things that are specific to your database platform, operating system and default printer type. •

Begin-Setup !************************** !****************************************** ***************************** ***************************** **************** ! Begin-Setup * !************************** !****************************************** ***************************** ***************************** **************** #include 'setup32.sqc' ! Initialize Printer and Page Variables #include 'useprntr.sqc' ! Initialize Printer

Page 2

•

Program File Header block and Pro gram Modification History block  o Must be placed at the beginning of each program. o Program naming conventions § First two characters (XX) represent the scope prefix § Second two characters (YY) represent the Product/Module qualifier § Fifth character (S) represents this as an SQR program (.sqr). Use an “I” to represent SQR include files (.sqc). § Sixth through eighth characters are sequential numbering !******************************************************************** ! XXYYSnnn.sqr:



*

!******************************************************************** ! ! Description: !

*

* *

!

*

! Input Parms:



*

!



*



! ! Output Parms:

*

-

! ! Tables:

*

! ! Notes:

*

* *

None

*

!

*

!******************************************************************** !

Modification History

! ! ProjectRef#

* *

Date

Who

Description

*

! ---------- ----------

---

-------------- --------------- -----*

! XXXXXXXXXX MM/DD/YYYY



Original Program Creation

*

!********************************************************************

•

Must Have: PeopleSoft begins each SQR program by setting setting SQL platform-specific environments. #includ #includ e ‘setenv.sqc’ ‘setenv.sqc’

!Set environm ent

The setenv.sqc file uses #define commands to define substitution variables that identify things that are specific to your database platform, operating system and default printer type. •

Begin-Setup !************************** !****************************************** ***************************** ***************************** **************** ! Begin-Setup * !************************** !****************************************** ***************************** ***************************** **************** #include 'setup32.sqc' ! Initialize Printer and Page Variables #include 'useprntr.sqc' ! Initialize Printer

Page 2

The Setup section is optional. If used it contains commands that describe general report characteristics such as page size in lines and columns, whether form feeds are to be used, and printer initializations. o The Setup section is preprocessed and is automatically interpreted before the program begins execution. o PeopleSoft has standardized the Setup section using SQC files references by the #include command. Examples o setup31.sqc Portrait mode Landscape mode o setup32.sqc Extract or interface programs that do not print o setup06.sqc reports o

Note: These #include statements must must appear before the Begin-Heading section o

If customization of the Setup section is required (rather than using a #include of one of the standard setup .sqc files), the following statement must be included in order to handle some environmental settings that are database platform-specific: #include ‘setupdb.sqc’

o

Some of the valid commands for the Setup section include: ASK Prompts the user for substitution variable BEGIN-SQL Begins a SQL paragraph CREATE-ARRAY Creates an array of fields to store and process data DECLARE-CHART Defines the attributes of a chart DECLARE-IMAGE Declares the type, size, and source of an image DECLARE-LAYOUT Defines the attributes of a report layout layout DECLARE-PRINTER Overrides the printer defaults DECLARE-PROCEDURE Defines specific event procedures DECLARE-REPORT Defines reports and their attributes DECLARE-VARIABLE Allows user to explicitly explicitly declare a variable type DECLARE-TOC Defines Table of Contents and its attributes LOAD-LOOKUP Loads columns into an internal array

Page 3

o

To understand an SQR program, it will help to see how SQR views the page.

The locations are referenced in the commands that use them (for example Print) using (Row Position, Column Position, Length o f Item) •

Begin-Program !************************** !****************************************** ***************************** ***************************** **************** ! Begin-Program * !************************** !****************************************** ***************************** ***************************** **************** begin-program do Initialize-Processing Initialize-Processing ! Initialize Procedure do Process-Main ! Main Processing Procedure do Finalize-Processing Finalize-Processing ! Finalization Procedure end-program

Page 4

The Program section determines where SQR will begin and end execution of  your report. PeopleSoft uses this section to control the flow of processing by calling procedures using the Do the Do command, thereby utilizing modular programming. o Begin-Program executes after the Setup section. o This section is required! o

•

Begin-Heading !************************** !****************************************** ***************************** ***************************** **************** ! Begin-Heading * !************************** !****************************************** ***************************** ***************************** **************** begin-heading begin-heading # #include 'stdhdg01.sqc' ! Standard Heading Definitions end-heading

The Heading section usually contains Print commands Print  commands that are processed in the heading portion of a page. The headings are printed printed at the top of each page after the body of the page has been filled. o The # is the total number of lines that define the heading portion of the page. o PeopleSoft delivers several xxHdg##.sqc files, one of which is usually referenced by a #include, followed by any report-specific headings. o You must code whatever column headings are relevant to the particular report. o

•

Begin-Footing !************************** !****************************************** ***************************** ***************************** **************** ! Begin-Footing * !************************** !****************************************** ***************************** ***************************** **************** begin-footing begin-footing # #include 'reset.sqc' ! Reset Printer Procedure end-footing

Print commands that are processed in the The Footing section usually contains Print commands footing portion of a page. The footing is printed printed after the body and heading of the page. o The # is the total number of lines that defines the footing portion of the page. o The Footing section is a good place to put any confidentiality statements required by the university. o PeopleSoft delivers a simple footing section in reset.sqc that places the “End of  Report” text at the bottom of the last page of the report. o

Page 5

•

Begin-Procedure !*********************************************************************** ! Procedure: Procedure_Name * ! Description: Description of the procedure goes here. If more than * ! one line is required, line them up as in this example. * !*********************************************************************** begin-procedure procedure _name [paragraphs] [commands] end-procedure procedure_n ame

A procedure is a list of commands and/or paragraphs that are processed when referenced by a corresponding Do command. o Procedure names must be unique. o Procedures can contain regular SQL commands as well as three special kinds of paragraphs § SQL The Begin-SQL paragraph allows you to execute any non-select SQL statements (Insert, Update, Delete) § Document The Begin-Document paragraph allows you to mix background text with data retrieved from the database and user defined variables. Complex forms can be designed with the inclusion of graphic commands. The paragraph mimics rudimentary word processing. § Select The Begin-Select paragraph is the heart of the SQR program. o

begin-SELECT [DISTINCT][Loops=#][ON-ERROR= procedurename] ! normally one per line COLUMN_NAME  [SQR commands] ! must be indented FROM TABLE/VIEW  [WHERE…] [Order by …] end-SELECT § If the Select statement is not successful or returns 0 rows, the embedded SQR commands will not be executed. § Code all column and table names in uppercase. § SQR requires that column names be flush with the left margin and any SQR commands be indented. § SQR column variables - &Colname – are read only. They are populated from the database by the begin-SELECT paragraph. § SQR commands cannot directly reference column names, they can only reference the &Colname or &Synonym variable created by the begin-SELECT.

Page 6

SQR automatically provides a column variable: &Colname. If two database columns of the same name are SELECTed in two or more begin-SELECT paragraphs, they must be given different synonyms to avoid ambiguity during SQR’s parsing stage. An example of synonyms: begin-procedure Get-Student-Info begin-SELECT A.STUDENT_ID B.DESCR &Customer_Name C.DESCR &Course_Name FROM PS_PSU_STUDENT_TBL A, PS_PSU_CUST_TBL B, PS_PSU_COURSE_TBL C WHERE …….. end-SELECT print &A.STUDENT_ID (+1,1) print &Customer_Name (,+2) print &Course_Name (,+2) end-procedure Get-Student-Info

Processing Sequence SQR divides it’s processing into two passes through the source program. First Pass All #include external source files (.sqc) are inserted into the program source. All #commands are evaluated (For example: #IF/#End-If). The #define text substitution variables in the program source are replaced with their values (For example: {MyValue} is replaced by the value set in #define MyValue. The begin-setup section is processed, including allocation and population of memory arrays created by the Load-Lookup and Array commands. (Note: Load-Lookup commands can be placed in any section of the SQR program; however, it will only be executed at compile time if it is placed in the setup section). Work buffers are allocated by SQR (pssqr.ini) Optimization of the SQL data access path is determined by RDBMS. Syntax of SQR source program and .sqc source files is checked. • • •

•

• • •

Page 7

Second Pass In the second pass, the actual execution of the program source occurs starting at begin-program and stopping at end-program. SQR calculates the body section’s working page size by subtracting lines reserved by the begin-heading and beginfooting sections from Page-Size declared in the begin-setup section. Then based on the number of available lines, SQR: 1. Initializes variables 2. Processes data for the body section. If page overflow occurs or a New-Page command is encountered in the code: a. Processes begin-heading section b. Processes begin-footing section c. Writes page buffer to the output buffer d. Returns back to step 2, above 3. Processes begin-heading section 4. Processes begin-footing section 5. Writes entire page buffer to the output device and then erases the page buffer 6. Closes any open files, commits pending SQL transactions and terminates program. •

Printing in SQR The Print command is used in SQR to place and format information in the page buffer. The placement of information is controlled by a set of position coordinates. Edit masks and formatting options may also be used to control the appearance of the data. •

Position Coordinates o The Print command uses three coordinates to place information relative to the Heading, Body, and Footing area of the page buffer § Line § Column § Length o Two ways to specify position coordinates § Fixed places information in absolute grid positions within the appropriate buffer area § Relative uses a signed number to place information forward or backward a specified number of lines or columns relative to the current cursor position o Position coordinates can be numeric literals or user-defined SQR variables o If the line or column coordinate is zero (or omitted), the current position is used. If the length coordinate is zero (or omitted), the actual length of the literal or user-defined variable is used

Page 8

•

•

Explicit Print Command Use an explicit Print command to print a column variable (&Colname), a userdefined variable, or a literal. An explicit print is the only way to print SQR variables or literals. Syntax:

print {variable | literal }

([line][,[column][,[length]]])

Example:

print ‘Student Name’ print &A.Name print $Report-Title print ‘PeopleSoft’

(1,1,11) (,+2,30) (+1,10) ()

Center

Implicit Printing You can implicitly print the value from the database column by placing print coordinates to the right of the column name (or synonym). SQR implies that if print coordinates are present, it should print the value from that column in the position indicated. Implicit printing only works with database columns in a begin-SELECT paragraph. It cannot be used to print SQR variables or literals. Syntax:

Example:

begin-SELECT COLUMN1 [&synonym] COLUMN2 … FROM … end-SELECT

([line][,[column][,[length]]]) ([line][,[column][,[length]]])

begin-SELECT A.NAME (+1, 1,30) A.PHONE ( ,+5,15) FROM PS_PERSONAL_DATA A end-SELECT

Format Options With the Print command, you can use a variety of formatting options. Some of these options can be used in combination with others and some are mutually exclusive. This is true whether you are performing an explicit or implicit print. See the following chart for formatting compatibility:

Page 9

Bold

Box

Center

Edit

Fill

Shade

Underline

Wrap

Bold Box Center Edit Fill Shade Underline Wrap

Bold

Box

 Bold causes the information to be printed in bold type. Syntax:

print {variable | literal}

(position)

Bold

Example:

print ‘Course Description’

(0,+2,30)

Bold

 Box draws a one-line deep graphical box around the printed data. This option has no effect for line printers. Syntax:

print {variable | literal}

(position)

Box

Example:

print $subTotalLine

(+2,1,0)

Box

Here is a sample output for the coding example above: Total for Course 1001

Center

Edit

on 01/12/1996 is:

10

Use the Center option to position the information on a line. PeopleSoft routinely uses this option to center text in the standard headings. When the Center option is used, the column value of the position coordinate is ignored. Syntax:

print {String_var | literal}

(position)

Center

Example:

print ‘PeopleSoft’

()

Center

The Edit option allows you to edit each field before it is printed for improved appearance, consistency, or conformity to your business standards. There are several types of edit: § Numeric § Text § Date

Page 10

Each of these edit types has several format masks. When the length component of the position coordinate and the size of the edit mask are in conflict, the smaller of the two is used. It is better to leave the length coordinate blank and let the edit mask determine the length. Syntax:

print {variable | literal}

(position)

Edit

{mask}

Numeric Edit Masks

§ The 9 mask is used to print numbers. It fills to the right of the decimal point with zeroes, and fills to the left of the decimal point with spaces. Ma sk Value Result 9,999.99 100 1 0 0 . 0 0 9,999.99 123.456 1 2 3 . 4 6 9,999.99 -16 - 1 6 . 0 0 9,999.99 1234.56 1 , 2 3 4 . 5 6 9,999.99 12345.67 * * * * * . * * § The 0 mask if added to the front of a 9 mask to left fill with leading zeros. Ma sk Value Result 099999 100 0 0 0 1 0 0 099999 123456 1 2 3 4 5 6 § The $ mask is used to print numbers with a leading currency symbol. If you use a single dollar sign, it is printed in a fixed position. If you use two or more dollar signs, it is printed as a floating currency symbol. The currently symbol is set by the MONEY-SIGN parameter in pssqr.ini and can be changed using the SQR Alter-Locale function. Ma sk Value Result $9,999.99 100 $ 1 0 0 . 0 0 $$,$$9.99 100 $ 1 0 0 . 0 0 § The PS and PF masks may be added to the end of a numeric edit mask  to add parenthesis around negative numbers. Ma sk Value Result 9999.99PS -100 ( 1 0 0 . 0 0 ) 9999.99PF -100 ( 1 0 0 . 0 0 ) $$$9.99PS -10 ( $ 1 0 . 0 0 ) $$$9.99PF -10 ( $ 1 0 . 0 0 )

Text Edit Masks

Text edit masks allow you to format string data. The characters X , B, and ~ have special meaning. Any other characters in the edit mask are printed as literals.

Page 11

§ The X mask prints the corresponding character from the print string. § The B mask embeds a space in the corresponding position of the print string. § The ‘~‘ (tilde) mask skips the corresponding character from the print string. Ma sk Value Results xxx-xx-xxxx xxx~xx~xxxx xx/~xx/~xxxx (xxx)Bxxx-xxxx ‘(xxx) xxx-xxxx’

123456789 123-45-6789 12-31-2000 0123456789 0123456789

1 1 1 ( (

2 2 2 0 0

3 3 / 1 1

4 3 2 2

4 5 - 6 7 8 9 5 6 7 8 9 1 / 2 0 0 0 ) 3 4 5 - 6 7 8 9 ) 3 4 5 - 6 7 8 9

Date Edit Masks Cha ra cter Ma sk

Description

YYY, YY, Y

Last 3, 2, or 1 digit(s) of year. Assumes current century and/or decade. 4-digit year, “S” prefixes BC dates with “-“. Last 2 digits of year, for years in other centuries. Century: “S” prefixes BC dates with “-“. BC/AD indicator Quarter of year (1,2,3,4: JAN-MAR = 1). Roman numeral month (I-XII: JAN=1). Week of year (1-53) where week 1 starts with the first day of the year and ends with the seventh day of the year. Week of the month (1-5) where week 1 starts on the first day of the month and ends with the seventh day of the month. Day of year (1-366). Day of month (1-31). Day of week (1-7). Sunday is first day of week. Name of day. Abbreviated name of day. Japanese Imperial Era. Returns the name of the Japanese Imperial Era in the appropriate kanji (“Heisei” is the current era). Year of the Japanese Imperial Era. Returns the current year within the Japanese Imperial Era. Julian day (variation); the number of days since Jan 1, 4713 BC. Numbers specified with “J” must be integers. Meridian indicator. Assumes 24-hour clock unless meridian indication specified.

YYYY, SYYYY RR CC or SCC BC AD Q RM WW

W

DDD DD D DAY DY ER

EY J

AM PM HH

Page 12

HH12 HH24 SSSS N, NN, NNN, NNNN, NNNNN, NNNNNN MONTH MON MM MI SS

Hour of day (1-12). Hour of day (0-23). Seconds past midnight (1-86399). Fractions of a second. Precise to microseconds, however, for most hardware and databases, this much accuracy will not be attainable. Name of month. Abbreviated name of month. Month (01-12: JAN=01). Minute (0-59). Second (0-59).

§ Case matters in some date formats such as DY , DAY , MON , and  MONTH . For example, the mask  MON would print J AN, but Mon would print as J an .  DAY would print as MONDAY, but Day would print Monday. § Single quotes are required with spaces are included in the edit string. § The ‘\’ (backslash) character may be placed in front of any character to indicate that it should be treated as a literal, not the corresponding edit mask character. Ma sk Value Result MM/DD/YYYY ‘Day, Month DD, YYYY’ ‘The cu\rre\nt \mo\nth is: Month’

Fill

Shade

12-19-2000 12-19-2000 12-19-2000

12/19/2000 Tuesday, December 19, 2000 The current month is: December

The Fill option is used to fill an area of the page buffer with a specified set of characters. The characters will be repeated as many times as is necessary to fill the entire area defined by the position coordinates. The length component of the position coordinate is required when the Fill option is used. Syntax:

print {String_var | literal}

(position)

Fill

Example:

print ‘*’ (prints a line of asterisks)

(+1,1,124)

Fill

The Shade option draws a one-line deep, shaded graphical box around the printed data. For line printers this argument has no effect. Syntax:

print {variable | literal}

(position)

Example:

print ‘Grand Total is --->:’ print #Grand

(+3,1,0) (0,+2,0) Edit 9999

Page 13

Shade Shade Shade

Sample output for the coding example above: Grand Total is --->:

Underline

1382

The Underline option adds a horizontal line under the printed text. For line printers, underscore characters are used to emulate underlining. The Underline option is not compatible with the Box option. Syntax:

print {variable | literal}

(position)

Underline

Example:

print ‘Start Date’

(0,+2,0)

Underline

Sample output for the coding example above: Start Date

Wrap

The Wrap option prints a specified number of characters from a long text field. Once the current line is full, the remaining text is wrapped to the next line. This process continues up to a specified maximum number of  lines. Each new line of text is aligned directly under the previous line. The Wrap option recognizes word boundaries and, when possible, only breaks between two words. If a complete work will not fit on the current line, the entire word will be wrapped to the next line. The Wrap option also supports three additional arguments: § The Strip argument scans the print string for the specified “strip” characters. If a “strip” character is found, it is replaced with a space. § The On argument scans the print string for the specified “on” characters. If an “on” character is found, it is removed, and a break  occurs at that point. § Both Strip and On arguments will accept regular characters plus nondisplay characters whose ASCII values are surrounded by angled brackets (). ASCII code for carriage return is <13>. ASCII code for line feed is <8>. § The Keep-top argument keeps track of the top line so additional information could be printed on the same line as the beginning of the wrapped text. Syntax: print {String_var | literal} (position) {length_lit | _var} {lines_lit | _var} Strip={Strip_lit | _var} On={On_lit | _var} Keep-top

Page 14

SQR Variables and Comm ands SQR variable names can be any length and are not case sensitive. Variables are global throughout a report, unless declared as local arguments. The valid SQR data types are: Data Type

Ma ximum Size/Range of Values

Initial Value

Examp le

Text Date Float Decimal Integer

32,767 bytes Jan 1, 4713 BC to Dec 31, 9999 AD Double Precision – Platform dependent Precision (1-38 digits) -2,147,483,648 to 2,147,483,647

Null Null Zero Zero Zero

$Name $Today #Average #Salary #Counter

SQR provides internal, reserved variables with values maintained and updated by SQR. Variable

Description

#current-column $current-date #current-line

The current column position within the page grid. The current date/time stamp on the host machine. The current line position within the page grid. This is the absolute line position (not the line number relative to a report area). End of File (EOF) indicator. This variable is set to 1 following the execution of a Read statement that results in an EOF exception. The current page number. This value may be altered during execution. A value that may be passed back to the operating system at the completion of the SQR program. The number of rows affected by a DML statement (insert , update, or delete). The text message returned from the DBMS upon execution of an SQL command. The numeric return code from the DBMS upon execution of an SQL command. Name of encoding for character data written to the log file or console. The character data retrieved from and inserted into the database. Name of encoding for character data read from files used with the open command. Name of encoding for character data written to files used with the open command. The report generated by SQR (for example, an LIS file or a PostScript file). Name of encoding for SQR source files and include files.

#end-file

#page-count #return-status #sql-count $sql-error #sql-status $sqr-encoding-console, {sqr-encoding-console} $sqr-encoding-database, {sqr-encoding-database} $sqr-encoding-file-input, {sqr-encoding-file-input} $sqr-encoding-file-output, {sqr-encoding-file-output} $sqr-encoding-report-output, {sqr-encoding-report-output} $sqr-encoding-source, {sqr-encoding-source}

Page 15

$sqr-database, {sqr-database} $sqr-dbcs, {sqr-dbcs} $sqr-encoding, {sqr-encoding} $sqr-hostname, {sqr-hostname} $sqr-locale

#sqr-max-lines

#sqr-max-columns

#sqr-pid

$sqr-platform, {sqr-platform} $sqr-program $sqr-ver $username $sqr-report

•

The database type for which SQR was compiled. Valid values are: DB2, ODBC, SYBASE, INFORMIX, ORACLE Specifies whether SQR recognizes double-byte character strings. The value may be either YES or NO. The name of the default encoding as defined by the ENCODING environment variable when SQR is invoked. The value can be ASCII, JEUC, or SJIS. This variable contains the name of the computer on which SQR is currently executing. The name of the current locale being used. A “+” at the end of the name indicates an argument used in the locale has changed. The maximum number of lines determined by the layout. When a new report is selected, this variable is automatically updated to reflect the new layout. The 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. The process ID of the current SQR process. #sqr-pid is unique for each run of SQR. This variable is useful in creating unique temporary names. The hardware/operating system type for which SQR was compiled. Valid values are VMS, MVS, WINDOWSNT, or UNIX. The name of the SQR program file. SQR version (including version number, operating system, database type and date created). The database user name specified on the command line. The name of the report output file. $sqr-report reflects the actual name of the file to be used (as specified by the –f flag or NEW-REPORT command).

User-Defined Variables o

Substitution Var iables are used to supply all or part of an SQR command, or

SQL statement, at compile time. Normally, substitution variables are defined at the beginning of your SQR program using a #define statement. Subsequently, when you want to refer to the contents of the substitution variable, you simple put the variable name inside brackets. while#define statements may be placed anywhere in your SQR program, substitution variable must be defined before they can be referenced. Substitution variables may also be created through the use of the ask command as well as certain command line flags.

Page 16

Syntax:

#define sub_var [lit_value]

Example: #define ColR 108 … Let #Colr = {ColR} – 2 o

Runtime Variables are allocated and manipulated during the execution phase

of your SQR program. The valid data types for runtime variables are text , date, and numeric. Typically, runtime variables are allocated by reference and are not explicitly declared. When allocated by reference, runtime variables are initialized by SQR to null for text and dates, or zero for numbers. However, as of SQR version 4.0, variables can be explicitly declared and typecast using the declare-variable command in the Setup section. •

Declar e-Variab le Command

SQR provides the ability to explicitly typecast program variables via the declarevariable command. This command must be used in the Setup section or as the first statement of a local procedure. When declared in the Setup section, a variable is global (i.e., it is known throughout your program). Likewise, if a variable is declared within a local procedure, it will be known in that procedure only. By default, all numeric variables are created as data type float . The default-numeric option may be used to override this default. Date variables must be declared if they will be used in calculations or complex formatting Syntax:

declare-variable [Default-Numeric={Decimal[Digits)] | Float | Integer} [Type Var1A [Var1B][…]] [Type Var2A [Var2B][…]] end-declare  Default-Numeric

 Digits

Type

defines the default format for all numeric variables. This applies to all numeric variables not explicitly declared. If no value is specified, all numeric variables will default to float . specify the maximum number of digits allowed for decimal numbers. This value may range from 1 to 38. If omitted, the maximum number of digits is 16. specifies the data type of all variables that follow. This value must be decimal, float , integer , date, or text . If the data type is decimal, the maximum number of digits may be specified. As with the default-numeric argument, the maximum number of 

Page 17

digits may range from 1 to 38, and if omitted, will default to 16 digits. Example:

•

declare-variable default-numeric=decimal(8) integer #Qty #LineNbr decimal(12) #Price float #LineItemAvg text $PartNbr date $BackOrderDate end-declare

Assignment Statements o

The move command is used to copy data from one storage location to another. Optionally, an edit mask may be applied in conjunction with the move statement. Syntax:

move source_data to target_variable [edit_mask] source_data target_variable edit_mask 

may be a literal or a variable must be a user-defined variable any valid edit mask 

Example: move &name to $emplname move &NID to $nid xxx-xx-xxxx o

The let command is used to assign the value of a literal or variable, or the results of an expression, to a user-defined variable. An expression may be formed as a combination of operands, operators, and functions. Syntax:

let user_var = expression

Example: let #Stot = 0 let $EmplID = &A.EMPLID let #Avg = (#C1 + #C2 + #C3) / 3 o

•

Benchmark tests show that the move command is more efficient that the let  command for assignment statements.

 Display an d Show Commands o

The display and show commands are used to place information into the SQR trace file. This is the same place that SQR writes informational and error

Page 18

messages. For this reason, display and show commands are frequently used for debugging programs. The display and show commands can be used to show variables and literals, but cannot be used to show the results of  expressions. o

The display command writes a single value to the SQR trace file. By default, each display command writes its output on a separate line. The noline option may be added to the end of the display command to prevent SQR from issuing a carriage return after the value is printed. The display command also allows you to specify an edit mask when writing your output. All edit masks available to the print command are also available to the display command. Syntax:

display {variable | literal}

[edit_mask]

[noline]

Example: ! Write the SQR internal variables #sql-error and #sql-status ! to the SQR trace file display ‘The sql error is’ noline display #sql-status noline display ‘ – ‘ noline display $sql-error o

The show command also writes information to the SQR trace file. However, unlike the display command, show may be used to write one or more variables or literals with each statement. Syntax:

show {variable | literal} [edit_mask] [{var | lit} [edit_mask] …]

Example: show ‘The sql error is ‘ #sql-status ‘ – ‘ $sql-error The show command also allows you to specify formatting options for your output. All edit masks that are available with the print command are also available with the show command. Example: show ‘Report date: ‘ $AsOfToday Edit ‘Day, Month dd, yyyy’ show ‘Total revenue: ‘ #TotalRev Edit $$$$,$$$,$$9.99

•

Control Statements o

Do

The do command is used to call or invoke a procedure. The procedure must be located in the same file as the SQR program or in an SQC file that has been linked by a #include statement. The do command causes the flow of  the SQR program to branch to the named procedure. The code in that procedure is executed and then control is

Page 19

passed back to the next statement following the do command. Syntax:

do procedure_name [( parm_list )]  parm_list 

Example:

o

o

one or more literals or variables (separated by commas)

begin-program do Initialize-Processing do Process-Main do Finalize-Processing end-program The if command allows conditional execution of one or more SQR commands. SQR recognizes the logical operators =, <>, >, >=, <, and <= and the Boolean operators and , or , and not . An if statement may optionally contain an else block. The SQR commands defined within the else block are executed when the if condition is false.  If  statements may be nested within another if statement, but each one must have its own matching end-if .

If 

Syntax:

if condition {Command(s) to execute when condition is true} [else {Command(s) to execute when condition is false}] end-if 

Example:

if $EndOfReport = ‘Y’ print ‘End of Report’ () center end-if 

Evaluate

The evaluate command is useful for branching to different commands depending on the value of a variable or literal. The value being evaluated is compared to the when condition. If the condition is true, all of the statements within that when block are executed. If no when condition is true, then the when-other block is executed. Optionally, the break command may be used to exit from the evaluate construct. Conditions may be any simple logical expression. You may use the operators =, <>, >, >=, <, and <= in the conditional expression. The Boolean operators and , or , and not are not allowed in the evaluate statement. However, an or condition may be simulated by using two

Page 20

or more when conditions, with no intervening SQR commands.

o

Syntax:

evaluate {literal | variable} when condition_1 [{Command(s) to execute when condition_1 is true} [break ]] ... when condition_n [{Command(s) to execute when condition_n is true} [break ]] [when-other {Command(s) to execute when nothing else is true}] end-evaluate

Example:

evaluate &EmplType when = ‘S’ let $Type = ‘Salaried’ do Count-Salaried break  when = ‘E’ when = ‘H’ ! Implied ‘or ’ condition let $Type = ‘Hourly’ do Count-Hourly break  when = ‘N’ let $Type = ‘Not Applicable’ do Count-NA break  when-other ! when no other condition is true do Invalid-Type end-evaluate

While

Syntax:

The while loop is used to process a set of SQR statement for as long as some condition is true. The condition is always tested first, therefore the embedded SQR commands might not be executed. Optionally, the break command (typically in conjunction with an if statement) may be used to exit the while loop. It is the developer’s responsibility to do something within the loop to make the condition true. while condition [{Command(s) to execute while the condition is true} [break ] . . .] end-while

Page 21

Example:

while #Count < #TotalCount do More-work  if #Errs > 3 ! If error threshold is exceeded break ! Exit the while loop end-if  add 1 to #Count end-while

o

Break 

The break command may be used in a while or an evaluate command to perform an unconditional branch to the next statement following the end-while or end-evaluate.

o

Exit-SELECT

 Exit-SELECT is used to unconditionally branch to the next statement following the end-SELECT  command. It is similar to the break command as used in the while or the evaluate statements.

Syntax:

exit-SELECT

Example:

begin-SELECT EMPLID NAME do Reformat-Name if #Errs > 3 exit-SELECT end-if  FROM PS_JOB WHERE . . . end-SELECT

o

Goto

The Goto command is an unconditional branch to a specified label within the same program section (or procedure). The label must be entered on a line by itself, left-justified and must end with a colon. The use of goto is not recommended.

o

Stop

The stop command is an immediate termination of the currently executing SQR program. This is considered an abn orma l ter mination by SQR and will tr igger a database rollback. If used by itself, the stop command triggers SQR error 3301 and causes the following message to be added to the SQR trace file: (SQR 3301) Program stopped by user request.

Page 22

If accompanied by the quiet option, the program will be terminated, but the SQR trace file message is suppressed.

•

Syntax:

stop [quiet]

Example:

if #Errs > 3 show ‘The maximum number of errors was exceeded.’ stop quiet end-if 

String Manipulation an d Format ting o

Concatenation

The two most common techniques used to concatenate strings are the let and string commands.

Let

Using the let command, you may concatenate two or more string literals, variables, or expressions by placing the concatenation symbol (two vertical bars) between each one. Expressions are the result of the execution of an SQR string function.

Syntax:

let result_str_var = {StrLit1 | var1 | Expl}||{StrLit2 | var2 | Exp2}[ || …]

Example:

let $Address = $city || ‘, ‘ || $state || ‘ ‘ || &zip let $CSV = $Fld1 || ‘,’ || $Fld2 || ‘,’ || $Fld3 || ‘,’ || Fld4

String

The string command allows you to concatenate together two or more fields using a common delimiter. The result is placed in a text variable specified by the into argument.

Syntax:

string {Str_Lit1 | var1} {Str_Lit2 | _var2} [… {Str_LitN | _varN}] by {StrDelimiter_Lit | _var} into ResultStrVar

Example:

string &City &State by ‘, ‘ into $Address string $Address &Zip by ‘ ‘ into $Address string $Fld1 $Fld2 $Fld3 $Fld4 by ‘,’ into $CSV

o

Extraction

Substr

!Begin address line !Complete address line !Comma delimited output

As with concatenation, there are many methods used to extract a portion of one string from another. The most common involve the use of the substr function (with the let  command) and the unstring command. The substr function allows you to extract one string from another, starting at a specified position and continuing for a set number of  characters.

Page 23

•

Syntax:

let StrVar = substr({Str_Var | _Lit | _Exp}, { Start_Pos _Num_Lit | _Var | _Exp}, {Len_Num_Lit | _Var | _Exp})

Example:

let $AC = substr(&Phone, 1, 3)

Unstring

The unstring command divides a string into two or more text variables using a specified delimiter.

Syntax:

Unstring {Str_Var | _Lit} by {Str_Delimiter_Var | _Lit} into StrVar1 StrVar2 … StrVarN

Example:

unstring &Phone by ‘/’ into $AC $TheRest unstring $Input by ‘,’ into $Fld1, $Fld2, $Fld3, $Fld4

Miscellaneous o

o

o

Length

Instr

The length function returns the length of a string, including leading and trailing spaces. Syntax:

let NumericVar = length({Str_Lit | _Var | _Exp})

Example:

let #Len = length($EmplName) (find the length of the student name)

The instr function returns the starting position of one string within another string. You may begin your search at any point within the source string by specifying a starting position. Syntax:

let NumericVar = instr({Source_Str_Lit | _Var | _Exp}, {Search_Str_Lit | _Var | _Exp}, {Start_Pos_Num_Lit | _Var | _Exp})

Example:

let #Pos = instr(&Phone, ‘/’, 1) (find area code delimiter in phone #)

Rtrim, Ltrim

The rtrim and ltrim functions remove unwanted characters from the right or left side of a text string.

Syntax:

let New_Str_Var = {R | L}trim ({Source_Str_Lit | _Var | _Exp}, {Trim_Str_Lit | _Var | _Exp})

Example:

Let $Course = rtrim($Course, ‘ ‘) (trim trailing spaces)

Page 24

Let $Amount = ltrim($Amt, ‘*’) (trim leading asterisks) o

o

Rpad, Lpad

Edit

The rpad and lpad functions pad a source string up to a maximum length with a given string (usually spaces).  Rpad adds to the right side of the source string and lpad  adds to the left.

Syntax:

let New_Str_Var = {R | L}pad ({Source_Str_Lit | _Var | _Exp}, {Max_Len_Num_Lit | _Var | Exp}, {Pad_Str_Lit | _Var | _Exp})

Example:

let $FieldValue = rpad(&CourseRt, 4, ‘ ‘) (add up to 4 trailing spaces) let $Line = lpad(&OrderLineNbr, 6, ‘0’) (add up to 6 leading zeroes)

The edit function applies formatting to a literal, variable, or expression (of any data type) and returns a string value. Syntax:

let Str_Var = edit({Source_Lit | _Var | _Exp}, edit_mask)

Example:

let $Phone = edit(&Phone, ‘(xxx) xxx-xxxx’) (format phone number) let $Total = ‘Total Sales: ‘ || edit(#TotalSales, ‘$$$,$$9.99’)

o

o

Uppercase, L owercase

The uppercase and lowercase commands convert string variables to uppercase or lowercase respectively.

Syntax:

uppercase Str_Var lowercase Str_Var

Example:

uppercase $Course lowercase $Name

To_Char The to_char function converts a numeric literal, variable, or

expression into the equivalent character string.

o

Isnull

Syntax:

let Str_Var = to_char({Numeric_Lit | _Var | _Exp)

Example:

let $Total = to_char(#Counter)

The isnull function returns a true value (1) is the specified literal, variable, or expression is empty; otherwise it returns a false (0). This function is only valid for text or date values. A text field is considered to be empty if it contains a string whose length is zero.

Page 25

A date is considered to be empty if it contains null values. The isnull function is usually used as the conditional test in an if or while command.

o

•

Isblank 

Syntax:

let #Boolean_Var = isnull({Date_or_Text_Lit | _Var | _Exp})

Example:

if isnull($InputParameter) show ‘Input parameter was not specified’ end-if 

The isblank function returns a true value (1) is the specified literal, variable, or expression is empty or contains only white space. This function is only valid for text or date values. White space is any combination of null, tab, carriage return, form feed, and line feed characters (i.e., ASCII codes 0 and 9-13). The isblank function is usually used as the conditional test in an if or while command. Syntax:

let #Boolean_Var = isblank({Date_or_Text_Lit | _Var | _Exp})

Example:

let #Test = isblank($EmplID) if #Test show ‘EmplID was not specified or is empty’ end-if 

Date Manipu lation an d C onversion

As of version 4.0, SQR recognizes date data types and stores their values in a proprietary format. To take advantage of this data type, date variables must be explicitly declared using the declare-variable command. o

Datenow The datenow function returns the current date from the operating

system in SQR date format.

o

Dateadd

Syntax:

let Date_Var = datenow()

Example:

let $Today = datenow()

!Get Today’s Date

The dateadd function returns a new date value after a specified number of date units are added to or subtracted from a given date value. Valid date units are ‘year’, ‘quarter’, ‘week’, ‘month’, ‘day’, ‘hour’, ‘minute’, and ‘second’. Syntax:

let $New_Date = dateadd({Date_Var | _Exp}, {Date_Unit_Lit | _Var | _Exp}, {Num_Lit | _Var | _Exp})

Page 26

Example:

o

let $EndDate = dateadd(&StartDate, ‘day’, 15) (add 15 days to StartDate)

Datediff  The datediff function returns the difference between two dates

based upon a specified date unit type. Valid date units are ‘year’, ‘quarter’, ‘week’, ‘month’, ‘day’, ‘hour’, ‘minute’, and ‘second’. Only whole numbers are returned. Syntax:

let Num_Var = datediff({Date_Var1 | _Exp1}, {Date_Var2 | _Exp2}, {Date_Unit_Lit | _Var | _Exp})

Example:

let #Weeks = datediff($EndDate, $Today, ‘week’) (returns number of weeks between Today and EndDate)

o

Datetostr The datetostr function returns the alphanumeric representation of a

date value based on a date edit mask. If no edit mask is specified, the date format will default to the sqr_db_date_format specified in pssqr.ini.

o

Syntax:

let $Str_Var = datetostr({Date_Var | _Exp} [, edit_mask])

Example:

let $PPEDate = datetostr($EndDate, ‘Day, Month dd, yyyy’)

Strtodate The strtodate function returns the SQR date representation of an

alphanumeric string. The date edit mask is used to indicate what format the string is in prior to conversion. If no edit mask is specified, the date format will default to the sqr_db_date_format specified in pssqr.ini.

•

Syntax:

let $Date_Var = strtodate({Str_Lit | _Var | _Exp} [, edit_mask])

Example:

let $MyBirthDate = strtodate(‘04/08/1961’, ‘mm/dd/yyyy’)

Numeric Expressions, Commands, an d F unctions

SQR provides a wide variety of techniques for performing numeric calculations. The following are some of the more common numeric expressions, commands, and functions. o

Let

The let statement is the most common command used for numeric expressions. With the let command, you can program most linear equations using the standard operators; ‘+’, ‘-‘, ‘*’, and ‘/’.

Page 27

o

o

o

Add

Round

Trunc

Syntax:

let Num_Var = {Num_Lit1 | _Var1 | _Exp1} [Operator1 {Num_Lit2 | _Var2 | _Exp2} [Operator2 {Num_Lit3 | _Var3 | _Exp3} […]]]

Example:

let #NewDays = &LengthDays + 1 let #UnitDiscount = &Units – 25.00 let #CostOfUnits = &Units * 75.5 let #AvgLength = (#Fld1 + #Fld2 + #Fld3) / 3

SQR also provides a set of COBOL-like commands for numeric manipulation. These commands include add , subtract , multiply, and divide. Of these commands, however, only add is still in common usage. Optionally, you may round the result to a specified precision. If the resulting number is floating point, the precision may be from 0-15 positions to the right of the decimal point. Otherwise, with a decimal number, the precision may be from 0 to 38 positions to the right of the decimal point. Syntax:

add {Num_Lit1 | _Var1} to Num_Var2 [round = {Preci sion_Lit | _Var}

Example:

add 1 to #Index add &UnitAmt to #SalesTotal Round=2

The round function is used with the let command to round a fractional value to a specified decimal precision. If the number is floating point, the precision may be from 0-15 positions to the right of the decimal point. Otherwise, with a decimal number, the precision may be from 0 to the maximum size defined for the number. Syntax:

let Num_Var = round({Num_Lit | _Var | _Exp}, {Precision_Lit | _Var | _Exp})

Example:

let #basrt2 = round(#basrt, 2) let #trate = round(#trate, 22)

The trunc function drops all decimal digits beyond a specified position. If used with a floating-point number, the position may be from 0-15 positions to the right of the decimal point. Otherwise, with a decimal number, the precision may be from 0 to the maximum size defined for the number. Syntax:

let Num_Var = trunc({Num_Lit | _Var | _Exp}, {Position_Lit | _Var | _Exp})

Example:

let #AvgLength = trunc(#Units, 0)

Page 28

o

To_Number

The to_number function converts a text string to a numeric value. The to_number function scans the string from left to right one character at a time. If a numeric character is found, it is converted, and scanning continues. However, if  a non-numeric character is found the conversion process stops.

Syntax:

let Num_Var = to_number({Str_Lit | _Var | _Exp})

Example:

let #LengthDays = to_number($LengthDays)

PeopleSoft SQC Files PeopleSoft has developed several conventions to make it easier to manage your SQR environment. To reduce development time and ensure consistency, PeopleSoft has developed a library of frequently used procedures. The members of this library are called SQC files. SQC files can contain any type of SQR statement, from a few lines of code to a complete set of procedures. Where you #include the file depends on what type of code the file contains. If the SQC contains one or more complete SQR procedures, by convention, it is #included at the end of the SQR program. Otherwise, it will be embedded within the section that the SQC code supports. Some of the most common uses of SQC files include: o Definition of SQR environment variables o Definition of the Setup section o Definition of the standard heading o Definition of the footing section o Data formatting and conversion o Lookup of descriptions and translate values The following SQCs are common to most SQR programs: File Name

Description

Pr ocedu re Name

setenv.sqc

Defines information that is common to your database platform, operating system, and report environment. setup31.sqc (P) Standard setup sections that define the setup32.sqc (L) page layout, printer configuration, and paper orientation. datetime.sqc Creates edit masks for conversion of date and time variables from one format to another. It also contains procedures to perform the actual conversion of those

Page 29

N/A

N/A/ 

Init-DateTime Format-DateTime Format-NativeTime

datemath.sqc curdttim.sqc

number.sqc

readxlat.sqc

stdhdg01.sqc stdhdgtr.sqc fshdg01.sqc reset.sqc stdapi.sqc

variables. Converts dates between native database format and DTU format (yyyy-mm-dd) Retrieves the current date and time from the database. This SQC produces four variables: $AsOfToday and $AsOfNow, the date and time in native database format, and $ReportDate and $ReportTime, in standard report format. Contains generalized routines to initialize currency edit masks and to format numbers. Retrieves the long and short names from the Translate table for a specified field name, field value, language, and effective date. Contain print statements used as part of  the PeopleSoft standard heading Contains the reset procedure and the standard footing section. Contains the application program interface procedures. These procedures allow the SQR program to communicate with the Process Monitor and Report Manager inside the PeopleSoft environment.

Convert-To-DTU-Date Convert-From-DTU-Date Get-Current-DateTime

Init-Number Format-Number Format-Amt ConvertCurrency Read-Translate-Table

N/A

Reset Begin-Footing Define-Prcs-Vars Get-Run-Control-Parms Successful-EOJ

Other Heading SQCs HRMS/SA file

Fina ncial file

stdhdg02.sqc

stdhdg03.sqc

Descrip tion

Addit ional Var iables*

Company

$Company $CompanyName $Business_Unit $Business_Unit_Descr $AsOfDate

fshdg02.sqc

Business Unit

fshdg01.sqc fshdg04.sqc

As Of Date

stdhdg04.sqc stdhdg05.sqc stdhdg06.sqc

Pay Period End Check Date Pay Group fshdg06.sqc

Business Unit/Ledger

Page 30

$PayEndDate $AsOfDate $PayGroup $PayGroupName $Business_Unit $Business_Unit_Descr

fshdg06a.sqc

stdhdg07.sqc

stdhdg08.sqc

Business Unit/Ledger Group/Ledger

Pay Period End/Cost Center

fshdg07.sqc

SedID

fshdg01.sqc fshdg05.sqc

For the Period

stdhdg09.sqc

Company/As Of Date

stdhdg10.sqc

Company/EmplID

stdhdg11.sqc

Company/For The Period

stdhdg20.sqc

Standard Report (with two title lines)

$Ledger_Name $Ledger_Descr $Business_Unit $Business_Unit_Descr $Ledger_Group_Name $Ledger_Group_Descr $Ledger_Name $Ledger_Descr $PayEndDate $DeptID $DeptName $SetID $SetID_Descr $FromDate $ThruDate $CompanyName $CompanyDescr $AsOfDate $CompanyName $CompanyDescr $EmplID $Name $CompanyName $CompanyDescr $FromDate $ThruDate $ReportTitle2

* These variables are in addition to those identified in stdhdg01.sqc. You use procedures to modularize your programs and to make your code reusable. By saving commonly used procedures as SQC files, you create libraries of functions that may be called from any SQR program. To avoid possible conflicts with variable names, these procedures are often defined as local procedures. Local procedures allow us to create and utilize local variables that are hidden from the rest of your program. In order to use the code in SQC files that contain procedures, you need to do some upfront analysis. You need to determine the following: The SQC file name The procedure name Input variable(s), if any, and whether they are required or optional Output variable(s), if any • • • •

Page 31

Once you have finished your analysis, you can implement the SQC using these steps: Populate any necessary input variable(s). Perform ( Do) the procedure. Use the output variable(s) created by the procedure. Include the SQC at the bottom of your program (#include). • • • •

SQC filenames should always be entered in lowercase in the #include statement. UNIX filenames are case sensitive and PeopleSoft-delivered SQCs are always in lowercase.

 Advan ced Printing with S QR Page Commands •

•

•

NEW-PAGE

This function sends the current page buffer to the output device and begins a new one. A form feed character is added to the output file after each New-Page occurs, unless you specify Formfeed=No in the Declare-Layout for this program in the Begin-Setup section. A New-Page will automatically occur if page overflow is detected. Syntax:

new-page

Example:

begin-select A.COURSE if #CurrentLine > 65 new-page end-if 

PAGE-NUMBER

LAST-PAGE

This function places the current page number on the page. The text specified in the pre_txt_lit and post_txt_lit are printed immediately before and after the number.

Syntax:

page-number (position) [pre_txt_lit [post_txt_lit]]

Position Pre_txt_lit  Post_txt_lit 

Specifies the print position of the page number Specifies a text string to be printed before the page number Specifies a text string to be printed after the page number

This function places the last page number on each page, as in “page N of X”. Using last-page causes SQR to delay printing until the last page has been processed so the number of the last page is known.

Syntax:

last-page (position) [pre_txt_lit [post_txt_lit]]

Page 32

Position Pre_txt_lit  Post_txt_lit 

Specifies the position for printing the last page number Specifies a text string to be printed before the last page nbr Specifies a text string to be printed after the last page nbr

Example:

begin-footing 1 page-number (1,37) ‘Page ‘ last-page ( ) ‘ of ‘ ‘.’ end-footing

!will appear as !’Page 12 of 25.’

Column Commands •

NEXT-LISTING

 Next-listing causes a new vertical offset in the page to begin. This function ends the current set of detail lines and begins another. The logical top of page is reset to the position of the cursor following the next-listing command (i.e., the current line becomes line 1 of the report body).

Syntax:

next-listing [no-advance] [skiplines = #] [need = #]

no-advance

Suppresses any line movement when no printing has occurred since the previous next-listing or new page. The default increments the line position even when nothing was printed. Causes the specified number of lines to be skipped before setting up the new offset. Negative values will be ignored. Specifies the minimum number of lines needed to begin a new listing or set of detail lines. If this number of lines does not exist, a new page will be started.  Need can be used to prevent a group of  detail lines from being broken across two pages. If  the value is less than or equal to 0, then 1 is assumed.

skiplines

need 

Page 33

Example:

•

begin-select A.EMPLID (1, 1, 0) A.NAME (0, 20, 0) A.ORIG_HIRE_DT (2, 1, 10) A.ADDRESS1 A.CITY A.STATE A.POSTAL let $address = rtrim(&A.CITY, ‘ ‘) – ||’, ‘||rtrim(&A.STATE, ‘ ‘) – ||’ ‘||rtrim(&A.POSTAL, ‘ ‘) B.DESCR (4, 20, 0) next-listing skiplines=1 need=5 FROM PS_PERSONAL_DATA A, PS_COUNTRY_TBL B WHERE A.COUNTRY=B.COUNTRY ORDER BY A.EMPLID end-select

Columns are analogous to defining tab stops. The columns command defines the left-most position of one or more logical columns (or tab stops) within the current page layout. It moves the cursor to the position defined by the first logical column.

COLUMNS

Syntax:

columns [int_lit1 | _var1 | _col1 [int_lit2 | _var2 | _col2 […]]]

int_lit | _var | _col Example: •

NEXT-COLUMN

Specifies the left margin position of each column

columns 1 29 57

!defines the starting pos for 3 columns

This command sets the current position on the page to the next column defined with the columns command. If at-end  is used, goto-top is ignored.

Syntax:

next-column

at-end 

Takes effect if the current column is the last one defined when next-column is invoked Causes the current line in the next column to be set. This is useful when printing columns down the page. Specifies where to begin erasing the page when an at-end=newpage occurs.

goto-top

erase-page

Page 34

[at-end = {newline | newpage}] [Goto-Top = num_lit | _var | _co l] [Erase-page = num_lit | _var | _col]

•

•

USE-COLUMN

This command sets the current position on the page to a specified column defined with the columns command. The command use-column 0 turns off column processing.

Syntax:

use-column num_lit | _var | _col

Example:

use-column 3

!move cursor to the 3 rd logical column

Example: Prints columns across the page begin-procedure Process-Main columns 1 45

!define two columns

begin-select A.NAME (0, 1, 20) A.ORIG_HIRE_DT (0, +3, 0) next-column at-end=newline

!prints names and hire dates !across the page

FROM PS_PERSONAL_DATA A ORDER BY A.NAME end-select end-procedure Process-Main •

Example: Prints columns down the page begin-procedure Process-Main columns 1 45 let #BottomLine = 55

!define two columns

begin-select A.NAME (0, 1, 20) A.ORIG_HIRE_DT (0, +3, 0)

!print names and hire dates !down the page

if #current-line >= #BottomLine next-column goto-top=1 at-end=newpage else print ‘ ‘ (+1, 1) end-if  FROM PS_PERSONAL_DATA A ORDER BY A.NAME end-select end-procedure Process-Main

Page 35

Graphic •

BOX

 Box draws a box of any size at any location on the page. Boxes can be drawn with any size rule and can be shaded or left empty. Syntax:

graphic (line, column, width) box depth rule-width shading

 Line and column

Width and depth

 Rule-width

Shading

Example:

•

HORZ-LINE

Position coordinates for the upper left hand corner of the graphic. You can specify relative placement with (+), (-), or numeric variables, as with regular print positions. The width is the horizontal size in character columns; depth is the vertical size in lines. The top left corner of the box is drawn at the line and column specified. The bottom right corner is calculated using the width and depth. The default rule-width (thickness) is 2 decipoints. There are 720 decipoints per inch. Specify 0 if no border is desired. A number between 0 and 100 indicating percentage of gray scale. 1 is very light and 100 is black. If no shading is specified, 0 shading is assumed.

graphic (4, 5, 160) box 2 5 10 print ‘Outstanding balance due: ‘ (+1, 10)

 Horz-line draws a horizontal line from the location specified for the length specified. Horizontal lines are drawn just below the base line. Syntax:

graphic (line, column, width) horz-line rule-width

Example:

graphic (4, 1, 66) horz-line 10 ( put line under page heading) graphic (+1, 62, 12) horz-line ( put line under final total)

•

VERT-LINE

Vert-line draws a vertical line from the location specified for the length specified. Vertical lines are drawn just below the base line of the line position specified to just below the base line of the line reached by the length specified.

Page 36

Syntax:

graphic (line, column, depth) vert-line rule-width

Example:

graphic (1, 27, 54) vert-line (draw lines between columns) graphic (1, 52, 54) vert-line

Declare-Image

The declare-image command is used in the Setup section to define default characteristic for images. You do not have to declare an image before it can be printed. Images can be printed in a report with the print-image command whether or not they are declared in the Setup section. The only caveat is that if you don’t use the declare-image to define them, you must supply all three is its attributes as part of the print-image command. Different printer types accept different image types. For example, PDF files only accept JPEG and GIF files; SPF only accepts BMP files, and HPLaserJets only allow HPLG file. Syntax:

declare-image {Image_Name} [type = {Image_Type_Lit}] [image-size = {Width_Num_Lit, Height_Num_Lit)]

[source end-declare Example:

Print-Image

= {File_Name_Lit}]

begin-setup #include ‘ptset01.sqc’ . . declare-image pslogo type = bmp-file image-size = (40.5) source = ‘c:\temp\pslogo.bmp’ end-declare . . end-setup

The print-image command is used to print a graphical image. The  print-image command can be placed in any section of a report except the Setup section. The image file must be one of the supported formats (i.e., EPS, HPGL, JPEG, GIF, or BMP). Different printer types accept different image types. For example, PDF files only accept JPEG and GIF files; SPF only accepts BMP files, and HPLaserJets only allow HPLG file.

Page 37

Syntax:

print-image [image_name] (position) [type = {image_type_lit | _var | _col}] [image-size = (width_num_lit | _var | _col, height_num_lit | _var | col)] [source = {file_name_txt_lit | _var | _col}]

type =

Type of file for the image. SQR supports files of  type: EPS-FILE, HPGL-FILE, JPEG-FILE, GIFFILE, and BMP-FILE. image-size = The width and height of the image using standard printer coordinates (i.e., columns and lines). Example: a standard 7.2-decipoint character width is equivalent to 10 characters per inch. Therefore, to print an image that is 2 inches wide, you would specify a width of 20 columns. source = The path and file name of the image file.

Example:

begin-heading 12 !adjust size to allow for image print-image CompanyLogo (1,1) position (+6) #include ‘stdhdg01.sqc’ … end-heading

On-break is a very powerful SQR feature that will allow you to control what happens when values in a column break, or change. Specifically, you will use on-break to: Suppress duplicate values Produce subtotals Produce subheadings Control the flow of your SQR On-Break Pr ocessing

• • • •

Syntax:

print {field} (a, b, c)

on-break

{print = change | print = change/top-page | print = always | print = never} before = procedure_name after = procedure_name save = $text_var  skiplines = nn level = nn

Page 38

On-break uses a number of parameters to control printing and program flow: Print = Option has four possible choices: Print = change (default) Values for the columns specified in the on-break command are printed only when they change. Print = change/top-page Values are repeated after a page break even when they don’t change Print = always The values are always repeated (whether they change or not) and one or more additional on-break options are executed when the values change Print = never Values are never printed, but one or more additional onbreak options are executed when the values change •

•

•

•

•

•

•

Before = Specified procedure to invoke before the value changes. If no rows are selected, will not be processed. Can only be used within a begin-select paragraph.  Break= is very useful for subheading. After = Specified procedure to invoke after the value changes. If no rows are selected, will not be processed. Can only be used within a begin-select paragraph.  After= is very useful for subtotaling.  Note: Do not use relative position coordinates with print statements that include

either a before= or after= procedure that also includes print statements. The position of the cursor will be moved by the procedure(s) and therefore the relative coordinates may produce inconsistent results. •

•

•

Save = Indicates a string variable where the previous value of a break field will be stored. Skiplines = Specifies how many lines to skip when the value changes. If you use skiplines=, be prepared to experiment until you get the desired result. If  skiplines= is used it should be used on all levels for consistent results. Level = Option specified level of break for report with multiple on-break  options. When one of the break field changes value, all higher level numbers will be treated as if they broke as well. Lowest level is the outermost or most major; highest level is the innermost or most minor.

On-Break P rocessing Or der

Levels also affect the order in which on-break processing occurs. The sequence of  events for a query containing on-break fields is as follows:

Page 39

1. Any before= procedures that have been declared are processed in ascending level sequence (current level to highest) before the first row of the query is processed. 2. All save= variables are initialized. When a break does occur, the following happens: 3. The detail line is printed. 4.  After= procedures are processing in descending sequence from the highest level to the level of the current break field (the field triggering the break). 5. Save= variables are set with the new value. 6. If skiplines= was specified, the current line position is advanced. Only the skiplines= argument for the current level is honored. 7.  Before= procedures are processed in ascending sequence from the current break level to the highest on-break level number. 8. Any breaks with the same or higher level numbers are cleared. Their values will print regardless of whether or not their value changed. 9. The new value is printed. After last detail line is printed, after= procedures are processed in descending level sequence (highest to lowest). 10. After the query finishes (at end-select), any after= procedures are processed in descending level sequence (highest to lowest).  Note: In order for your SQR program to make appropriate use of the on-break 

logic, your SQL statement would need to Order by on the same fields on which you are breaking.

 Looku p Tables A common function within an SQR program is to join tables in order to get the long description of some data code. For instance, in a given table, you may have a COURSE or DEPT_ID but the corresponding description is in another table. This situation requires the SQR program to retrieve subordinate data and can be done via a join, or by performing a procedure that performs a separate SELECT from within the main SELECT statement. However, for large tables with large amounts of data, a table join can be a slow process. There is a more efficient way of retrieving this data in SQR. You can use lookup tables to save static data in a dynamic structure providing you more efficient data retrieval during the execution of an SQR program. Lookup tables are implemented in SQR programs using the load-lookup and lookup commands. Load-lookup This command can be used in any SQR section. It is good practice

to place your load-lookup commands in an initialization procedure, such as Init-Report.

Page 40

Syntax:

load-lookup name={lookup_table_name} table={database_table_name} key={key_column_name} return_value={return_column_name} [rows= {initial_row_estimate_int_lit | _var| _col}] [extent= {size_to_grow_by_int_lit | _var | _col}] [where= {where_clause_txt_lit | _var | _col}] [sort= {sort_mode}] [quiet]

 Name

The name of the lookup table. The array name is referenced in the lookup command. The name of the table or view in the database, where the Table key and return_value columns or expressions are stored. Key The name of the column that will be used as the “key” in the array, which is used for looking up the information. Keys can be character, date, or numeric data types. If  numeric, only integers 12 digits or less are permitted for the key column. Keys can be any database-supported expression and can be concatenated columns.  Return_value The name of the column (or expression) that will be returned for each corresponding key. Note you can combine several columns into an expression if you need several fields returned for each lookup. This is achieved by concatenating columns.  Rows The initial size of the lookup table. This is optional; if not specified, a value of 100 will be used. The amount to increase the array when it becomes full.  Extent  This is optional; if not specified, a value of 25 percent of  the initial rows value will be used. Where A conditional clause used to select a subset of all the rows in the table. If specified, the selection begins after the word where. When a literal value is used, the where clause is limited to 512 characters. Specifies the sorting method to be used. The following Sort  values are permitted: DC Database sorts data, case-sensitive sort DI Database sorts data, case-insensitive sort SC SQR sorts data, case-sensitive sort SI SQR sorts data, case-insensitive sort Suppresses all warning messages generated by the loadQuiet  lookup command (such as “Loading lookup array …”) when the command executes. The warning message stating the number of duplicate keys found is also suppressed. • • • •

Page 41

Example:

load-lookup name=Int-Rooms rows=12 table=PSXLATITEM key=FIELDVALUE return_value=XLATLONGNAME where=FIELDNAME=’’CLASSROOM’’’ and (effective dating logic) …

Given the following rows in PSXLATITEM: Fieldnam e Fieldvalue CLASSROOM A CLASSROOM B CLASSROOM C CLASSROOM D CLASSROOM E CLASSROOM X

Xlat LongNa me

Room A Room B Room C Room D Room E Other

The lookup table would look like this: Key Return_value A Room A B Room B C Room C D Room D E Room E X Other

Lookup

The lookup command searches a lookup table for a key value and returns the corresponding return_value string.

Syntax:

lookup {lookup_table_name_lit} {key_lit | _var} {return_value_var}

lookup_table_name key return_value

Example:

Specifies the lookup table. This table must be previously loaded with a load-lookup command. The key used for the lookup. It could be a column, variable, or literal. A variable into which the corresponding value is returned.

lookup Int-Rooms &RoomAbbr $RoomName

If the report is small and the number of rows to join is small, a lookup table is NOT the way to go. Using a lookup table can be slower because the entire table has to be loaded and sorted for each report run, which represents Limitations

Page 42

some processing overhead. Typically, the more subordinate data involved and the more table joins that are done, the more significant the performance boost can be for SQRs that use lookup tables. Which one is best? Try it both ways in your SQR program to find out. The only limit to the size of a lookup table is the amount of memory available. Except for the amount of memory available, there is also no limit to the number of  lookup tables that can be defined.

 Arrays and Graphics Arrays can be defined to store intermediate results or data retrieve from the database. For example, a SELECT paragraph can retrieve data, store it in an array, and gather statistics all at the same time. When the query finishes, a summary could be printed followed by the data previously stored in the array. Arrays can also be used when the subordinate data that is associated with a certain code, abbreviation, or ID is more than just one column. Rather than one data element like NAME associated with ID, perhaps there are several data elements. Or perhaps some of  the data that needs to be stored in conjunction with a certain data code is calculated and not just pulled from a table. You may even need to integrate information from an external data source, such as a file, with some table data. These scenarios do not lend themselves to a lookup table so a common array that stores many data items in each row makes more sense. Create-array

Creates an array of fields to store and process data. The createarray command can be written in any section of the program, however, SQR creates arrays at compile time (i.e., before a program starts to execute). Therefore, it is recommended that all arrays be created in the setup section of your program.

Syntax:

create-array

 Name

Names the array. The name is referenced in other array commands. Defines the number of elements in the array (analogous to table rows). This must be either a hard-coded value or a substitution variable. Defines each field or column in the array. Each field is defined as type: Number uses the default-numeric type Char (or Text) character string Date same as date variable

Size

Field 

name=array_name size=nn {field=name: type [:occurs] [=init_value_lit]} [{field=name: type [:occurs] [=init_value_lit]} …]

• • •

Page 43

•

Decimal [(p)]

•

Float

•

Integer

decimal numbers with an optional precision (p) double precision floating point numbers whole numbers

Fields can optionally have a number of occurrences, that is, they can be repeated occurs times, which gives the array an apparent third dimension. You can also specify an initialization value for each field. Each field is set to this value when the array is created or when the clear-array command is executed. If no initialization value is specified, numeric fields are set to zero, character and date fields are set to null. The maximum number of arrays in a single SQR program is 128. The maximum number of fields per array is 200. Example:

create-array name=Benefits size=12 field=EmpNum:number field=Name:char field=Coverage:char:3=’Full’ field=Dependants:number:3

That code would produce a virtual array something like this:

 Note: Subscripts start at 0.

Page 44

Value assignment

Columns retrieved from the database and SQR variables or literals can be moved into or retrieved from an array using the put , get , and let commands. The array must have been created previously using the create-array command. The let command can be used to reference a value in an array, copying that value to a variable. It also can be used to copy data to the array from a variable.  Let works as described previously. Get and put work in the same fashion as the move command.

Let syntax:

let $variable_name = array_name.array_field (#I) let var = array_name.field({row_nbr_lit | _var | _col} [,{fld1_occ_lit | _var | _col}]) let array_name.field([row_nbr_lit | _var | _col} [,{fldocc_lit | _var | _col}]) = expression

Example:

let $name = Benefits.name(5) let Benefits.name(9) = ‘Doe,Jane’

Pu t syntax:

put {value1_lit | _var | _col} [{value2_lit | _var | _col} […]] into array_name_lit ({rownbr_lit | _var | _col}) [field1[({fld1_occ_lit | _var | _col})] [field2[({fld2_occ_lit | _var | _col})] […]]]

Example:

!Data values are: !#j = 5, &EmpNum=621333, &Name = ‘Smith, John’, and !$DentalPlan=’E+3’ put &EmpNum &Name ‘Med-A’ $DentalPlan into Benefits(#j)

empnum name coverage(0) coverage (1) Get syntax:

get value1_var [value2_var […]] from array_name_lit({row_nbr_lit | _var | _col}) [field1{({fld1_occ_lit | _var | _col})] [field2 {({fld2_occ_lit | _var | _col})] […]]]

Example:

Get # empno $name $medical from Benefits(#j) empnum name coverage(0)

Page 45

Clear-array

The clear-array command resets each field of the named array to the initial value specified for that field in the create-array command. If no initial value was specified, numeric fields are reset to zero, text fields are reset to null, and date fields are reset to null.

Syntax:

clear-array

{name=array_name}

Example:

clear-array name=Benefits

The following four commands perform arithmetic on one or more elements in the array. The array must first be created using the create-array command. The four array arithmetic commands perform on one or more source numbers, placing the results into the corresponding field in the array. Additional Array Commands

Syntax:

array-add {numeric_lit | _var | _col} to array_name (row) [field[(occurs)]] … array-subtract {numeric_lit | _var | _col} from array_name (row) [field[(occurs)]] … array-divide {numeric_lit | _var | _col} into array_name (row) [field[(occurs)]] … array-multiply {numeric_lit | _var | _col} times array_name (row) [field[(occurs)]] …

If division by zero is attempted, a warning message is displayed, the result field is unchanged, and SQR continues executing. Common Pr ogramming Err ors with Ar rays o

Subscript Value pr oblems: The most common programming error to make

when working with arrays is to try accidentally to reference an array element using a subscript value that is outside the range specified by the array. The exact error reads as follows: (SQR 1500) Array element out of range (25) for array ‘rev_break_down’ on line 165.

This is typically not detected during compile because the reference to the array element often uses a variable as a subscript. Therefore, this particular programming error can be difficult to prevent, but easy to diagnose and correct. o

Looping pr oblems: Because loops are typically used to load and manipulate

arrays, another common error is getting stuck in an endless loop for whatever reason (not incrementing counters properly, referencing the wrong variable in Page 46

logic to break out of the loop, etc.). Another common error also related to looping is the mistake of using the wrong variable or counter in the subscript when referencing an array element. These code problems would not result in a specific SQR error but would cause the program to run endlessly because it was stuck in an endless loop. o

Data Type issues: Another common programming error is data type

conflicts. Trying to load an invalid data type in an array element or trying to lead an array element value in a different variable type are two common errors made when working with arrays. These errors are found during compilation and are easily diagnosed. The error for data type mismatching is as follows: Error on line 166: (SQR 3514) PUT and GET variables must match array field types. To help prevent data type errors, you can use the special characters in array field names. The special characters used in variable names to designate data type can be sued in the naming of fields in an array like this: create-array name=ClassBreakDown size=25 field=ProjRole$:char field=Count#:number SQR Charts

Arrays are used to create graphics in SQR. SQR provides two rather complex commands for creating charts in a report: declare-chart and print-chart . Several different chart types are supported from bar graphs to pie charts to xy-scatter plots. To add a business chart to an SQR report, there is a basic three-step approach that PeopleSoft outlines in their Developer’s Guides: 1) In the Setup section of your program, create an array and optionally define default chart characteristics: Use create-array to allocate an array. Arrays are used to store the data for charts and are created at compile time (and loaded at run-time). Optionally, you may use declare-chart to define the basic attributes of a chart. You can print multiple charts in one program, each of which could have unique chart attributes. •

•

Page 47

2) Put the data into an array. Load the data into an array. Charts used arrays for their data source. •

3) Print the chart. Use the print-chart command to print the chart. The attributes that were set in the declare-chart command may be overridden in the program with  print-chart command. Reference the data for the chart with the data-array argument. Provide certain print attributes, such as position coordinates and chart size. •

• •

Defines the default attributes of a chart that can be printed in an SQR report. The command may only appear in the Setup section of an SQR report. Among the available attributes are the following: Declare-chart

Syntax:

declare-chart {chart_name_lit} [chart-size = (chart_width_int_lit, chart_depth_int_lit)] [title = {title_txt_lit}] [sub-title = {subtitle_txt_lit}] [fill = {fill_lit}] [3D-effects = {3d_effects_lit}] [type = {bar_type_lit}] [legend = {legend_lit}] [legend-title = {legend_title_txt_lit}] end-declare

Chart-size is optional, but must be de fined in either the declare-chart or  print-chart command. Chart_name is the only required field because the other arguments have predefined default values. Some of the other arguments for the declare-chart command are described in the table below. The default values are underlined. For a complete listing of all command arguments, see the SQR Language Reference. Argument

Valid Values

Description

Default value

chart-size

title

none Text

sub-title

none Text

The width and depth of the chart specified in SQR position coordinates. Enter none for no title OR enter the text to be used as a title for the chart immediately after the = and enclosed in single quotes. Enter none for no sub-title OR enter the text to be used as a sub-title for the chart

Page 48

fill

grayscale color crosshatch none

3D-effects

yes no

type

line pie bar stacked-bar 100%-bar overlapped-bar floating-bar histogram area stacked-area 100%-area xy-scatter-plot high-low-close yes no none text

legend legend-title

immediately after the = and enclosed in single quotes. The sub-title is placed just below title. Specifies the type of fill that is applied to the shapes (bars, piesegments, etc.) that represent the data loaded into the chart. Grayscale varies the density of  black dots. Color sends color instructions to the current printer. If the current printer does not support color, then it may appear as grayscale. Crosshatch uses patters to fill the shapes representing each data set. None displays all graph shapes filled with white. 3D-effects will give your chart depth if set to yes. If no , then chart is displayed in a “flat” 2D mode. Specifies the type of chart.

Specifies if there will be a legend displayed for this chart. Title for the chart legend.

General Notes: § All declare-chart attributes may be overridden by the printchart command.

Page 49

§  Declare-chart arguments specified more than once will produce a warning. SQR uses the first instance of the argument and ignores all subsequent references. § Arguments can be specified in any order.

Print-chart

Prints a chart at the designated position within the report bu ffer.

Syntax:

print-chart [chart_name_lit] (position) data-array = array_name data-array-row-count = {x_num_lit | _var | _col} [chart-size = {chart_width_int_lit, chart_depth_int_lit)]

Chart_name

Specifies the name of the chart from the declare-chart  command. This name is not necessary if you specify the chart-size and all other pertinent attributes in the printchart command. Specifies the position of the upper-left corner of the chart. Position As with other print commands, position coordinates may be relative. After execution of the print-chart command, the cursor is repositioned to this po sition. Specifies the name of the array containing the data to be  Data-array plotted. This must be the name of an array defined with the create-array command. Specifies the number of rows or sets of data  Data-array-row-count  to be used from the data-array. If the data-array has a greater number of rows, only data-array-row-count  will be included in the chart.  Data-array-column-count  Specifies the number of columns to be used from the data-array. If the data-array has a greater number of columns, only data-array-column-count  will be included in the chart.

General Notes: § Print-chart expects the data-array to be organized in a particular way. For example, for pie charts, print-chart expects the data array to be composed of two columns and multiple rows of data; the first column should be character and the second column should be numeric. For a bar chart, print-chart  expects multiple rows of data with at least two columns, the first column character and the others numeric; each numeric column would represent another rows of data with at least two columns, the first column character and the others numeric; each numeric column would represent another Y-axis value.

Page 50