Structured Querry Report
Structured Query Report Writer is a programming language that combines the power of SQL queries, the sophistication of procedural logic, and the freedom of the multiple-platform development. It works equally well on client and server, crosses almost seamlessly between different platforms, and covers nearly all-relational databases.
With SQR we can extract data from and load data into the database, process complex file structures, print sophisticated reports with dynamic breaks at multiple levels, create interfaces between different systems, generate form letters with business charts, graphs and images.
SQR components include SQR Server, SQR viewer, SQR Print, SQR Execute, and SQR Workbench for windows.
SQR products are available for use with all major databases including oracle, Sysbase, MS SQL Server, DB2, SQLBase, Informix, Ingres, Red Brick, Rdb and AllBase.
With SQR we can do the following
1. Reports
2. Background sql process
3. Interfaces (file handling)
SQR will be stored on File Server not on the DB
SQR server runs on a wide variety of both desktop and enterprise operation systems
SQR programs are distributed at source level or as pre-compiled pseudo-code modules.
All SQR commands and functions are platform-transparent and require no changes when SQR programs are moved across platforms.
SQR SECTIONS:
In SQR there 5 Sections available
1. Setup Section – Begin-Setup/End-Setup
2. Heading Section - Begin-Heading /End-Heading
3. Footing Section - Begin-Footing/End-Footing
4. Program Section - Begin-Program/End-Program
5. Procedure Section - Begin-Procedure/End-Procedure
In SQR there are 3 Paragraphs available
1. Select Paragraph - Begin-Select/End-Select
2. SQL Paragraph - Begin-SQL/End-Sql
3. Document Paragraph - Begin-Document/End-Document
An SQR program consists of Sections. Each section starts with the Begin statement, follows with SQR commands that make up the section body, and ends with the End Statement.
Syntax
Begin-
End-
SQR program may include a number of different sections, but the only required one is the Program Section.
Begin-Program
End-Program
SQR Procedures:
An SQR program may include 1 or more SQR procedures. Procedures allow us to split our program into several logical pieces, which makes the program easy to understand and maintain.
Syntax:
Begin-Procedure
End-Procedure
Note: in earlier SQR versions the Program Section was called the Report Section.
No
Sections / Uses
1
Begin-Setup
End-Setup
This section contains commands that determine the overall characteristics of the program. This section is optional but, if included, must be placed at the beginning of the program. The Setup section is processed during the program compilation stage before actual program execution.
The following commands can be used in setup section: Ask, Begin-Sql, Create-Array, Declare-Variable, Load-Lookup, Use.
2
Begin-Heading
End-Heading
Commands in this section are executed every time SQR generates a page. When an SQR program generates multiple reports, we can have more than 1 heading section, however, there can be only 1 heading for each report.
The #lines parameter in the begin-heading statement defines the number of lines allocated for the heading.
3
Begin-Footing
End-Footing
The general structure of footing section is similar to the heading section.
The #lines parameter in the begin-footing statement defines the number of lines allocated for the footing.
4
Begin-Program
End-Program
This section is the required section in SQR program.
5
Begin-Procedure
End-Procedure
This section is optional, it can used to break our program into manageable and easy-to-maintain pieces. Multiple procedures can exist in SQR program, but must have unique names and be invoked with the help of the Do Command.
Procedures can global or local. By default, all procedures are global, all variables and selected columns from a global procedure can be referenced in other procedures.
To make procedure local, we have to explicitly declare it local using the LOCAL keyword. All procedures with arguments are considered as local.
All variables and selected columns created within a local procedure can be referenced only in this procedure and will not be recognized outside the procedure.
To reference a global variables in local procedure, we have to add an underscore to the variable name after its special character $,#,&. Ex: #_Count, $_Street, &_Emplid.
All SQR reserved variables are global. When referencing SQR-reserved variables in a local procedure, we have to add an underscore to their names ex:$_sql-error, #_sql-staus.
No
Paragraph / Uses
1
Begin-Select
End-Select
It is very similar to SQL Select statement. All selected column names are placed 1 per program line at the beginning of each line with no comma.
Note: all selected columns must be listed in the select paragraph; you cannot use an asterisk in the select paragraph.
Begin-Select Loops=10 determines the loop should repeat for only 10 rows.
When a row is not returned from a query, none of the SQR commands in the Select paragraph are executed.
2
Begin-SQL
End-SQL
SQR Select Command is similar to SQL Select statement, but not exactly the same. We have to use strict SQR syntax to select data and to process each selected row, and cannot use native SQL Select statement in SQR. Native SQL statements other than SQL Select are handled in the SQL paragraph. For ex, we may use this paragraph to load data into the DB or to update certain records in the DB. The SQL paragraph can be used in one of the following sections: Procedure section, Program Section, Setup Section.
The Sql paragraph starts with Begin-sql and ends with End-sql commands. If we have more than 1 sql statement in the same sql paragraph, the sql statements must be separated by a semicolon-‘;’.
On-Error clause can be used in SQl paragraph. This clause specifies the name of the procedure to be executed if an error occurs during the Sql exection.
Begin-Sql On-Error=Db_Error
Update Personal_data
Set Postal = $Old_zip
Where Postal = $New_zip
End-Sql
On-Error=Warn => is used when we want a warning message to be displayed when an error occurs.
On-Error=Stop =>is used when we want to halt the program if an error occurs. Note that the rest of the program will be scanned for errors but will not be processed.
On-Error=Skip => is used when we want to ignore all errors and to continue to run the program.
3
Begin-Document
End-Document
SQR Document paragraph allows to put information onto a report pages same as word processor. This is useful when creating form letters. We can design business forms or letters, and automatically populate them with data from the DB.
The Document paragraph allows combining the body of our standard letter with the values of variables or database columns. The Document paragraph can be used in an SQR Procedure section or in the Program Section. It is advisable to place it in the Procedure section. We can have multiple Document paragraphs in a single procedure.
Begin-Procedure Print_Document
Begin-Document (1,1)
Dear Friend:
.b ! use .b to print blank lines
This letter is written to let you know how simple is.
.b
Sincerely yours,
.b
.b
End-Document
End-Procedure
· Use spaces (not tabs) to indent text or field.
· Use .b to indicate an entire blank line.
· Use fixed or relative position to indicate the start of the paragraph on the page
· SQR commands are not allowed within the Document Paragraph.
· A Document paragraph must fit within a single report page.
· In order to place a variable or column into document, simply specify the variable or column name at the desired location.
Paragraph accessibility in Sections
Begin-Select
Begin-Sql
Begin-Document
Begin-Setup
No
Yes
No
Begin-Heading
No
No
No
Begin-Footing
No
No
No
Begin-Program
Yes
Yes
Yes
Begin-Procedure
Yes
Yes
Yes
SQR Dialog Box:
The SQR Dialog Box provides the perfect means of running SQR programs under windows. It also connects to the DB. The SQR dialog box is very simple to use.
When an SQR program runs under windows, you can see the results of all Display and Show statements executions on the screen. SQR will also display all compile errors on the same screen. All this screen information is written to the log file.
By default SQR creates an output file with name of your program and the extension ‘.lis’. This .lis file is basically designed for printing.
Unless otherwise specified SQR places the output file in the same directory with SQR program.
Note: if you do not specify different log file name for each SQR program run, the
next program execution will overlay the previously created log file.
Key Points
1. We can use ASCII text editor to type our SQR program
2. The SQR Dialog Box can be used to submit our program for execution under windows.
3. The following are commonly used extensions for different SQR files
.sqr - for SQR Programs
.lis - for Report files
log - for SQR log files
4. All print commands direct their output to the report file. All Show & Display commands direct their output to the log file.
5. An SQR program may include a number of different sections, but the only required one is the Program Section. Program Section may call one or more procedures using the DO command. Procedures may in tern call other procedures.
6. SQR uses an exclamation mark to indicate the start of a comment line in the program.
SQR Data Elements:
SQR has 3 main categories of data elements:
1. SQR columns
2. SQR Variables
3. SQR Literals
1. SQR Columns are used to reference columns defined in the database. In order to refer a table column, SQR uses the symbol ‘&’ as a prefix to the table column name. For ex: &Emplid, &Hire_date. We don’t have to declare column variable in SQR program.
2. SQR Variables can be
String variable, Numeric variable, Date Variable.
SQR uses ‘$’ symbol for String, Date variables ex:$Name,$MyDate
‘#’ Symbol for Numeric variables ex:#Count
‘%’ Symbol for list variables ex:%MyList
3. SQR Literals are String, Numeric or Date Constatns.
String Literals are placed in single quotes – ex: ‘this is string literal’
Numeric literals may include digits with optional decimal point and leading sign. Ex:2043.44,-434.55
SQR variables do not have to be declared explicitly in the program. When a variable appear for the first time, SQR assigns this variable its type (based on the first character of the variable’s name) and initial value. All string variables are initialized to the NULL valued. All numeric variables are initialized to zeros. All string variables have variable length.
The Declare-Variable command, which allows to explicitly declaring variables.
When date variables are declared, they are initialized to NULL values similarly to string variables.
Variable names are not case-sensitive: for ex- $Name is same as $NAME or $name. Variable names can be of any length.
SQR variables can be global or local. Global variables can be referenced throughout the entire program, whereas Local variables are effective only within a local procedure.
Predefined SQR Variables:
Predefined variables are not write-protected, so we can change their values just as like other variables.
Variable
Use
#Current-line
Denotes the current line number within the current program.
#Current-column
The current column number on the page.
#End-File
If the controls is at the end of file then #end-file = 1
Otherwise, #end-file = 0.
#Page-Count
The current page number
$Current-Date
The current date and time on the local machine when program started running.
#Return-Status
This value will be returned by SQR to the calling program.
#Sql-Count
The number of rows affected by any Sql DML statement (Insert, Update, Delete)
Note:#Sql-Count cannot be used to check the number of selected rows. It is used only to verify updates.
$Sql-Error
This string variable contains an error explanation message from the DB
#Sql-Status
A status value returned from the DB after each SQL statement is executed.
$Sqr-Database
SQR installation is set up to work with some DB and this variable can be checked in an SQR program to determine what DB we are working with.
#Sqr-Pid
The process Id of the current SQR run. This value is unique for each run of an SQR program and can be used to create unique composite file names.
$Sqr-Platform
This variable tells our SQR program which Operating System the program is run under. Valid values are DOS/Windows/Windows-NT/Unix/Vm/VMS/MVS.
$Sqr-locale
The name of the current locale. The SQR locale name is defined in the SQR.ini file.
$Sqr-dbcs
Specifies whether SQR recognized double-character strings. Valid values are ‘Yes’ or ‘No’
$Sqr-Program
The name of the SQR program
$Sqr-Report
The name of the report output file. This is actual name specified in the –F flag parameter or in the New-Report command.
$Sqr-ver
A string with SQR version ID.
$Username
The user name specified on the command line.
$Sqr-hostname
The name of the computer on which your SQR program is executed
#Sqr-max-lines
The max number of lines for the current report
#sqr-max-columns
The max number of columns for the current report.
Working with Dates
Date variables must be explicitly declared with the help of the Declare-Variable command. Date variable names are prefixed with a dollar sign-$.
In SQR dates can be stored as character strings in string format variables or as date in special date format variables.
Ex:
Begin-Setup
Declare-Variable
Date $MyDate
End-Declare
End-Setup
$Current-Date reserved variable can be used to get current date & time from the system.
List Variables
List variables contain ordered collections of SQR variables. These variables are not nested: you cannot include one list variable within another. List variables cannot be passed as parameters to local functions.
List variables are denoted with a special character %. Unlike other variables these variables cannot be declared via the Declare-Variable command. They are created and manipulated with the LET command. We can use list variables to hold one set of variables or multiple rows of information of similar structure.
Arithmetic commands:
There are 4 arithmetic commands are available
1. Add - Add #Employee_Salary To #Total_By_Department
2. Subtract - Subtract 1 From #Employee_Count
3. Multiply - Multiply &Rate_Increase Times #Total Round 2
4. Divide - Divide #Number-of_Employees Into #Avg_Sal Round 2
String Manipulation Commands
SQR has the following commands to perform string manipulations
1. Find - Find ‘John’ In $Full_Name 0 #Position
2. Concat - Concat $Full_zip with $Address
3. Extract - Extract $First_Name from $FullName #StartLocation #Lenth
4. Encode -
5. String - String $Emplid $Empl_Name By ‘,’ Into $Empl_Record
6. UnString - Unstring $Empl_Nam By ‘-‘ Into $FirstName $Initial $LastNam
7. Uppercase -
8. Lowercase -
Move Command
The Move command is used to moving data from one field to another, it can also perform data conversions and data editing using special edit masks.
The Move command can handle data of any format. The source and target fields for the Move command can have different formats. For ex: the source can be a numeric variable, and the target can be a string variable. In this case, the command not only moved data, but also performs the numeric-to-string conversion.
Move ’15,995.00’ To #Total !Results in incorrect move (error is - , comma)
Move ‘15.99500’ To #Total !Results in correct Move
Some Examples of Move command
Move &Phone To $Disp_Phone (xxx)bxxxx-xxx
Move #Salary To $Disp_Salary $9,999,999.99
Move ‘Month DD, YYYY’ To $Date_Mask
Move &Counter to #Number_of_Employees Number
Move &Annual_Rate To #Annual_salary Money
Move $Hire_Date to $Start_date Date
LET Command
The Let command is much more complex and versatile than other date element manipulation commands. A single Let command may be capable of replacing a number of date manipulation and logic commands.
Syntax: Let target_variable = expression
Expressions in the let command can be combinations of operands, operators and functions.
Let #Number_of_days = $Julian_day
Let #ytd_expense_avg = #ytd_expnse_total / #Number_of_days
The Let command can also be used to create and manipulate List Variables. List variables are used in conjunction with the Begin-Execute command that used list variables as parameters to pass them to external sources of information. SQR arrays cannot be used for this purpose.
Here is how we can create a list variable using the Let command
Let %Customer = List(#Cust_Num, $Cust_Nam, $Cust_Birth_Dt)
The command creates a single-row list variable. To create a multiple-row list variable, use slightly different format of the same command.
Let %Customer[100]=List(NUMBER’.Cust_Num’,TEXT’.Cust_Nm’,DATE’.Cust_Birth_dt’)
The number between the square brackets indicated the number of rows in the list.
To retrieve values stored in components of list variables, use the following Let command.
Let $My_Cust_Name = %Customer[20].Cust_Nm
SQR Functions
SQR version 6 offers more than 50 different built-in functions. In addition, we can write our own functions in C using the supplied source file ‘ufunc.c’
The following are the different types functions available in SQR.
1. Numeric Functions
2. File-related Functions
3. Date Functions
4. String Functions
5. Miscellaneous Functions
1. Numeric Functions:
Abs():this function returns a value of the same type as its argument either integer, float or decimal.
Let #Percent_difference = (abs(#Avg_salary - &Salary) / #Avg_salary) * 100
2. File-related Functions:
These functions are helpful in performing file maintenance, and can sometimes replace calls to the OS.
There are 3 functions available. All these function returns Zero if the operation was successful; otherwise they return the system error code (platform-specific)
a. Delete() : Let #status = exists($New_file_Name)
If #status=0
Let DelStatus = Delete($New_file_name)
End-if
b. Exists() : Move ‘C:\temp\testb.lis’ To $New_File_Name
Let #status = Exists($New_file_Name)
c. Rename(): Let #Status = Exists($New_file_name)
If #Status =0
Let RenFile = Rename($File_Name, $New_file)
End-if
3. Date Functions:
a. StrToDate() : This function converts String to Date
Date $MyDate1
Date $MyDate2
Let $MyText1 = ‘19980315’
Let $MyText2 = ‘98/03/15’
Let $MyDate1 = StrToDate($MyText1)
Let $MyDate2 = StrToDate($MyText2, ‘yy/mm/dd’)
b. DateToStr() : it converts date to a string
Let $Date_String = DateToStr($MyDate, ‘MonbDD,byy’)
c. DateAdd() : this function adds a specified number of time units to a date
Let $Fifth_Anniv_dt = DateAdd(&Hire_Dte, ‘YEAR’, 5)
d. DateDiff() : used to calculate the difference between 2 dates
Let #Diff_Months = DateDiff(&Effdt, $Calc_date, ‘Month’)
e. DateNow() : this function returns the Current date and time
Let $Date_time= datenow()
Note: the difference between the SQR predefined variable #Current_date & DateNow() functions is
#Current_date variable: contains the date and time when our program started
DateNow(): function returns the current date and time.
4. String Functions:
There are 16 string functions is SQR version 6
a. Instr() :
b. IsBlank() :
c. IsNull() :
d. Length() : tells the number of characters in a string or date
e. Lower() : converts the characters to lower
f. Upper() : converts the lower characters to Upper characters
g. Ltrim() : eliminates the left side spaces
h. Rtrim() : eliminates the right side spaces
i. Lpad() :
j. Rpad() :
k. Replace() :
l. SubStr() :
m. To_Char() : converts the source numeric literal, column, variable or expression to the character format and preserves the precision of the source
n. To_Number() : converts the source string or expression to the numeric format.
o. Translate() :
p. Edit() : this function is similar to move command. Formatting the
source field according to the specified edit mask.
Ex: Let SSN_No = Edit(#SSN, ‘xxxx-xxx-xxxx’)
Let Dformat = Edit(Datenow(), ‘DD/MM/YY’)
5. Miscellaneous functions:
1. Array()
2. Ascii()
3. Asciic()
4. Chr()
5. Cond()
6. Getenv()
7. Nvl()
8. Range()
Key Points
1. The 3 main SQR data elements are Columns, Variables and literals
2. SQR columns are fields defined in the DB. SQR uses the symbol & as the first character of a table column name
3. SQR variables can be either string variables or numeric variables. SQR uses the special character $ for string variables and the special character # for numeric variable.
4. The numeric variables can be integer, floats, or decimals.
5. SQR list variables are ordered collections of SQR variables, literals or column variables.
6. SQR literals are string, date or numeric constants.
7. SQR variables do not have to explicitly declare in the program; when a variable appears for the first time, SQR assigns this variable its initial value.
8. SQR variables can be global or local. Global variables can be referenced throughout the entire program while local variables are effective only within a local procedure.
9. SQR provides you with a number of date element manipulation commands, including arithmetic commands and string manipulation commands.
10. The MOVE command helps you to move data elements from one field to another with optional editing using special edit format masks.
11. The LET command has the most power allowing you the use of sophisticated expressions. As a result, one Let command can replace a number of elementary arithmetic or string commands.
SQR Page
SQR does not print into its output file every time we use the print command. Instead, SQR accumulates the entire page in the memory. The heading and footing sections are generated after the body of the pages has been filled. Once the entire page is complete, it is written to the output file and is erased from the memory just in time to be replaced with the next page.
How SQR process the source program:
SQR goes over the source program in to 2 stages.
1. Compile Stage
· All external source files are inserted into the program source file.
· All compiler directives are evaluated. Compiler directives start with # and are processed during the compile stage.
· All substitution variables are resolved.
· SQR executes all ASK Commands that prompt the user for the values of some substitution variables not code in the program.
· Program memory and work buffers are allocated. All memory arrays are created.
· SQR checks the syntax of the source program.
· SQR determines how to optimize the SQL data access.
2. Execution Stage
· Actual program execution takes place only if SQR finds no compile errors in the source program.
· Starts processing at Begin-Program and stops at End-Program.
· Calculates the size of the report working page.
· Processes the report body
· Processes the report heading
· Processes the report footing
· Writes the entire page to the output ifle and gets ready for the next page.
Usually wont separate these 2 stages, when running the source program, SQR runs through the compile stage and, if no errors are found, immediately switches into the execution stage.
In some cases, we may want to proceed directly to the execution stage, SQR does allow to perform the compile step once, the save the pre-compiled version of the program, to return your report at a later date. If we use pre-compiled version of a program, SQR will not re-execute the required compile steps, including the ASK command. SQR will use all external source files inserted during the compilation stage as well as all substitution variable values assigned.
Key Points
1. SQR page consists of a heading, a body and a footing.
2. SQR accumulates all the report pages lines in the memory and writes the entire page into the output file.
3. SQR processes programs in 2 steps: 1.Compile Step, 2.Execution step.
4. An SQR program may include the Program section, Setup section, Heading section, Footing section and Procedure section. The only required section if the Program Section.
5. The Setup Section determines the overall characteristics of the program. The setup section is processed during the compilation stage.
6. Commands in the Heading and Footing sections are executed every time SQR generates a page.
7. The procedure section is used to break program into manageable and easy to maintain pieces. We can have multiple procedure sections in our program. Each procedure is invoked with the help of the Do command.
8. Procedures can be global or local; by default all procedures are global. To make a procedure local we have to explicitly declare it with the LOCAL keyword.
9. All procedures that have arguments are local
10. To reference a global variable within a local procedure, we have to add an underscore to the variable name after its type character $, # ,&.
11. All SQR reserved variables are global, when referencing SQR reserved variables in a local procedure; we have to add an underscore to their names.
Column Variable
When a column value is selected from a DB SQR automatically creates a column variable for this column. The name of the column variable is the column name prefixed with an ampersand-& symbol. We can use this name to reference the column variable in our program. Ex: &Emplid, &Name.
Begin-Select
Count (*) &EE_count
Print ‘Employee count=’ (1,1)
Print &EE_count
From employment_data
Where hire_dtAnd Empl‑rcd = 0
End-select
Explicit and Implicit printing
Explicit Printing:
We use Print command to print the selected column values. This is called explicit printing.
Ex: Print ‘ Company Name ’ (1,10)
Implicit Printing:
By placing a position parameter immediately after the column name. The printing will perform at the moment of selection.
Ex: Begin-Select
Emplid (+1,1) !implicit printing
Name(,12)
From personal_data
Where state=’NY’
End-Select
The order of both implicit and explicit print commands output is determined by the order in which these commands are coded in the select paragraph. You can also include print edit parameters in Select paragraph. These parameters define the output edit masks for each column. Ex:
Begin-Select
A.Zip (,+2) Edit 99999-999
A.Phone (,+2) Edit xxx/xxx-xxxx
From personal_data
End-Select
Load-Lookup and Lookup
Load-Lookup and Lookup is very useful technique of retrieving information from multiple tables. This method significantly improves the performance, but also simplify table joins.
Load-Lookup: this command creates an internal memory array and populates this array with keys and the corresponding column values from a specified table. This command can be used in either the Setup section or any procedure, but it is important to remember that, if the Load-Lookup command is used in the Setup section, it is processed only once, when used in a procedure, the command is processed every time the procedure gets executed.
Load-Lookup retrieved 2 fields from the DB: 1. Key field 2. Return_value field. The key field may be of the string or numeric format, and it must refer to a column with unique values in the table. No NULL values are allowed in the field. The Return_value field may refer to just one table column or a combination of several table columns. In case of a column combination, the columns must be concatenated. We can have the Load-Lookup command populate its array with only certain records based on some specified selection criteria.
Ex: Begin-Setup
Load-Lookup Name = Company_Name
Rows = 200
Table = Company_tbl
Key = company
Return_value = Descr
Where = country = ‘USA’
End-Setup
In this ex, the Load-Lookup command creates an array named Company_Name and populates the array with the company names from the Descr column of the Company_tbl. When populating array, Load-Lookup uses the SQL where clause to select only table rows with the Country column value equal to ‘USA’. The Rows parameter specifies the initial size of Lookup array as 200 rows. This parameter is optional and, if omitted, a default value of 100 will be used. When an array becomes full, another optional parameter, Extent, will be used to increase this array size. If no Extent is specified, 25% of the initial size will be used as a default value. Note that the only limit of the size of a Lookup table is the amount of memory available in your computer.
Lookup: this command is used to search through internal memory arrays created and populated by the Load-Lookup command. It returns the Return_value field value for each specified key field value. SQR uses the binary search algorithm to search through the array.
Begin-Select Distinct
Emplid (+1,1)
Company
Lookup Company_Name &Company $Comp_Name
!(the lookup command retrieves the $Comp_Name value based on the key stored in &Company)
From Job A
where A.effdt = (select max(effdt) from job
where emplid = A.emplid
and empl_rcd = A.empl_rcd)
and A.effseq = (select max(effseq) from job
where emplid = A.emplid
and empl_rcd = A.empl_rcd
and effdt = A.Effdt)
End-Select
*** we can also build the where clause in the Load-Lookup command dynamically by placing it into a string variable.
Begin-Program
Let $where = ‘A.Plan_type=’’00’’ and’
‘A.effdt = (select max(effdt) from deduction_tbl’
‘where Play_type = A.Play_type and Dedcd=A.Dedcd)’
Load-Lookup name=Ded_codes
Rows=5
Table=”Deduction_tbl a’
Key=Dedcd
Return_value=’DESCR’’,’’DED_PRIORITY’
Where = $Where
End-Program
Key Points
1. The select paragraph starts with Begin-Select and ends with End-Select.
2. SQR commands can be placed in the Select paragraph anywhere between the column names and the From clause.
3. SQR commands in the Select paragraph must be indented.
4. select * not allowed in SQR
5. SQR commands in the Select paragraph are executed for each selected row.
6. SQR column variables are read-only
7. You can use an implicit Print command in the Select paragraph by placing the position qualifiers next to the selected columns.
8. There are many ways to select data from multiple tables. You can use SQL table joins, nested or hierarchical queries, or the Load-Lookup and Lookup commands.
9. Using the Lookup technique for long reports may significantly improve performance.
10. The Load-Lookup command can be used in the Setup section or in any procedure. It is used in conjunction with one or more lookup commands.
DML vs. DDL Statements
Data Manipulation Language: Insert, Update and Delete.
Data Definition Language: Create, Drop, Alter, Grant and Revoke.
Both DDL, DML statements could be used in the SQL paragraph of the setup section. Recommended to keep the DML and DDL statements separate since only DML statements are preprocessed by SQR in the compile stage.
In SQR programs, DDL statements are often used with temporary tables. Temporary tables are handy to hold the intermediate results. If we want to create temporary table, we may code the SQL paragraph in the setup section as follows:
Begin-Setup
Begin-Sql On-Error=warn
Drop table Temp_test_tbl
Create table Temp_test_tbl (emplid char(11), Name char(30))
End-Sql
End-Setup
Key Points
1. We can use native SQL statements in SQR with exception of the SQL Select statement.
2. It is a good idea to use the On-Error clause when updating tables.
3. SQR verifies SQL DML statements at the compile stage, but leaves SQL DDL statements unchecked.
4. The best place to create temporary tables is the Setup Section.
Key Points
1. SQR logical expression can be a combination of 3 major elements: operands, operators and functions.
2. There are 2 types of logical operators: Relational operators and Arithmetic and String Operators.
3. Parentheses in logical expressions help to override operator precedence.
4. Parentheses in complex logical expressions make our expressions more redable and easier to debug.
5. We can use any number of built-in function in a logical expression.
6. The result of a logical expression evaluation in the IF statement is either FALSE (Zero) or TRUE(One)
7. The Evaluate statement is useful when we have a large decision tree, and the decisions depend on a value of the same variables.
8. the Break Command is used to exit from the While or Evaluate Loops.
Print Command
The Print command places data on the page, we must specify the position and length of each output field with the help of 3 parameters (x,y,z)
X - specifies the line position of the page
Y - the column number
Z - the number of positions allocated for the field (length)
Print $Name (+1, 1, 20) ! prints $Name on the next line, position 1, width=20
Print $Name (+2, +3) ! prints $Name 2 lines below the current line starting from current column +3 and use
width = actual length of $Name
Print $Name () ! Print in the current line, current column,
Print $Name(0, 0, 0) ! Print in the current line, current column.
We can also use substitution variables defined in the beginning of program to code all printing parameters.
Formatting output
Print $Name (+1, 1) Bold !Prints the output in Bold
Print $Header (2, 5) Underline !Printer the output in Underlined
Print ‘Test Report’ () Center !Printes the text in Center of the page
Print ‘*’ (1, 1, 25) Fill ! Fills line with 25 Asterisks
Print $Comments (1, 1) wrap 15, 3
! This command prints the value of string variable Comments in 15 Characters per line for a maximum of 3 lines.
Print $Empl_Status (0, 16) Match
A 0 25 ‘Active’
T 0 25 ‘Terminated’
L 0 25 ‘On Leave’
If match is found in Empl_status field then the corresponding substituted value (‘Active’, ‘Terminated’, ‘On Leave’) will be printed in position 25 of the current line. In no match is found, an orginal employee status code will printer in position 16 of the current line.
Edit Masks
There are 3 types of edits
1. Text Edit
· X – Place a character in the output
Move ‘123456789’ to $Zip
Print $Zip (1,1) edit xxxxx-xxxx !output will be 12345-6789
· b – insert a blank in the output
Move ‘3456789’ to $Phone
Print $Phone (1,1) edit (xx)bxxx-xx output->(34) 567-89
· ~ -(tilde) skips a character in the output
Print ‘ABCDEFGH’ (1,1) edit xxxx~xx~ output->ABCDFG
2. Numeric Edit
Let #Unit_Price=10.459
Let #qty=10
Let #total=104.59
Let #credit=-50.00
Print #Unit_Price (1,1) edit 9999.99 output->10.46
Print #qty (1,1) edit 9999.99 output->10.00
Print #total (1,1) edit 9999.99 output->104.59
Print #credit (1,1) edit 9999.99 output->-50.00
Let #Unit_Price=10.459
Let #qty=10
Let #total=104.59
Let #credit=-50.00
Print #Unit_Price (1,1) edit $$$$.99 output->$10.46
Print #qty (1,1) edit 9999 output->10
Print #total (1,1) edit $$$$.99 output->$104.59
Print #credit (1,1) edit $$$$.99 output->$-50.00
3. Date Edit
Position Command
Position command will allow us to set the current position:
Position (10,2) ! sets the current position to line 10, column 2
Print #Amount() ! print amount in the current position
Position (+3,1) ! set the current position 3 lines down
Print #Total () ! Print total in the current position
Position (+1) ! move the print position to the next line
Controlling Vertical Spacing
We can use Next-Listing command to create detail groups in report and to control the vertical spacing between detail groups on a page.
Ex:
Begin-Procedure Select_EE
Begin-Select
Name (1,1,40)
Address1 (2,1,40)
City (3,1,15)
Next-Listing Skiplines=1 Need=3
From personal_data
Where State=’CA’
End-Select
End-Select
Here columns Name, Address1, City make up a detail group that is printed on 3 lines every time a row is selected.
The Next-Listing command moves the current position to 1 line below the current line in the page body, skipping 1 line and starting the next detail group on the next line.
The Need parameter controls the end of the page printing, directing SQR to begin a new page if there are less than 3 lines on the current page left, thus eliminating a potential situation in which a detail group can be broken between 2 different pages.
We can also use the Next-Listing command when we need to create variable length detail groups by using the Wrap qualifier.
Controlling Horizontal spacing
The Columns, Next-Column and Use-Column commands are used to control the horizontal spacing in reports. They define and navigate logical columns on the page.
Columns 10 20 30 !Define 3 Columns in report
This command defined 3 logical columns and made column 10 Current. Now if we use a Print command, it will shift the current column position from the left edge of the page and add 10 to the position qualifier in any Print Command.
Columns 10 20 30
Print $Name (3, 2, 30)
$Name will be printed in the 2nd position of the first logical column. What will be the actual SQR page position? The answer is: 11
In order to advance from 1 column to another, we use the Next-Column command.
Columns 10 20 30
Print $Name (3, 2, 30)
Next-Column
Print #Amount ()
The Next-Column command will move the current column position to the second declared logical column that is the column located at absolute column position 20. variable #Amount will be printed in the first position of the second logical column.
The Use-Column command sets a specific logical column as current or turns off column printing. To stop printing within logical columns simply specify 0(Zero) as a Use-Column parameter
Columns 20 50
Use-Column 2 !the columns command must be issued prior to Use-Column
Print $Last_Name() ! prints Last name in the second column i.e 50
Use-Column 0 !stops printing within columns.
Changing Reposts Heading/Footing:
Alter-Report is used to change a report’s heading or footing sections while the report is running. We can change the size of heading or footing or switch from one heading or footing to another dynamically.
Generating PDF files
SQR can generate Adobe PDF files. We don’t have to change our source program, all we have to do is to use the –PRINTER:PD SQR command line flag. The SQR.INI file has new section called [PDF Fonts] which provides the mapping between SQR fonts and Adobe Fonts. The name of the PDF file will be same as your report name with the .Pdf extension.
Another SQR command line flag, -EH_PDF creates a PDF icon in the navigation bar when we generate HTML output.
Key Points
1. The print command controls the position and the length of each output field with the help of 3 parameters; the 2 coordinates on the page grid (line and column) and the number of positions allocated for the field.
2. Line positions are always specified relative to the current page section (header, body, or footer), not to the entire report page.
3. The position and length parameters in the Print command can be numeric literals or variables.
4. In the Select Paragraph, we can implicitly print a table column by specifying the print position coordinates (x,y) and the space allocation Z to the right of the column name.
5. We can use substitution variables to define all printing parameters at the beginning of report.
6. The Print command format options help to enhance the appearance of report
7. There is 3 types of edit format masks: Text Edit, Numeric Edit, and Date Edit. Each of these 3 edit types uses its own set of special characters for mask coding.
8. Edit masks can be built and changed dynamically by assigning a mask value to a text variable, and then referencing this variable by its name prefixed by a colon.
9. The Position command will allow you to set the current position in the report page.
10. The Next-Listing command is handy when your report has groups of detail records.
11. The Columns, Next-Column, and Use-Column commands are used to control the horizontal spacing.
12. The SQR reserved variable #Current-Line always holds the actual line number within the page body.
13. The Print Delay command allows you to go back and change the value of an output variable before printing.
14. We can create PDF output without changing our program.
15. We can change reports heading or footing dynamically with the help of the Alter-Report Command.
On-Break option of the Print Command
When a value of a column is changed, it is common to do some special processing, we may need to skip lines, print value only if changed, execute a special procedure before or after the break, print sub-totals etc. SQR break handling commands allow we to do all of the above.
The On-Break option causes the specified action to take place when the value of a certain output field is changed. It can be used for both explicit and implicit printing. We can specify a number of qualifiers of the On-Break Option. These qualifiers define specific actions to be taken when the break occurs. The most popular qualifier is Print (not be confused with Print command). The Print qualifier is the default qualifier of the On-Break option; we don’t have to specify this qualifier when coding the On-Break option.
When using the Print qualifier of the On-Break option, we can specify when the break field should be printed:
Print=
Always
The break field will be printed for each detail group.
Change
The break field will be printed only when its values is changed. This is the default option.
Change/Top-Page
The break field will be printed when its value is changed plus at the top of each new page.
Never
The break field will not be printed.
In some cases, we may choose not to print a break column value when a break occurs.
Begin-Select
A.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
End-Select
Skiplines qualifier specifies how many lines to skip when the break occurs.
When dealing with nested On-Breaks, use the Level qualifier to ensure that the breaks are properly nested and to control the order in which break procedures are called (if any). The parameter in the Level qualifier specifies the level of the break for reports containing multiple breaks. When coding the value of this parameter, we should number breaks in the same order as that in the Order By clause.
Begin-Select
A.Company (,1,7) On-Break Level=1 Print=Change/Top-Page Skiplines=1
A.Paygroup (,+2,8) On-Break Level=2
End-Select
On-Break can only be used on String and Date Column and variables. We cannot explicitly use the On-Break option directly on a numeric column or variables. To fool the system, move the numeric column value to a string variable, then code the On-Break option in the Print command for this new string variable
Using procedures when Break occurs
When we use the On-Break option of the Print command, we can have SQR automatically call procedures before and after each break occurs. In order to accomplish this, use the Before and After qualifiers specify the names of the procedures to be called.
The Before qualifier will automatically call the specified procedure right before the column value is changed, including the processing of the first selected row.
The After qualifier will automatically call the specified procedure after the column value is changed including the last change, that is, when select is completed.
***If no rows are selected, neither procedure will be called.
Note: the Before and After qualifiers can be used only within Select Paragraph.
Begin-Select
A.Company (,1,7) On-Break Before=Company_Name After=Company_totals Skiplines=1
End-Select
Using the SAVE qualifier of the On-Break Option
Sometimes it is necessary to use or print the previous break value of a print variable in the After procedure. Since After procedures are executed after the values of the On-Break column have been changed, the columns will already hold their new values. The problem can be solved by using the SAVE qualifier of the On-break option. This method allows storing the previous column value in a specially designated variable when a break on this column value occurs.
Ex:
Begin-Select
A.Company (,1,7) On-Break Level=1 After=Company_totals Skiplines=1 Save=$Prev_comp
End-Select
Key Points
1. We can greatly enhance reports by using the On-Break option of the Print Command.
2. We can write special procedures to be invoked before and after column breaks. The Before and After qualifiers tell SQR when to call these procedures.
3. The Level qualifier of the On-Break option is used to arrange multiple breaks in hierarchy, and to specify the sequence of events.
4. Use the Skiplines qualifier to insert the necessary number of lines between detail groups.
5. When a break occurs, the previous column value is overridden with the new one. Use the Save qualifier to save a previously selected value to be used in the After procedure.
6. On-Break can only be used on String and Date Columns and variables.
7. In the beginning of a query, all Before procedures are process in ascending Level order when the first row of the query is fetched.
8. All After procedures are processed in descending Level order from the highest level to the current break level of the field where the break occurred.
9. All Before procedures are processed in ascending Level order from the current break level to the highest break level.
10. The Procedure qualifier can not be used in a combination with either Before or After qualifiers.
11. After the query execution (at End-Select) all After procedures are processed once more in descending Level order.
12. If a query returns no rows, neither Before nor After procedures are invoked.
SQR Run-Time & Compile-Time Variables:
There are 3 types of variables are used to make SQR reports flexible
1. SQR Bind Variables - Run-time variables
2. Substitution Variables - Compile-time variables
3. Dynamic Query Variables-
1. SQR Bind Variables/Run-time variables:
SQR Bind variables are used when a query includes parameters defined outside of the query. These parameters may come from user input, another query, or procedure.
SQR Bind variables are run-time variables. Their values are assigned and changed during the program execution stage. When we use a bind variable in an SQL statement, SQR ‘binds’ the variable before the SQL is executed.
Bind variables can also used to link 1 query to another. One typical situation occurs when we use nested queries.
2. Substitution Variables/Compile-time variables:
Substitution variables are used to specify SQR elements in several program locations at compile time. The values of these variables are set during the compilation stage and checked for syntax errors before the execution begins. They cannot be changed during the program execution.
The #Define statement and Ask Command present 2 ways to define substitution variables and to assign values to these variables.
Please note that we define these variables only once, but use them in more than 1 place in the program. When referenced in program, these variables must be enclosed in braces ‘{}’
SQR supports nested #Include statements; one include file may have a #Include statement referencing another include file, allowing up to 4 levels of nesting.
Another way to utilize substitution variables is Ask Command. The Ask command always coded in the Setup Section prompts the user for the value, which is placed into a substitution variable. With Input command, we can validate user input and re-prompt if it is not valid, something we cannot do with the Ask command.
The Ask command should not be confused with the Input Command, another user dialog command. The Input command is processed at execution time; whereas the Ask command is process at compile time.
The Ask command is used only in Setup Section. Ask commands have to be issued prior to any use of their substitution variables.
3. Dynamic Query variables/Dynamic SQL variables:
Dynamic query variables are used to build dynamic SQL Code. Sometimes called Dynamic SQL variables, or simply Dynamic variables, dynamic query variables are text variables whose values are used as parts of SQL statements. When referenced in SQL statements their names must be enclosed in square brackets ex:[$by]
We can use dynamic query variables to substitute columns and other parts of SQL statements or to dynamically change the Where clause or an entire SQL Statement. The important thing to remember is that when we use dynamic variables in our SQL Statement, SQR cannot check syntax at compile time as it usually does. Run-time errors will occur if a dynamic variable or the Where clause syntax are incorrect.
Key Points
1. The 3 special types of variables that increase flexibility of SQR programs are: Bind Variables, Substitution Variables, Dynamic Query variables.
2. Bind variables and Dynamic query variables are Run-Time variables.
3. Substitution variables are Compile-time variables: their values can’t be changed at runtime.
4. Bind variables are used in SQL statements in the SQL or Select Paragraphs.
5. Bind variables can be used in correlated Select statements to link one query to another.
6. Substitution variables can be used to alter any part of SQR programs.
7. Substitution variable values are set at compile time by either the #Define compile directive or the Ask Command.
8. The Ask command can be used only in the Setup section.
9. When referenced in a program, substitution variable names must be enclosed into braces ex: {Subst_var}
10. A change to just one substitution variable may cause multiple changes in the program.
11. Dynamic query variables are variables whose values are used as parts of SQL Statements.
12. When referencing dynamic query variables we should enclose their names in square brackets ex:[dynamic_var]
13. Dynamic query variables can be used to substitute any part of an SQL statement.
14. When dynamic query variables are used to generate SQL statements, SQR cannot check the SQL syntax at compile time.
15. When using dynamic query variables to generate SQL statements, it is very important to code error-handling routines.
SQR Arrays
An Array is a collection of similar groups of fields in the program memory, wherein its relative position number in the array can refer to each group.
An SQR Array is a memory structure that consists of rows and fields. When no arrays are used, in most cases, an SQR program processes its input records one by one. After a record is processed, it is overlaid with the next record, making the first record’s contents inaccessible. Because it is sometimes necessary to process all or some records at once, SQR Arrays can serve as buffers where the data can be temporarily stored. Often times, it is not only a convenient method of temporarily storing information, but also a good way of developing effective program.
Arrays are temporary structures created in the program memory. SQR has a specific set of commands used to create and populate arrays and to access information stored in arrays.
· Create-Array (to create arrays)
· Clear-Array (Initializing arrays)
· Get (retrieve data from arrays)
· Put (place data into arrays)
· Array-Add (
· Array-Subtract
· Array-Multiply
· Array-Divide
· Create-Array
In SQR arrays are created during the compile stage, before the program is executed. The Create-Array command is used to create array.
Create-Array Name=Dependents Size=25
Field=Dependent_Id:Char
Field=Dependent_Name:Char
Field=Dependent_Birthdate:Date
Field=Replationship:Char
In the example we have created an array named Dependents with a size of 25 Rows. The size in the array command specifies the max number of rows in the array. If any array subscript value exceeds the maximum array size, SQR aborts the program execution and displays the following message:
(SQR 1500) Array element out of range (25) for array ‘Dependents’ on line 28
SQRW: Program Aborting.
All SQR array are create before the program is executed. While the Create-Array command can be used in any section of the program, it is a good practice to place in a Setup-Section. The Create-Array command just allocates and initializes memory for an array.
When array is created its fields are automatically initiated to their default values based on the following rules
All Numeric fields are set to ZERO.
All String fields are set to NULL.
All Date fields are set to NULL.
Multiple-Occurrence fields:
Create-Array Name=Clients Szie=100
Field=Name:Char
Field=Phone:Char:3
In the above array each row includes the Name field and the 3-occurrence field Phone. Once the array is dynamically created, specifying the field name can reference any array field, the field row number starting from number Zero. In Clients array, the rows are numbered from 0-99, and the field Phone occurrences are numbered from 0-2.
· Clear-Array
When an array is created all its field values are automatically initialized. Sometimes we need to re-initialize an array after some or all of its fields have been populated. It is a good practice to always reinitialize arras after its use. The Clear-Array command resets each field of an array to its initial value. Field initial values are specified in the Create-Array command. If no initials value was specified for a field, the field is et t its default value.
Clear-Array Name=Dependents
· Get
The Get command is used to retrieve data from arrays. We can retrieve the entire array’s fields at once using the one Get command.
Get $Dep_Id from Dependents(10)
Get $Dep_Id $Dep_Name from Dependents(#i)
Get $Client_Name $Client_Phone from Clients(10) Name Phone(#j)
· Put
The Put command is used to placing data into arrays. There are 2 methods of placing data into an array. We can reference the array elements in the Let command or by using the Put command.
Let Dependents.Dependent_Id(10) = ‘01’
Let Dependents.Dependent_Id(#Row_Num) = &A.Dependent_Benef
Let Clients.Phone(#i,#j) = &A.Phone
Put ‘01’ into Dependents(5)
Put ‘01’ ‘Smith,Maria’ into Dependents(#i)
Put &Name $Phone into Clients(#Row_Num) Name Phone(#sub_Num)
We can perform addition, subtraction, division and multiplication operations on elements of an array by using the Let command or of the following array specific commands.
Let Employees.Salary(10) = Employees.Salary(10) + #Bonus
· Array-Add
Array-Add #count to Clients(#i) Count
· Array-Subtract
Array-Subtract 1 from Books(500) Books_received
· Array-Multiply
Array-Multiply #Incr1 #Incr2 times Empl(1) Rate(0) Rate(1)
· Array-Divide
Array-Divide 5 into Empl(#m)
Searching Data in Arrays
SQR does not have special commands to carry out array searches; we have to program this logic. There are many popular algorithms that can be used to search in arrays. Some of them are very simple to implement, while others may require some effort.
Here we will discuss 2 algorithms: Sequential search and Binary search.
Sequential Search Algorithm: when looking for an element with a particular value in a certain field, our program has to go over every single array element until it hits the right one. Not a very efficient method of searching, but very simple to program.
Binary Search Algorithm: it is more efficient than the sequential search, but it has very important limitations; it works only with pre-sorted arrays. The main idea of the binary search algorithm is very simple; it compares the target value to the value of the search field of the middle element of the arrays current search area. The search starts when the current search area I equal to the entire array. The search fields in the array are ordered in a predefined sequence, therefore, if the target value is less than the value in middle element, the search focuses on the lower half of the array, otherwise the search concentrates on the upper half. The remaining half of the array becomes the current search are. The next step is to compare the target value to the middle element of the current search area. This process of dividing the current search is in 2 continues until the element is found or the current search area is empty.
Key Points
1. The Create-Array command creates an array of fields in the memory.
2. The Create-Array command can be used in any section of SQR program.
3. We can specify an initial value for each field in an array.
4. Some fields in an array can be defined as multiple-occurrence fields.
5. Row number and field occurrence numbers in arrays start from Zero.
6. Row numbers and field occurrence numbers can be numeric literals or numeric variables.
7. When using variables to refer to row numbers or occurrence numbers, be sure to keep the variable values within the boundaries defined in the Create-Array command.
8. All SQR arrays are created during the program compile stage
9. All fields used in arithmetic operations must be declared as number, decimal, float or integer
10. The Get command is used to retrieve one or more fields from an array.
11. The Put command is used to move one or more fields into an array.
12. The Clear-Array command is used to initialize an array.
13. The following commands can be used when working with arrays:
Create-Array
Clear-Array
Get
Put
Array-Add
Array-Subtract
Array-Multiply
Array-Divide
Let
Defining Multiple Reports in SQR
In a multiple-report SQR program, each report must be defined with a separates Declare-Report command, this command is coded in the Setup Section.
In the Declare-Report command, specify the following attributes
· Name of the report
· Name of the Layout to be used for the report
· Type of the printer to be used for the report
Depending on the specific business requirements, each report can use its own Layout or printer definition, or some reports can share same Layout or Printer definitions. If no Printer-Type is specified, the default is LINEPRINTER for the report. If no Layout is specified in the Declare-Report command, the Default Layout is used.
Declare-Report My_Report1
Layout=My_Layout1
Printer-Type=PS
End-Declare
Valid values for the Printer-Type argument in the Declare-Report command are HTML(HT), HPLASERJET(HP), POSTSCRIPT(PS) and LINEPRINTER(LP).
The Declare-Layout command is also coded in the Setup Section. This command allows specifying the attributes of the layout for our output file and defining as many layouts as we need for our application.
If we don’t code the Declare-Layout in our SQR program, a default layout with the name Default will be created. If we need to override its attributes, we can define a layout called Default in our program and list only the attributes we need to change. If we are defining multiple layouts in our program, each layout name must be unique.
Begin-Setup
Declare-Layout Layout1
Left-margin=1
Right-margin=1
End-Declare
Declare-Layout Layout2
Left-margin=5
Right-margin=5
End-Declare
Declare-Report Rpt1
Layout=Layout1
Printer-Type=HP
End-Declare
Declare-Report Rpt2
Layout=Layout2
Printer-Type=PS
End-Declare
End-Setup
If we need to override some of the default printer attributes, use the Declare-Printer command. This command is also coded in setup section.
The Declare-Printer command allows us to create our own customized printer type and assign it a name. In order to make a Declare-Printer command applicable to a specific report, we have to use For-Reports parameter with a specific report name defined in a Declare-Report Command. If the For-Reports parameter is not coded, the printer type attributes specified in the Declare-Printer command will apply to all reports that use the printer type specified in the Type argument of this Declare-printer command.
Begin-Setup
Declare-Printer HP1
For-Reports = Report1
Type=HP
Font=4
End-Declare
Declare-Printer PS1
For-Reports = Report2
Type=PS
Font=5
End-Declare
End-Setup
We can specify the printer type in the SQR command line –Printer:xx flag. this will cause the SQR program to produce all our report output files for the specified printer type. For ex: -Pinter:HP to produce all output for HP LasetJet printer.
Handling Multiple-Reports in one Program
If our program generates multiple reports, the Print commands need to know which report to direct their output to. The Use-Report command will allow our program to switch between reports. When this command is used, all Print commands issued after the Use-Report command will write the output to the specified report file, until the next Use-Report command is issued. See the following ex
Use-Report Empl_detail
Print ‘Emplid’ (1,1)
Use-Report Empl_Summary
Print ‘Total Employees:’ (+1,1)
Print #Total_recs (,1)
The headers and footers are printed using a different approach. The same header or footer can be shared by multiple reports. In real business life, however, different reports usually have different information in their headers and, sometimes, footers. In these cases, we should code separate Heading and footing section for each report and use the For-Report parameter to assign each section to a specific report, for example
Begin-Heading 4 For-Reports=(Empl_Detail)
Print ‘ Employee Detail Report ‘ (1) Center
Print ‘….’
End-Heading
Begin-Heading 2 For-Reports=(Empl_Summary)
Print ‘ Employee Summary Report ‘ (1) Center
Print ‘….’
End-Heading
Begin-Footing 1 For-Reports=(Empl_Detail)
Print ‘ End of Employee Detail Report ‘ (1) Center
End-Footing
Output Files in SQR Program with Multiple-Reports
If we use 2 multiple reports in our SQR program, it will generate 2 report files; for example if the file name is Test1a.sqr then the output report files will be Test1a.lis and Test1a.l01. The Test1a.lis will be assigned to the first Declare-Report command, and the Test1a.l01 will be assigned to the next report declared by the Declare-Report Command.
By default SQR creates output report files with the same names as our program name, but with different extensions. For single report programs, the extension is usually .lis or .spf.
For multiple reports, the report file names are still the same as the program name, but the file extensions will be .lis, l01, l02 and so on. In case of .spf output files, the names will be same as the program name with the extensions: .spf, .s01, .s02 and so on.
If the Environment variable OUTPUT-FILE-MODE=LONG in the Default settings of the SQR.Ini file, the report file names will be like
Test1a.spf, Test1a_01.spf, Test1a_02.spf
Test1a.lis, Test1a_01.lis, Test1a_02.lis.
By default the OUTPUT-FILE-MODE=SHORT.
We can use the –F flag in the SQR command line to override the default file names with our own file output names. For example, if we want the Test1a.sqr program to generate the Empl.lis and Depd.lis report files, the following –F flags:
-FEmpl.lis –Fdepd.lis
Similarly, we can use the –F flag to create multiple .spf files. Keep in mind, however, that we must still specify the .lis extensions for these reports. SQR will automatically rename the files to Empl.spf and Depd.s01.
Key Points
1. In multiple report programs, each report must be defined with a separate Declare-Report command.
2. The Declare-Report and Declare-Layout commands must be coded in the Setup Section.
3. Each report can use its own layout or printer type definitions; some reports can share the same layout and type definitions. If no layout is specified, the Default layout is used.
4. If we define multiple layouts in our program, each layout name must be unique.
5. The Use-Report command allows our program to switch between reports.
6. SQR allows us to specify the Heading and Footing sections for each report using the For-Report argument in the Begin-Heading and Begin-Footing commands.
7. When creating multiple output files, the default names of the report files will be the program name with the extensions .lis, l01, l02 and so on. In case of the SPF output files, the file extension will be: .spf, .s01, .s02 and so on.
Creating SQR Portable Files: SPF Files
SQR allows creating SQR Portable File (SPF). The main idea of SPF file is that, with their help, our program can create printer-independent output files. These files can be viewed and printed by SQR Viewer, or converted back to LIS files with the help of SQR Printer. End users can use SPF files to view, e-mail, fax, or print them on any available printer device.
SPF output files can be produced by SQR on any platform. We don’t have to change our program to make if generate SPF output. All we need to do is to use one of the following flags in the SQR command line or the SQR Dialog Box:
· -NOLIS will create SPF file output instead of LIS output file.
· -KEEP will allow creating SPF output in addition to LIS output.
· -ZIV will invoke the SQR viewer after creating an SPF file. This flag automatically invokes the –KEEP flag to create an SPF file. If a program creates multiple output files, the SQR viewer will automatically display only the first report file. The other SPF files can be viewed or printed by using the SQR Viewers file selection option.
SQR generates SPF output even when it is not asked to do so. When SQR needs to accumulate its output before creating the final reports it uses SPF files as intermediate output file storage. Another example occurs when the Last-Page command is used in a program, this command causes SQR to accumulate the entire report without printing it until the last page is processed. In these and other cases, SQR automatically creates SPF files and deletes them upon the programs completion.
SPF files allows generating a report and storing the produced SPF output for subsequent electronic distribution or on-site printing. We do not have to know the type of printer belonging to each potential recipient.
SPF files also permit to view, search, or email the text portions of a graphic output by rendering a line-printer output. We can run a report on a UNIX server and then print the output on a PC using Windows.
Using SQR Viewer
The SQR Viewer allows viewing our report online before printing. We can also take advantage of the Viewer’s Print Preview, Print Setup, Find and Zoom options as well as the capability to email the report to users.
To view a report thought the SQR Viewer, the report must be in the SPF format. These files normally have .SPF or .SNN extensions.
We use the –NOLIS command line flag to create SPF output. This option will make the program generate: Test1a.spf and Test1a.s01
(If the OUTPUT-FILE-MODE environmental variable in the SQR.ini file is set to LONG, the SPF file names will be Test1a.spf and Test1a_01.spf)
Another convenient way of using the SQR Viewer is to run the program with –ZIV command line flag. When we use this flag, SQR creates SPF output, automatically invokes the SQR Viewer, and opens the first SPF file created in the program.
It is important to keep in mind that while SPF files can be created on any platform, the SQR Viewer can be used only on Windows. What if our program ran on a non-windows platform, but users need to view the SPF file generated by the program? The simplest way to solve the problem is to use a file transfer tool (such as FTP) to transfer the files to the windows environment.
Key Points
1. SQR portable files (SPF files) are printer-independent and offer a higher degree of flexibility for both program developers and end users.
2. We don’t have to change our program to make it generate SPF files; it can be achieved by using the proper SQR command line flags.
3. SPF files can be generated on any platform.
4. The SQR viewer allows to view, print and email SPF files. This product works in the Windows environment only.
5. The SQR Print converts SPF files to printer-specific files. This is a multiple-platform product.
Document Markers
Another method of placing the values of variables or columns into Document involves using document markers. A document marker is a special variable whose name begins with @. It marks a location in the document where we place data from areas external to the Document Paragraph parts of the program. Document markers defined in Document Paragraph can be referenced in the Position command outside the document paragraph to establish the next printing position. (SQR command cannot be used inside Document paragraph.)
Begin-Program
Do Print_Doc
Do Print_Bold
End-Program
Begin-Procedure Print_Doc
Begin-Document (1,1)
Dear Colleague:
.b
@Start_Bold
.b
@Start_Underline
.b
End-Document
End-Procedure
Begin-Procedure Print_Bold
Position () @Start_Bold
Print ‘ Hai this is Bold text…’ () Bold
Position () @Start_Underline
Print ‘ This is underline text…’ () Underline
End-Procedure
Key Points
1. Use the Document paragraph to create form Letters.
2. The Begin-Document and End-Document commands are used to define Document paragraph.
3. The Document Paragraph may be used inside any procedure. There may be multiple Document paragraphs within a program.
4. Do not use tabs inside a Document paragraph. Use spaces instead. For a full line spaces specify .b
5. SQR commands are not allowed within the Document paragraph.
6. A printed Document paragraph must fit into a single report page.
7. We can use document markers to designate special locations in the Document paragraph where we can place data from external to the paragraph parts of the program. The Position and Print commands are used to move data to the Document paragraph from outside the paragraph.
8. We must generate a document before placing information at the locations marked by the document markers.
9. We can separate our letter into variable length sections to achieve greater flexibility.
Working with Flat Files
Files in SQR: Regular sequential files are often called Flat Files. SQR supports all necessary input/output operation on flat files, including opening and closing files and reading from and writing to the files. Please note that SQR does not provide random access input/output commands; all records are processed sequentially.
We can use sequential/Flat Files to
· Import data from other systems into our DB tables.
· Unload data from our DB tables into Flat Files, and use the files as interfaces to external systems.
· Sort our data using the operating system commands or utilities.
Flat files created in SQR are frequently used as Interfaces between PeopleSoft applications and other Systems. They are also used for generating comma-delimited files compatible with Excel Spreadsheet format.
Opening a File in SQR
Before accessing a file in SQR, we have to open it. A file can be opened as an input file for reading from an existing file, or an output file for writing to a new file or appending data to an existing file.
The maximum number of open files at any time is 256. If our program includes more than 256, close the files we do not need at that moment.
When a file is opened for reading, the record length specified in the Open Command must be equal to or greater than the length of the longest record used in the file. The maximum record length is (32K –1) bytes (32767)
When a file is opened for writing, a new file is created unless the For-Append parameter is specified. If the same file already exists and no append mode is specified, the existing file will be overwritten.
Open ‘My_file1’ as 1 For-Reading Record=80:Vary
Open ‘c:\temp\My_file2’ as 2 For-Reading Record=80:Fixed
Open $File3 as 3 For-Reading
Open $File2 as #k For-Writing Record=100
Open $File3 as 4 For-Append Record=70
Open &A.File_Name as 5 For-Reading Record=120:Fixed_Nolf
Let $File4=’C:\temp\abc.dat’
Open $File4 For-Reading Record=120:Vary Status=#File-Status
The number for file handling is a positive integer from 1 to 64,000.
Vary : should not contain binary data, record length does not include the line terminator character. It is applied by default if no type is specified.
Fixed : all the records should have the fixed length.
Fixed_Nolf : indicates with no line terminator at the end of the record. This is useful when writing or reading binary data.
Status : if status is not Zero, it means that file was not found
Closing a File in SQR
SQR files stay opened until the Close Command is used or program successfully terminates.
Close 1
Close #k
The close command uses only one argument, the file number, which is the number assigned to the file in the corresponding Open Command.
Reading from a File
SQR will allow reading an input files only if the file was opened For-Reading. The Read command reads the next record from the file into the variable or variables specified in the command.
Read Into:
Read 1 Into $Input_Record:80
In this example, it reads the whole record into a single variable $Input_Rcord. This gives us flexibility and, with the help of string functions, we can easily extract the needed fields, or parse the record by a field separator. Here are some parsing examples:
Unstring $Input_Record By ‘,’ Into $Last_Name $First_Name $Address
Extract $First_Name from $Input_Record 0 20
Let $Last_Name=Substr($Input_Record,1,20)
Read 1 Into $Last_Name:20 $First_Name:10 $Address:50
Detecting End of File:
SQR internal variable #End-File is set to 1 when there are no more records to read. We should check this condition after each record is read.
Begin-Procedure Read-File
While 1
Read 1 Into $Record:80
If #End-File=1
Break
End-If
End-While
End-Procedure
Verifying Read Status:
SQR returns Zero if the read is successful, otherwise a system-dependent error number is returned.
Begin-Procedure Read-File
While Not #End-File
Read 1 Into $Record:80 Status=#Read_Stat
If #End-File
Break
End-If
End-While
End-Procedure
Reading Text Data
When reading text data we should specify the variable name and number of bytes we want the program to read. Keep in mind that the trailing blanks are omitted when the record is read.
The total length specified for all our read variables must be less than or equal to the length of the entire record read.
Reading Binary Data
In order to read binary data, the file must be opened as a file with record type equal to Fixed or Fixed_Nolf. Binary fields can be 1, 2 or 4 bytes in length. When reading binary numbers, they must be placed into numeric variables. Binary numbers hold only integers. If we need to maintain a decimal portion of a number, convert the number to a string variable.
The following is the example for reading binary numbers.
Read 1 Into #Amount:2 #Hours:1
Reading Date Fields
The date field was written to a file in the SQR date variable format, we could read this field to either a date or string variable. The date variable must be in one of the following formats:
The format specified by the SQR_DB_DATE_FORMAT environment variable.
DB specific format
DB independent format ‘SYYYYMMDD[HH24[SS[NNNNNN]]]]’.
Writing to a File:
SQR will allow writing a record to a file only if the file was opened For-Writing or For-Append. The Write Command writes a record to a file from the variable or variables specified in this command.
When using SQR Write command, we can write to a file from a single literal, variable, or column or from a list of literals, variables, or columns. The command operands are very similar to those of the Read Command. We have to specify the file number, the source fields, the length of the source fields, and a variable to hold the Write command status. Only the file number and source field names are required parameters. The source field and the status variable are optional. See the following example;
Write 1 From $Record:80 Status=#Write_Status
Write #H From $Record_2
Write 2 From $Lst_Name:20 ‘,’ $First_Name:10 ‘,’ $Address:50
Write 3 From #Amount:2 #Number:1
Write 4 From $Date:18
Different techniques for creating Flat File output
One of the most popular uses of flat files is downloading information from a database into a flat file. The file created can later be used by other applications outside the SQR environment. We can use one of the 2 major approaches to accomplish this task:
1. The Write Command
2. The Print and New-Page command
Using the Write command is a more traditional way of creating flat files. It is also a more flexible method. It involves the following steps
1. Open a file For-Output or For-Append
2. Select the required data from DB tables in the Begin-Select Paragraph
3. Perform necessary data conversions and manipulations.
4. Use the Write command to place data into the file.
5. Repeat steps 2, 3 and 4 until the end of selection.
6. Close the output file.
Using Print Command to Create a Flat File
Using the Write command to create an output file is traditional way of creating Flat File. Many programming languages use very similar approaches. SQR allows us to employ another technique of writing data to an output file: using the Print Command. Keep in mind, however, that this method is not as flexible as using the Write command, it has a number of limitations
To use Print command to output data to a file:
1. Specify Max-Lines=1 in the Declare-Layout Command.
2. Define the Width in the Declare-Layout command equal to record size (the size of our output record)
3. Specify Formfeed=No in the Declare-Layout command to make sure that SQR will not output form-feed characters.
4. Use explicit length in our Print Command, thus placing each field at a fixed location in the record.
5. Use the New-Page command after each record is processed.
Using Flat Files to import data into DataBase
Another frequent task involves uploading data from a flat file into a Database. We can develop a simple program that reads the file and inserts the information into our database. Some business needs require the designing of more complex programs that not only read and insert, but also verify the information read from the file, and populate multiple tables at once. Our SQR program can do many things, including the following
· Read records from the input file
· Edit input fields using SQR functions, table lookups and queries
· Build logic to insert data into different tables based on the specified criteria
· Check for duplicates
· Print a audit report
Example of interface:
!*************************************
Begin-Setup
!*************************************
Begin-SQL On-Error=Stop
Create Table Temp_Job_Tbl
(
Emplid Varchar2 (11) Not Null,
Empl_Rcd Number Not Null,
Effdt Date Not Null,
Effseq Number Not Null,
Deptid Varchar2 (6) Not Null,
Jobcode Varchar2 (6) Not Null,
Empl_Status Varchar2 (1) Not Null,
Annual_Rt Number (18,3) Not Null
Monthly_Rt Number (18,3) Not Null,
Hourly_Rt Number (18,6) Not Null
)
End-SQL
End-Setup
!*************************************
Begin-Program
!**************************************
Open ‘C:\temp\job.dat’ as 1 For-Reading Record=3000 Status=#File-Status
If #File-Status !=0
Show ‘Error opening input file’
Else
Do Read_Input_File
End-If
End-Program
!*************************************
Begin-Procedure Read_Input_File
!*************************************
Display ‘ inserting records from file’
Move 0 to #Inserts
Move 0 to #Tot-Recs
While Not #End-File
Read 1 Into $Input:1000
If #End-File
Break
End-If
Unstring $Input By ‘ ‘ Into
$Emplid –
$Empl_Rcd –
$Effdt –
$Effseq –
$Deptid –
$Deptid –
$Jobcode –
$Empl_Status –
$Annual_Rt –
$Monthly_Rt –
$Hourly_Rt
Do Insert_Temp_Job
End-While
Close 1
Display ‘ Total records inserted:’
Display #Tot-Recs 999,999,999
End-Procedure
!*************************************
Begin-Procedure Insert_Temp_Job
!*************************************
Begin-SQL On-Error=Insert_Error
Insert Into Temp_Job_Tbl
(
Emplid,
Empl_Rcd,
Effdt,
Effseq,
Deptid,
Deptid,
Jobcode,
Empl_Status,
Annual_Rt,
Monthly_Rt,
Hourly_Rt)
Values
(
$Emplid
$Empl_Rcd
$Effdt
$Effseq
$Deptid
$Deptid
$Jobcode
$Empl_Status
$Annual_Rt
$Monthly_Rt
$Hourly_Rt )
End-SQL
If #sql-count=1
Add 1 to #Inserts
If #Inserts >= 500
Commit
Move 0 to #Inserts
End-If
Add 1 to #Tot_Recs
End-If
End-Procedure
!*************************************
Begin-Procedure Insert_Error
!*************************************
If #sql-status = -9
Show ‘ insert error: duplicate row is not allowed for emplid=’ $Emplid
Else
Show ‘Insert Error:’ $sql-error
Show ‘Error No: ‘ #sql-status
Stop
End-If
End-Procedure
Key Points
1. SQR provides the following Input/Output commands: Open, Read, Write and Close.
2. Before accessing a file, it must be opened. If we are reading a file it should be opened For-Reading. If we are writing to a file, it should be opened For-Writing.
3. The open For-Append parameter is used when we need to append the records to an existing file. If a file does not exist, it will be created.
4. The record type may be defined as Fixed, Vary, or Fixed_Nolf(fixed with no line terminator). The Fixed_Nolf type is often used for accessing binary data.
5. When reading from or writing to a file, we refer to the file by the file number rather than by the file name. File names are specified only in the Open command.
6. SQR internal variables #End-File is set to 1 when there are no more records to read.
7. When reading text data, the trailing blanks are omitted.
8. In order to read binary data, the file must be opened as Fixed or Fixed_Nolf.
9. We cannot read or write NULL values from or to an ASCII file
10. The Print and New-Page commands can be used to create Flat Files with fixed length record.
11. We can use the operating system commands to sort files.
12. Files are closed either by Close command, or upon successful termination of our program.
SQR Program Execution
SQR program can be invoked in a number of ways.
We can run an SQR program from the
1. SQR Dialog Box in the windows environment.
2. Command Line
3. PeopleSoft Process-Scheduler
Executing SQR Program from the Command Line
When executing an SQR program from the operating system Command Line, we need to enter
· The name of the SQR engine (SQRW in Windows, SQR in Unix, etc)
· Our program name
· The database connectivity string
· SQR command line flags
· Application specific arguments
Since .sqr is the default extension for all SQR source programs, we don’t have to specify it, unless our program has another extension.
The database connectivity string contains important information needed to connect our program to the DB, including User Id and Password. The exact format of this string is database-specific.
Sqrw test01.sqr sqrbook/passwd –Fc:\temp\ -windows
Sqrw test01.sqr sqrbook/passwd –Fc:\temp\mysqr.lis -Windows
Sqr test01.sqr sqrbook/passwd –F/home/output -Unix
Sqrw test18a.sqr test/pswrd @arg1.dat !argument file is arg1.dat
Executing Pre-Compiled SQR Program/.SQT Program
In order to compile an SQR program without executing, use the –RS SQR command line flag which allows us to create a pre-compiled version of our program. Pre-compiled programs are usually assigned the extension of .SQT and are sometimes called SQT programs.
After an SQT program is created, we can execute it at any time with the help of either the –RT SQR command line flag or via the SQR Execute (SQRWT for windows, SQRT for Unix) program. The SQR Execute program performs only program execution, it allows us to specify most command line flags similar to the flags we specify when executing our program in 2 stages.
The following example shows how to pre-compile and then execute and SQR program.
Sqrw test01.sqr sqrbook/passwd –RS !compile test01.sqr
Sqrw test01.sqt sqrbook/passwd –RT –Fc:\temp\ !execute testo1.sqt
All the commands in Setup section are processed at compile time. This means that when a pre-compiled program is executed, all Ask commands will be ignored. We will have to use Input command, instead of Ask command to prompt for user input at run time.
Executing SQR Programs in Batch Mode
SQR programs can be executed in batch mode under MS-DOS, Windows, UNIX or MVS. For example an SQR command line can be included into a batch file or a Unix shell Script. PeopleSoft users are, probably, familiar with a special script name PRCS_SQR. This script wad developed by PeopleSoft to run PeopleSoft SQR programs in the Unix Environment. It takes all user parameters from PeopleSoft on-line panels with the help of the PeopleSoft Process Scheduler, and dynamically builds the regular SQR command line.
********* The Call System SQR command is the Operating system command.
Call System Using ‘cp /usr/tmp/file1.dat /usr/tmp/file2.dat’ #Status
Key Points
1. We can execute an SQR program from a Command Line, start this program from the SQRW Dialog Box in the windows environment, or call the program from other program.
2. SQR allows us to changes its default behavior by using its command line flags. SQR command line flags always start with a dash (-). Some flags may have additional arguments. In this case, the arguments must follow their respective flags immediately with no intervening spaces.
3. When flag arguments specify file directories, they must be ended with the operating system-specific directory character. For windows, it is a backslash ‘\’; for unix, a forward slash ‘/’.
4. SQR command line arguments are the values that your SQR program expects to receive from the command line via the Ask command at compile time or the Input command at run time.
5. We can use an argument file to pass arguments to our program on the command line. Each argument must be entered in the argument file on a separate line.
6. SQR processes the program in 2 stages. The compilation stage and the execution stage. We can use –RS command flag to create a pre-compiled version of program with .SQT extension. We can execute the .SQT file by specifying –RT command flag or using the SQR Execute.
7. We can issue any operating system command within SQR program. The Call System Using command is used to execute the operating system commands.
8. In addition to issuing the operating system commands, the Call System Using command can be used to invoke any executable program.
9. In order to call SQR program from other applications, we can use the SQRAPI
10. SQR programs can be scheduled fro execution from PeopleCode.
Key Points
1. We can generate program reports in an HTML compatible format and publish these reports on the Internet.
2. There are 3 main methods to generate HTML from an SQR program output.
3. We always have to use the –PRINTER:HT, -PRINTER:EH command line parameters to have our program generate HTML output.
4. All SQR HTML procedures are delivered in the Html.inc file.
5. Executing the Html_On procedure turns on all SQR HTML procedures, but causes SQR to ignore all print position qualifiers.
6. The SQR HTML table procedures can be used to control the report-element positioning.
7. The SQR HTML hypertext link procedure allows us to specify links to other parts of the current web page or to an external web page. Alternatively, we can use these procedures to define anchors within our report so that the same or other web pages can have links to these anchors.
8. Our program can use the regular SQR Input command to accept input parameters from another HTML Script.
9. If we run an SQR program without the -PRINTER:HT, -PRINTER:EH command flag, SQR will ignore all SQR HTML procedures in the program and will generate regular LIS files as the program’s output.
Debugging Techniques
Show and Display commands
When it comes to debugging an SQR program, there are no fancy step-through debuggers. The main weapons are 2 SQR commands: Show and Display. We can use these commands anywhere in our program except the Setup Section. Both the Show and Display commands display the specified texts and variables on the screen and also write the same information into the program log file.
If for example, we ran and completed a program successfully, but output is not what we expected, then we need to find out whether our program had some logic flaws, or whether the unexpected results have been caused by an incorrect selection in the programs query. The simplest way to find this out is to add some Show or Display commands to the program.
Show ‘Select started’
Begin-Select
A.Emplid
A.Name
Show ‘selected emplid=’ &a.emplid ‘Name=’ &A.Name
From Personal_data
Where name like ‘Abr%’
End-Select
In the above example, each selected record is displayed on the screen and written to the program log file.
The Display command is somewhat similar to the Show command, but it has less power; we cannot display multiple fields in one Display command.
Display ‘Selected Emplid = ‘ Noline
Display &A.Emplid
The #Debug Command
We can optionally use the #Debug conditional compiler directive by executing the Display, Show, Print or other commands in our program during testing, and then deactivating them when the program is released to production. This approach is called Debugging Encapsulation.
Any SQR command that immediately follows the #Debug statement will be compiled only when the corresponding –Debug flag is specified in the SQR command line. The #Debug command may be appended with one or more letters or numbers: ex #Debugxy, to provide additional flexibility in debugging. We may optionally specify up to 10 different suffix letters or numbers.
#Debug Show ‘Start proce1’
#Debugx Show ‘Start proc2’
#Debugy Display ‘start proc3’
#Debugz Display ‘Start proc4’
#Ifdef or #Ifndef / #End-If
#If / #Else / #End-If
Key Points
1. The Display and Show commands are the main instruments in debugging SQR programs.
2. The –DEBUG SQR command line flag in combination with the #Debug, #Ifdef, and #Ifndef statements, can help to encapsulate the debugging logic in your program.
3. The –S, -T, -C and –E SQR command line flags can provide additional help during the debugging process.
Local Procedures Vs. Global Procedures
All procedures in SQR are considered global by default. In order to make a procedure local, it must have parameters or must be explicitly declared local. Variables that appear in local procedures are not available outside of their respective procedures. This way, the same variable name can be used in different local procedures without creating an error situation.
Local procedures can communicate with other local and global procedures via parameters. Since global variables defined outside of a local procedure can be accessed only by using special naming convention there is no risk that some variable values can be accidentally overridden.
Key Points
1. To support global development:
· Avoid hard-coding date, currency and numeric edit format masks: use SQR special keywords Date, Money and Number instead.
· Use SQR locales.
2. To ensure our program platform independence:
· Do not use fully qualified hard-coded file names
· Use the $sqr-platform predefined variable to determine the current platform
· Avoid calling operating system commands from SQR programs; use similar SQR functions instead.
· Test your programs on both client and server
3. These rules will help to write database independent programs
· Do not use database-specific function, use similar SQR function instead.
· Avoid using built-in function in the where clause
· Use the ANSI standards in SQL
· Do not rely on database-specific default date formats
· Do not assume that all database return the same error codes and messages
· Do not use database specific command parameters and flags.
4. Using SQR procedures in the Begin-Select instead of sub-queries gives your move flexibility and improves readability of program, but may negatively impact performance.
5. Local procedures may help to avoid errors caused by incorrect usage of previously defined variables.
6. Coding error-handling routines allows you to alter users of any unusual situation, avoid the system crushes, and prevent a possible database corruption.
7. Avoid hard-coding in programs
8. Place encapsulated debugging statements in the program code
9. Do not forget about comments
10. Use meaningful variable name.
Running SQR in PeopleSoft Applications
We can run SQR programs from the SQR Dialog Box, or from the operating system Command Line. PeopleSoft-delivered reports are usually executed from an online page and run with the help of the PeopleSoft Process Scheduler.
Programs in PeopleSoft can be written in different languages: SQR, Crystal Reports, Cobol Application Engine, etc. all these programs can run under the PeopleSoft Process Scheduler. The Process Scheduler works with processes and job streams.
A process is any program that runs under PeopleSoft Process Scheduler. It can be a reporting program, a file generation program, a database update program, or a combination of all these. We can run processes on workstation or a server. The PeopleSoft Process Scheduler can help to schedule the execution of processes so that they can run automatically.
The PeopleSoft Process Scheduler allows bundling processes into job streams and scheduling them to run at a specific date and time.
Process
Scheduler
Process Monitor
SQR Program
When page information is filled in, PeopleSoft generates process request parameters. These parameters usually include User Id, Run Control Id, Run Location, Output Destination, File/Printer name, plus application-specific parameters, fro example, company Id, FromDate, ToDate and so on.
After the process request parameters have been read from a page, PeopleSoft passes them to the Process Scheduler. The Process Scheduler generates the SQR command line with flags and arguments that are required to run the requested SQR program, invokes SQR and passes the flags and arguments to SQR. When the input from the page is save, the system updates a number of tables that are used by SQR to communicate with the Process Scheduler and the Process Monitor via the PeopleSoft API.
The requested SQR program is executed. It may generate reports, update the database, create flat files, or print its reports directly on the specified printer. Users are kept inormed abou the program status with the help of the PeopleSoft Process Monitor. The Process Monitor receives the program feedback via the PeopleSoft API parameters and displays the program status on the Process Monitor page.
Any program run under the Process Scheduler is considered as a process.
Run Control
When we run a process via the Process Scheduler, we need to supply it with a number of parameters like; the run location, output destination, output format, file/printer name, etc. this information is stored in the PeopleTools Run Control record PSPRCSRUNCNTL.
Each process maintains its own Application Run Control Record to store the process-specific input run-time parameters for ex: as-of-date, companycode, or state. The Run Control Id along with the OperId are the key fields in both Application Run Control Record and PeopleTools Run Control Record.
Process Scheduler Request Dialog
The Process Scheduler Request Dialog is used to specify where we want to run our report, the destination type and format of our output and the time of the report execution.
In this page we tell the system how to handle our process request. We select the Server Name, Recurrence definition, Run Date & Run Time.
The Recurrence parameter allows defining a process as a recurring process that may be executed on a periodic basis.
The default output format for SQR with output type web is Adobe Acrobat-(.pdf)
After filling Process Scheduler Request page, click on the OK button. The PeopleTools Run Control Record (PSPRCSRUNCNTL) is updated. Also a record is inserted into the PSPRCSRQST table (Process Request Table) and other PeopleTools tables. This gives the Process Scheduler the necessary information to run and monitor the process request. The Process Scheduler Server Agent pools the PSPRCSRQST table for incoming process request. When the row is found, the Server Agent invokes special program, called PSSQR wrapper, which then calls SQRW.EXE or SQR.EXE depending on the platform the SQR report runs on.
Take a look at the line under the Run Button. It shows the Process Instance Number. As soon as report is submitted, the system automatically generates the Process Instance value.
Note: A Process Instance number displayed under the Run Button indicates that our Process has been submitted for execution.
The Process Monitor
The Process Monitor not only allows to check the status of our process, but also to delete completed report request from the queue or cancel process requests that re currently running or have been initiated.
If we click on the Process Monitor hyperlink the system will bring the Process Monitor Page. The Refresh Button is used to update this page. The process Monitor shows the Processes by User (Operator Id). We can modify this process list by narrowing the selection down to a specific Server, Type (SQR Report, SQR Process, Application Engine, Crystal, etc.) or Run Status (Success, Error, Initiated, Canceled).
Queued => Initiated => Processing => Posting => Success.
Key Points
1. PeopleSoft delivered reports are usually executed from online page and run with the help of the PeopleSoft Process Scheduler
2. The Process Scheduler works with processes and job streams.
3. PeopleSoft recommends executing processes and jobs via the PeopleSoft Internet Architecture (PIA). It allows more flexibility in distribution and viewing the report output in Report Manager
4. The Process Scheduler controls process execution with the help of the control records; PeopleTools Run Control and the Application Run Control. The Run Control Id along with the Operator Id are the key fields in these records.
5. Clicking on the Run Button in the Run Control Page does not submit a report. It brings u the Process Scheduler Request page.
6. The Process Scheduler Request Page is used to specify where you want to run your report, the destination and the type and format of your output, and the time of the report execution.
7. The Process Instance number displayed under the Run button on the Run Control Page indicates that your process has been submitted for execution.
8. SQR is executed the wrapper program PSSQR.exe. This is an executable module of a program written in C that is responsible for handling your process request.
9. The Trace File hyperlink in the Report/Log Viewer brings you a page with the log file. Any Display or Show statements used in our SQR program will be shown in this file. It will also show you any errors produced by the program.
10. The View Log/Trace link appears on the Process Monitor detail page if the following 2 conditions are true.
a. the process request output destination is set to web.
b. The report and log files were successfully posted to the Report Repository by the Distribution Agent. The process must have a run status of successful.
11. If the program produced a report with an output destination type of web, we would be able to click on the report link from the Report/Log viewer page and view your report online. For process types with output other than web, we can track their status through the Message Log.
12. The Status on the Report manager page differs from the Run Status on the Process Request page.
To access any report under PeopleSoft, we need to know the Menu to which this report belongs. After all application-specific parameters are entered and the Run Control page is saved, the system inserts this information into the Application Run Control record fro the report. In order to attach our report to a Run Control Page, a Process Definition has to be created for this report.
The following PeopleTools objects have be created or reused when we need to attach a custom report to the PeopleSoft Process Scheduler.
· Menu
· Run Control Page
· Component
· Application Run Control Record
· Process Definition
PeopleSoft delivered Application Run Control Record is PRCSRUNCNTL, which has the following fields
OperId Key
Run_Cntl_Id Key
Language_Cd
Language_Option
PeopleSoft delivered Run Control Page is PRCSRUNCNTL which has a Sub-Page named PRCSRUNCNTL_SBP.
Creating Process Definition
Every process run under the PeopleSoft Process Scheduler needs a Process Definition to specify the process attributes and link the process to the appropriate component.
To create a Process definition, we will be using the Process Scheduler manager. This component of PeopleTools, similar to the Maintain Security tool, is accessed via PIA.
Navigation: Home=>PeopleTools=>Process Scheduler Manager=>Use=>Process Definition.
If we select SQR process, the Process Scheduler will not pass the Operator ID and Run Control ID to our program and the program will not work correctly unless we specify Operator ID and Run Control Id as additional parameters.
The Process Name must be the same as our program name, the extension .sqr is not needed.
After we press OK and assigned the type to our process, the system displays the Process Scheduler Process definition component. This component consists of the following 6 pages
1. Process Definition
2. Process Definition Options
3. Override Options
4. Destination
5. Page Transfer
6. Notification
The Process Definition Page
An API aware process is a process that updates the Process Request Table (PSPRCSRQST) with the Process Run Status (Error, Success etc), completion code, message set and message number. This allows the system to perform Commit or Rollback, depending on the Run Status. Based on the process execution results, the system displays a standard or custom message on the Process Monitors Process Request Detail Page.
Not every program is API aware. Turning the API aware flag ON does not automatically make the process API aware.
The Process Definition Options Page
In this page the Run location can be Server, Client or both. The Server name is (optional) specified if you always have to run your process on a particular server. If we do not want to be so restrictive, eave it blank.
If a user leaves the server name blank on the process request page, the system automatically find the first available server that can process the request for this process class. The server name can be specified only if the run location is server.
Placing the SQR Program into the right Directory
SQR reports are located on the file server on your network. There are 4 environment variables that are used by PeopleSoft. The Process Scheduler uses these variables to search for your SQR program.
%PSSQR1% - your workstation user SQR directory
%PSSQR2% - your workstation PS delivered SQR Directory
%PSSQR3% - network file server user SQR directory
%PSSQR4% - network file server PS delivered SQR directory
Key Points
1. Any Run Control Record must have the Operator Id and the Run Control Id as its key fields.
2. A Run Control Record may contain additional fields that are not used in our program.
3. A Run Control Page should be specific to our application and should contain only the fields that we want user to enter. Many processes may use the same Run Control page as long as they share the same input parameters.
4. A Component acts as a link between a Run Control page and a menu. There may be multiple pages in a single component.
5. We can add our SQR program to an existing menu item or create a new one.
6. The appropriate security access must be granted to allow all users to see the new menu.
7. Each PeopleSoft user has an individual profile, called User Profile, which is linked to one or more roles.
8. Permission lists control access to menu, components, and pages for each role. Roles act as links between User Profiles and Permission lists.
9. An API aware process update the Process Request (PSPRCSRQST) tables with the process run status, completion code and message parameters.
10. In Order to make our SQR program run under the Process Scheduler, the following PeopleSoft object have to be created, modified or reused:
a. Application-specific Run Control Record
b. Application Run Control Page
c. Component
d. Menu Item
e. Security access to the new component
f. Process Definition
Making SQR Program API Aware
Making an SQR program API aware involves adding a program code to update the Process Request Table (PSPRCSRQST) with the program run status (Error, Success) completion code, error message set and error message number.
In order to make our SQR Program API aware, we have to use the #Include operators to add the STDAPI.sqc and SETENV.sqc files to our program. The STDAPI.sqc intern, uses the nested #Include operators that refer to other important API files.
#Include ‘setenv.sqc’
Begin-Program
Do Stdapi-Init
Do Main
Do Stdapi-Term
End-Program
#Include ‘datetime.sqc’
#Include ‘Stdapi.sqc’
At the program start, the Stdapi-Init procedure is invoked. This procedure is a part of the PeopleSoft-delivered SQC file STDAPI.sqc. Stdapi-Init invokes 2 more procedures in tern. The first one is Define-Prcs-Vars its job is to initialize all API variables, the second procedure is Get-Run-Control-Parms determine whether the program is called from the Process Scheduler and, if yes, promotes the run status from ‘Initiated’ to ‘Processing’
At the end of the main section, the program calls the Stdapi-Term procedure, which is a part of STDAPI.sqc. The purpose of this procedure is to update the PRCSRQST table with the Process Run Status, Message Parameters, and Return code.
The Run Status in the PSPRCSRQST table is updated based on the #prcs_run_status variable value. The variable determines the Run Status that we see on the Process Monitor page.
In a normal run PeopleSoft promotes the Process Run Status in the following order:
Queued -> Initiated -> Processing -> Success
Key Points
1. An API aware process is a process that updates the Process Request table (PSPRCSRQST) with the process run status (Error, success) completion code, message set, and message number.
2. PeopleSoft-delivered SQC file STDAPI.sqc helps us to make our program API aware.
3. The Define-Prcs-Vars procedure in PRCSDEF.sqc initializes tall the fields used in the API.
4. The Get_Run_Control_Params procedure in PRCSAPI.sqc retrieves input parameters: Database Name, Process Instance, Operator Id, and Run Control Id.
5. It is our SQR programs responsibility to communicate errors back to the Process Scheduler.
Key Points
1. To accept input parameters from PeopleSoft online pages, we can either use the existing PeopleSoft-delivered SQC files or develop our own, depending on the parameters our SQR program needs to accept.
2. Our program should support both types of input parameter retrieval logic: retrieving the parameters from the Process Scheduler, or accepting them from the SQR Dialog Box, or the Command Line.
3. Usually, there are 2 SQC files involved in accepting program input parameters from PeopleSoft Online pages. 1 file should contain a procedure to select all required fields from the proper Run Control Record. Another one should include procedures to edit the selected fields and place them into designated SQR variables.
4. Our SQR program must be changed to include the proper SQC files, and a code to call the input parameter retrieval procedures.
5. A Run Control page that contains all the input parameters should be developed, or an existing page should be used or customized.
6. The changed SQR program has to be thoroughly tested to make sure that the input parameters are passed and accepted correctly.
Key Points
1. We can schedule our programs that run on Server for execution on a recurring basis.
2. A job may include one or more processes.
3. In order to schedule a job for execution, a job definition has to be created. Like a Process Definition, a Job Definition needs to be associated with a Component.
4. We can include processes of different types (SQR program, Cobol, Application Engine,) into one job.
5. We need to create a page in the jobs Component for each run control record used by a process in the job.
6. If we want our processes to be executed sequentially, we should select the Serial mode on our Job definition page, otherwise, use the Parallel Mode.
Preventing an SQR from running outside the Process Scheduler
If the value of $Prcs_oprid is Null, SQR detects that it is not executed from the Process Scheduler.
Key Points
1. In order to prevent an SQR Program execution outside the PeopleSoft Process Scheduler, check the value of the $prcs_oprid variable.
2. Row-Level security is used to control access to specific rows of data in the database. PeopleSoft delivers applications with row-level security.
3. PeopleSoft uses security search view records to provide on-line row-level security.
4. Build your own security views when we cannot use the PeopleSoft-delivered ones.
Working with Effective-Dated Tables
In PeopleSoft, most applications are built around the concept of effective dates. This approach facilitates managing data changes over time and keeping track of all changes. It also allows for creating future-dated information and making these data effective only on the appropriate date.
In order to accomplish this task, some PeopleSoft tables contain a special field: EFFDT- effective date. A table that contains the effective date as part of the table key, is usually called an effective-dated table, and the data rows of such tables are called effective-dated rows.
PeopleSoft relates all effective-dated rows to one of the following 3 categories:
· Historical data rows
· The Current data rows
· Future data rows
The current data row is the most recently entered row, whose effective date comes closest to todsy’s date without exceeding, it. For a given search criteria, there can be only 1 current data record within a particular table.
The future data rows are rows with effective dates that are greater than today’s date. All other rows are considered historical rows. The historical data rows include rows with effective dates that are less than the current row’s effective date, as well as the rows with the effective date equal to the current row effective date if these rows were entered into the system prior to the current row.
Multiple Records with the same Effective Date
In many cases, we need to insert multiple rows with the same effective date into an effective-dated table. Lets consider an example of the effective-dated table PS_JOB that belongs to the PeopleSoft HRMS system. This table contains employee job history records.
An employee may have several events that can occur on the same date. Most of these events are registered in the employee’s job history. When 2 different events may occur on the same day, PeopleSoft provides a key field Effseq (Effective Sequence). When paired with Effdt, this key combination allows entering more than one unique row with the same effective date.
Begin-Select
A.Emplid
A.Company
A.Effdt
A.Action
From PS_JOB A
Where A.Company=’CCB’
And A.Effdt = (Select max(Effdt) from PS_JOB B
Where A.Emplid = B.Emplid
And A.Empl_Rcd = B.Empl_Rcd
And A.Effdt <= Sysdate)
And A.Effseq = (Select max(Effseq) from PS_JOB C
Where A.Emplid = C.Emplid
And A.Empl_Rcd = C.Empl_Rcd
And A.Effdt = C.Effdt)
End-Select
Selecting the top row from the Effective-dated table
Begin-Select
A.Emplid
A.Company
A.Effdt
A.Action
From PS_JOB A
Where A.Company=’CCB’
And A.Effdt = (Select max(Effdt) from PS_JOB B
Where A.Emplid = B.Emplid (the sub selection does not include additional
And A.Empl_Rcd = B.Empl_Rcd restrictions on the Effective dates.)
And A.Effseq = (Select max(Effseq) from PS_JOB C
Where A.Emplid = C.Emplid
And A.Empl_Rcd = C.Empl_Rcd
And A.Effdt = C.Effdt)
End-Select
Key Points
1. Placing effective dates in table records allows from managing and tracking data changes over time.
2. Tables that contain the Effective date columns are usually called Effective-dated tables.
3. PeopleSoft relates all effective-dated rows to one of the 3 categories: Historical rows, Current rows and Future rows.
4. There can be one 1 current record for a particular table key.
5. In PeopleSoft, if the effective date is present in a table record, it is always a part of the table key.
6. The effective sequence fields, when paired with the effective date fields, allow for entering more than 1 row with the same effective date.
7. The MAX SQL aggregate function can be used to select the current rows from the effective-dated table.
8. We can improve performance when selecting the current records by sorting table by the effective date and sequence and using Loops=1 to limit the output of the sub-queries to only 1 record.
9. We can select both the current and prior rows from an effective-dated table.
10. We can us the Exists SQL operator to select only specific prior rows.
11. Date intervals between events can be calculated using either SQR date functions or database-specific functions. Avoiding database-specific functions makes your programs database-independent.
