SQR

What is SQR?

SQR (Structured Query Reportwriter) is a procedural language. As its name indicates SQR is mainly used for Reporting purposes, though it is said to have all the capabilities. As you will get to know SQR is very handy when it comes to accessing and manipulating tables in a database. It has all the necessary commands to generate reports, letters, mailing labels and so on.

Advantages of SQR:

1. Has the capabilities of SQL
2. SQR has the commands, which can be used to access and manipulate the database directly. Using these commands we can perform anything that can be done with SQL
3.  Has the capabilities of Procedural Language Along with the capabilities of SQL, SQR has all the capabilities of a procedural programming language which enables us for conditional processing, iterative processing, file handling and so on.
4.  Platform Independent
5. Covers almost all the databases
6. Can generate mailing labels, mails etc.
7. Can generate more than one report in one program
8. Can include graphics in the output (like bar chart, pie chart, barcodes etc.)
9. Can produce HTML output.

Common Types of files associated with SQR

1. .SQR (Like a .c file in C language)
    The source code file should be saved with extension .SQR.
2. .SQC (Like a .h file in C language)
    Any procedure that can be used across several SQR programs can be stored in file with extension .SQC.               It is just enough to #include the SQC file to use the procedures in that SQC file.
3. .SQT (Like a .exe file for a .C file in C)
    Generally a .SQR file is directly executed without any intermediary executable file or something like that.      But if you want one, then that can be created with extension .SQT.
4. .LIS
The output of an SQR will be a .LIS file unless the output contains graphic elements like charts, bar codes, images etc. in the output.
5. .SPF
If the output contains any graphic elements then it will be saved with extension .SPF. In order to view the output you need to have SPF Viewer installed.
6. .LOG
When a program runs successfully along with output file (either .lis or .spf) one more file is also created with an extension .log. The log file contains the information about the SQR file path, output file path, log file path and so on.
7. .OUT
The output of Show and Display statements is placed in .OUT file. If the program contains errors, these errors are placed in .OUT file.

Characteristics of SQR

1.  SQR is case in-sensitive
2. There is no need to declare variables (you can, if u want)
3.  Exclamation ( ! ) at the beginning of the line indicates that it is a comment line
4. There is no block comment in SQR
5. There is no separate statement-separator. There should be only one command (statement) per line.
6. You can continue the command in the next line by giving a hyphen ( - ) at the end of the line.
7. Any variable by default is a global variable, unless it is in a local procedure.

Before looking at the structure of SQR program, let’s see how to write a simple ‘Hello World’ Program.

A ‘Hello World’ Program

Begin-program ! like Main() in C
Print ‘Hello World’ (1,10,15)
End-program

Begin-program is where the execution of the program starts and end-program is where the program terminates. Print is the command, which prints the string passed to it in output file. The numbers in parentheses indicates the row position (1), column position (10) and the number of characters the string is going to occupy (15) respectively. We will discuss about Print command in detail in coming sections of the document. Begin-program - end-program (program section) is the only section that is necessary for a program. It is like Main( ) function in C language. All the remaining sections are optional.

Remember that you have to save the file with .SQR extension.

Sections in SQR program
In total there are 5 sections in a SQR program. They are
1. Program Section
2. Heading Section
3. Footing section
4. Procedure section
5. Setup Section

1. Program Section

Program section is enclosed in Begin-Program - End-Program Block. This section may contain commands and/or calls to other procedures. It is just like Main( ) function in C. Execution starts from Begin-program and ends with End-Program. There can be only one Program section in a SQR.

Program section definition looks like the following:

BEGIN-PROGRAM
. . .
END-PROGRAM

2. Heading Section
Heading Section is enclosed in Begin-Heading - End-Heading block. Heading section contains the print statements that are to be printed for every page. There can be only one heading section in an SQR. You should mention the number of lines the heading is going to occupy.
Heading section definition looks as shown below:

BEGIN-HEADING <No-Of-Lines>
. . .
END-HEADING

3. Footing Section
The functionality of Footing Section is similar to that of Heading Section, except that footing section is used to print Footer for every page. There can be only one Footing section in a SQR. Footing section is enclosed in Begin-footing – End-Footing block. In footing section also you should mention the number of lines Footing is going to occupy.

BEGIN-FOOTING <No-Of-Lines>
. . .
END-FOOTING

4. Procedure Section
As SQR is a procedural programming language, it supports modularity. Modularity in SQR is achieved by using Procedure section. You can divide the given task into procedures and you can call these procedures, which in turn may contain calls to other procedures. There can be any no of procedures in a section.
The definition of a procedure section may look like this

BEGIN-PROCEDURE <Name-of-the-procedure1>
. . .
END-PROCEDURE [<Name-of-the-procedure1>]

After Begin-procedure, you should give the name of the procedure. After End-procedure, it is optional but advisable to mention the name of the procedure, as it improves readability.

5. Setup Section

A setup section contains the statements that determine the overall characteristics of the report to be generated. It can be size of left margin, right margin, top margin, bottom margin and so on. Along with above statements setup section may also contain statements, which are used to declare variables, arrays, charts and so on. The definition of the setup section looks like this:

Begin-setup
. . .
End-setup

There can be only one setup section in a SQR. One important point to note is that setup section should be placed above all the other sections.

Page layout - Header, Footer & Body
In SQR, The page is divided into 3 logical parts:
1. Report Header
2. Report Footer
3. Report Body

Every page of the report contains some information about the report itself, such as title of the report, date, page number and so on. The information such as heading, date etc. is printed on the top of the page and is called report header. The information such as page number of the report is printed at the bottom of the page and is called report footer. Suppose if there are 60 lines (rows) in a page and if header is going to occupy 4 lines and footer is going to occupy 3 lines, then the remaining 53 lines will be allocated to report

body, where all the data of the report is printed. As header, footer and body are independent; the row numbering is also independent of one another for all the sections. Suppose if you use a print statement with position as (1,10) in Heading Section, then this print statement will be printed in the first line of the heading. If you use the same print statement in Program section, then it will be printed in the first line of the body. Same is the case with footer.

Row numbering for every section starts with 1.

Data elements in SQR

1. Column Variables :  Column variables are used to refer to the values that are fetched from the database. These variables are prefixed with &.Consider the following code snippet.

Begin-select
E.emplid &Employee_Id
E.Name
from Employee E
End-Select
Print &Employee_Id (1,1,12)
Print &E.Name(1,13,20)
. . .
Begin-select – End-select block is used to directly access the database tables. In the above code, the value of E.emplid is stored in column variable &Employee_Id. Otherwise we can directly refer the database column by prefixing & to the column name.
The above two print statements print the Emplid and Name of the last row of the Employee table.
Remember that column variables are read-only. You cannot change the values that were stored in column variables.
For example
Let &Employee_Id = 104423 is an invalid command in SQR. (‘Let’ is used to assign values to variables)

String variables and literals

A string variable is prefixed with Dollar sign ($). The default value of string variable is NULL.
Ex: $Name is a valid string variable

A string literal should be enclosed in single quotes.
Ex: Let $name = ‘SQR’
Here the string constant SQR is assigned to $name variable.

Numeric Variables and literals

A numeric variable is prefixed with hash sign (#). By default a numeric variable is a floating point variable and contains 0.

E.g.: #Amount is a valid numeric variable

A numeric literal may contain optional sign and decimal point. It can be even in scientific notation. E.g.: 15.4e3

Date Variables and Literals

A date variable is also prefixed with $. A date variable should be declared explicitly, otherwise it is assumed as a string variable. However a string variable can also store a date value, but as a string. If you want to perform date arithmetic, then it is a must that you should declare Date variables. Date literal should be enclosed in single quotes and the content should be in a valid date

format E.g.: ’08/sep/1980’

Document markers

Document markers are placeholders in documents. Suppose you are sending offer letters to 100 people. The content of the offer letter will be same except the name to which you are sending. A document marker variable is used in place of name and the value in the variable is changed.
A document marker variable should be prefixed with @ E.g.: @Name

Predefined Variables
Following are some of the predefined variables in SQR:

1.  #current-line : current line value in the page
2. #current-column : current column value in the page
3. #end-file : 1 if End Of File is reached. Other wise 0
4.  #page-count : current page number
5. #sql-count : Number of rows affected by DML statement in SQR.
6. $sqr-program : SQR program name that is running
7. $sqr-ver : Version of SQR
8.  $username : username specified in the command line
9. $current-date : current date and time on the local machine
10. $sqr-database : The database name on which the SQR runs. Can be ‘DB2’,’ORACLE’, ‘INFROMIX’,SYBASE’ etc.
11. $sqr-platform :Name of the operating system.Can be‘UNIX’,’DOS’,’WINDOWS’,’WINDOWS-NT’ etc.

Some commonly used Commands in SQR

• Declaring a variable
A variable should be declared in Declare-variable – end-declare block. It’s syntax is as shown below:

Declare-variable
Variable declarations
End-declare
Eg:
Declare-variable
Date $myDate
End-declare

• LET
LET command is used to assign values to variables
LET <VAR> = <Expression>
<VAR> can be string, date or numeric variable
<expression> can be a variable or a constant of same data type or an expression resulting in the same data type as that of RHS
Eg: let #total = #var1 * #var2 is valid
Let $string = ‘SQR’ is valid
Let $string = 10 is invalid

• MOVE

Move is also used to move values into variables. Move command, along with moving the value into the variable will also perform Data type conversion i.e. the result of the expression is converted to the target data type. The only exception is Date and numeric fields can not be converted into each other using Move. Also note that when converting a string to a number, string must contain a valid number.

MOVE <Expression> to <VAR>
E.g.: 1) Move 15 to $string is valid when $string is a string variable.
(after this statement $string contains ‘15’ )
2) Move 15 to $string is invalid when $string is a date variable.

Arithmetic Commands

• Add <expression> to <VAR>
• Subtract <expression> from <VAR>
• Multiply <expression> times <VAR>
• Divide <expression> into <VAR>

<expression> can be any variable, expression or a literal.
<var> is any numeric variable

Print
Print command is used to output to the output file
(.lis or .spf). Print <PrintString> (<row>, <col>, <size>) <format qualifier>
<PrintString> can be a literal or a variable.
<row> is row position; it can be either absolute or relative.
<col> is column position; it can also be either absolute or relative.
If nothing is specified for <row> and <col>, current row position and column position are assumed.
<size> is the number of positions that <PrintString> is going to occupy.
If nothing is specified for <size> the length of the <printString> is assumed.
<format qualifier> can be Bold, Underline, center, fill

• Bold : when used will print <PrintString> in bold
• Underline: underlines <PrintString>
• Center : Prints <PrintString> in center at the specified row. When used, <col> may not be mentioned.
• Fill : Fills <size> positions from (<row>, <col>) with <printstring>

E.g.:
Print ‘Understanding SQR’ (10,1)! absolute
!positioning with no Size
Print ‘Understanding SQR’ (+1)!relative positioning
!of row with no column and size
Print ‘Understanding SQR’(,+15)!relative positioning
!of column with no row and size
Print ‘Understanding SQR’() center !using Format
!qualifier, prints the string in the center.
Print ‘*’ (1,1,60) fill ! fills 60 positions with
! ‘*’,from row 1 & col 1

 Show , Display
Show and display commands are used to send output to the Log File. These commands are very useful for debugging purpose. Essentially show and display perform the same function. But we can output more than one value/variable in a single Show statement, where as with Display statement only one variable or value can be printed.
Show <printstring>
Display <printstring>
<printstring> can be a literal or a variable
E.g.:
Show $Myname ‘ ‘ &e.Emplid ‘ ‘ #sal
Display $Myname
*Show and display should not be used in Setup Section

INPUT
INPUT command is used to take input from the user, when the user runs the
SQR through Command prompt or through SQRW.
INPUT <var> [MAXLEN = <maxlen>] [<Prompt text>]
[TYPE = {CHAR |TEXT |NUMBER | INTEGER| DATE}]
[FORMAT = <format String>]
E.g.:
1) Input $string1 Maxlen = 15 "Enter the string”
TYPE = Char
2) Input $Date1 “Enter the Begin Date” TYPE = DATE
FORMAT = ‘DD/MM/YYYY’

ASK
Ask command is used to retrieve compile time substitution variable. The
retrieval may be by user input, command line arguments or can be from the
entries in @file on the command line.
ASK <var> [<prompt text to be displayed>]
E.g.: Ask #val1 “Enter the No of lines for heading”
It is important to note that ASK executes at Compile time, where as Input
Executes at run time.

Using EDIT Masks
Edit masks are used in print statements in order to print the output in the desired format. When edit is used in a print statement, the output is edited before it is printed. We will see the most commonly used edit-masks.
There are three types of edit masks. They are as follows.
Text edit masks Used for editing text outputs. Some of text edit masks are as follows
 X - used to place a character in the output
 B - used to insert a blank in the output
 ~ - used to skip a character in the output
 Any character can be used as a text constant in the output. If X, B etc has to be placed in the output as constants then place ‘\’ before the character.
e.g.:
Print $phone(1,1) edit ~(xxx)bxxx-xxxx
Suppose $phone contains ‘00123456789’ then output of the above statement will be (012) 345-6789

Numeric edit masks
Used for editing numeric output. Some of the numeric edit masks are
8 - zero fill to the right of the decimal point, space fill to left, left justifies the number
9 – same as above but no left justification.
B – treated as 9, but if value is zero, blank will be printed in the output
$ - used for currency, optionally floats to the right.
& , - denotes decimal point and comma respectively
V – implied decimal point.
E.g.:
print #total-amount (1,1) edit $$$$$.99
Suppose #total-amount contains 342.768 then the output of the above statement will be $342.77


Date edit masks
Date edit masks are used for changing the date formats from one to another.
 YYYY – 4 digit year, YYY (3 digit), YY (2 digit), Y (1 digit)
 Q – quarter of the year.
 WW – week of year (1 – 53), W – week of month (1-5)
 DDD – day of the year, DD- day of month- day of week, DAY – day of week
 MONTH –full name of month, MON – abbreviated month, MM-2 digit month
E.g.:
Suppose $date contains ‘13/Oct/2003’
1)Print $date (1,1) edit ‘DAY,DD/MON/YYYY’
o/p: MONDAY,13/OCT/2003
2)Print $date (1,1) edit ‘day,DD/MON/YYYY’
o/p: Monday,13/OCT/2003

Control Structures

As in any language Control Structures in SQR are used to redirect the flow of control. They can be conditional statements or Iterative statements. Conditional statements are used for conditional processing of a group of statements. Iterative statements are used to execute a block of statements repetitively upon satisfying certain criteria.

Conditional Statements
If – else –Endif
If <Logical Expression>
Sqr-stmts
[else
Sqr-stmts]
end-if
<Logical Expression> is any logical expression, which results in a TRUE or FALSE. Same as that of in C, a non-zero value is considered as TRUE.

Consider the following example.
. . .
If #amount > 15000
<block1>
else
<block2>
end-if
. . .
In the above example, if #amount is greater than 15000 then <block1> will be executed otherwise <block2> will be executed. We can have If-else-Endif statements in <block1> or <block2>. There should be end-if, corresponding to every If. Evaluate Functionality of Evaluate is somewhat similar to Switch statement in C.

EVALUATE <expr>
WHEN <operator> <val>
Sqr-stmts
[ BREAK ]
[ WHEN <operator> <val1>
Sqr-stmts
[ BREAK ] ]
[ WHEN-OTHER
Sqr-stmts
SQR for Neophytes
Page 15 of 58
[ BREAK ] ]
END-EVALUATE

<expr> can be a variable or an expression, which results in a value
<operator> can be any comparison operators like = , < , > ,>=, <= and so on.
<val> can be one of the values that <expr> yields.
WHEN-OTHER block will be executed when no other condition is satisfied. (Similar to ‘Default:’ in C language)
BREAK is used to break the flow out of the EVALUATE block.
If break is omitted then the control falls-through until it encounters break or End- Evaluate.

Eg:
Evaluate #rank
When = 1
Print ‘First Rank..’
Break
When = 2
Print ‘Second Rank..’
Break
When = 3
Print ‘Third Rank..’
Break
When-other
Print ‘Not in First 3 ranks..’
Break
End-Evaluate
Iterative statements
The only iterative statement that is available in SQR is While. It works the same way as While in C language.
While <condition>
Sqr-stmts
End-while
E.g.:
#count = 40
While #Count < 20
Print #Count (,+4)
Let #count = #count – 1
End-while

Data Base Access

Data Base Access in SQR is achieved using Begin-Select – End-Select block. Suppose that we need to access and print all the rows in the table EMPLOYEE where State is ‘AP’. Suppose that EMPLOYEE table contains name, Emplid, City, State and phone. Then following code snippet shows how to access and print the rows in the table

EMPLOYEE:
Begin-select
EMPL.Emplid
EMPL.Name
EMPL.City
EMPL.State
EMPL.Phone
Print &EMPL.Emplid (+1,1,10)
Print &EMPL.Name (,11,20)
Print &EMPL.City (,32,10)
Print &EMPL.State (,43,3)
Print &EMPL.Phone (,47,10)
From Employee EMPL
Where Empl.state = ‘AP’
Order by state, city
End-select

The above Begin-select – End-select block retrieves all the rows in the Employee table whose State contains ‘AP’ and prints them one per row. Emplid, Name, City etc. are the field names in the Employee table and they should be placed one in a row in the first column. Those, which are not database fields, should be

indented inside. (Like the above print statements) The above method of printing database filed values is called Explicit printing, we can implicitly print the as shown below. The following method is called implicit printing.

Begin-select
EMPL.Emplid (+1,1,10)
EMPL.Name (,11,20)
EMPL.City (,32,10)
EMPL.State (,43,3)
EMPL.Phone (,47,10)
From Employee EMPL
Where EMPL.state = ‘AP’
Order by state, city
End-select

Break Logic

A break is a change in the value of a column. Suppose you want to print the entire rows of Employee table (that was used in the above example) group by state. Then all the rows with state, as ‘AP’ is a group. All the rows with state as ‘TN’ is a group. We can apply break logic on state to group them by state. A break occurs, whenever State changes. It can be used to beautify a report, remove redundant data, print totals, subtotals and so on. Now lets see how to use break logic to print all the rows in Employee table , group by

state, city.

Begin-program
Begin-select
State (,1 ) on-break level = 1 skip-lines = 2
City (,5) on-break level = 2
Emplid (,15)
Name (,25)
Phone (,40)
Position(+1) !Command used to advance to next line
From employee
Order by State,City
End-select
End-program

If we observe in the program, Break is applied on state and city i.e. the value of State and city will be printed only when the value changes. Suppose there are 15 rows with state as ‘AP’, not all the 15 rows in the report show ‘AP’, only the first row shows ‘AP’ and for remaining rows it is left as blank, indicating that the state is same as above. Similar is the case with City. Whenever state changes skip-lines = 2 ensures that two lines are left blank before printing the row corresponding to next state. Here if you observe, Break is nested. Whenever value for state changes, it is logical that City should also change. That means a break should also occur on City field. A break on state forces a break on city. When a break occurs at a particular level, Level option ensures that break will also occur for the fields whose level is greater than that level. To boil down, LEVEL key word is used to arrange the break fields hierarchically.

So whenever there is a break in State field, there will be a break in city field also. But if there is a break on City field, there will not be any break in State field. It is logical, as a state may contain different cities, but not vice versa.

The following are the different options available with on-break command: There are many options that can be used with on-break. They are as follows
• Skiplines
Whenever a break occurs on the field. Skiplines option is used to print blank lines on the report.
• Level
Level option is used when there is a break on more than one field to specify the hierarchy.
• Print
Print option is used to specify when to print the variable on which break is applied. There are different options for this.
o Always
Always keyword ensures that the break-field is always printed, irrespective of whether a break occurs or not.
E.g.: . . .
State (,1 )on-break Print = always
. . .
Here print = always ensures that State is always printed, whether a break occurs or not.
o Change
Change is used when you want to print the break-field only if its value changes. This is default.
o Change/Top-page
This makes sure that the break-field is printed only when its value changes or if it prints the rows in a new page
o Never
Never is specified when u don’t want to print the field at all. This is used when you want to group the rows by a certain field but you don’t want to print that field on the report.
• Before
Suppose you want to do something before the value of break-field is changed. Before option is used for this purpose.
E.g.: . . .
State (,1 ) on-break Before = print-state-heading
. . .
In the above example, whenever a break is going to occur on state, just before the break, the procedure print-state-heading is executed. Remember the option before takes a procedure as a parameter.

• After
After is similar to Before option, but the procedure is executed after all the rows of the break-field for a particular value are retrieved.
E.g.: . . .
State (,1 )on-break Before = print-state-heading
after = print-subtotal-for-state
. . .

For the sake of simplicity and clarity, let’s assume that state field has two different values ‘AP’ and ‘TN’. Now lets see how the ‘Before’ and ‘After’ options work. When the query is going to fetch the first row, the value of state is going to change, i.e. a break is going to occur. Now the before-procedure printstate-

heading is executed. Remember that in the procedure &State contains the new value. So we can directly use this value in our beforeprocedure. After executing print-state-heading, all the rows with state as ‘AP’

will be retrieved and printed. When a break occurs on state, i.e. the state is going to change from ‘AP’ to ‘TN’, the after-procedure for state = ‘AP’ is executed (print-subtotal-for-state) Then for state = ‘TN’, before-procedure (print-state-heading) is executed, all the rows with state as ‘TN’ will be retrieved and printed. If the state is going to change to other value or there are no more rows with state as ‘TN’, Then after procedure for state =’TN’ (print-subtotal-forstate) is executed.

• Save
Save option is used to store the previous value of a break field.
E.g.: . . .
State(,1 )on-break Before = print-state-heading
After = print-subtotal-for-state
save = $prev_val
. . .
Consider that state has two values, ‘AP’ and ‘TN’. When the state value
changes from ‘AP’ to ‘TN’, ‘AP’ will be saved to $prev_val .The variable for
save option should be a string variable.

Order of events in break logic

The order of events in Break-logic, when there is a break on more than one field is as follows.
1) Executes BEFORE Procedures in ascending order of Level
2) Selects the rows from Table until a break occurs
3) AFTER Procedures are processed in descending order of Level
4) SAVE variables are set with the value of previous break-field value
5) Before procedures are processed in the ascending order from the current level to highest level
6) Advances the current-line position, if skip lines is specified
7) The value of the new group is printed
8) After the SELECT is complete, AFTER procedures are processed in descending order of Level

Points to remember in Break-Logic
 The BEFORE and AFTER procedures are executed only when the query fetches at least one row.
 ON-BREAK can not be used with SQR numeric Variables
 Place ON-BREAK columns ahead of other columns
 Place lower level ON-BREAK columns ahead of higher level ON-BREAK columns
 Use the same line positions for all ON-BREAK columns

Some SQR functions
Date Functions:
Following are some of the date functions.
• Dateadd()
Adds a value to given date in given units and returns a date
<Date_var> = Dateadd(<date_val>,<units>,<val>)
<Date_var> can be a constant or date valid values
<units> can be ’year’, ’quarter’, ’week’, ’month’, ’day’ ’hour’, ’minute’ or
’second’.
<val> is any numeric variables
e.g.: $newdate = dateadd(‘10/10/2003’,’year’,2)
The value of $newdate will be ‘10/10/2005’
• Datediff()
Gives the difference between two dates in desired units
<Diff>= Datediff(<date_val1>,<date_val2>,<units>)
<Date_val1><date_val2> are valid date values/variables
<units> can be ’year’, ’quarter’, ’week’, ’month’, ’day’ ’hour’, ’minute’ or
’second’.
<diff> is the difference between the two dates in desired units.
e.g.:
#diff = datediff(‘10/10/2002’,’10/10/2000’,’year’)
The value of #diff will be 2.
• Datetostr()
Converts a Date to a string
<str1> = datetostr(<date_val> [, format_mask])
$date = datetostr(‘09/08/2003’,’DD/MON/YY’)
$date contains ‘09/AUG/2003’
• Strtodate()
Converts string to date
<Date_var> = strtodate(<source>[, format_mask])
$dateval = strtodate(‘10/01/2003’,’DD/MM/YYYY’)
$dateval contains 10/01/2003,If $dateval is a date variable

String functions
Some of the most frequently used string functions are listed below
• length( ) : Takes a string as parameter and returns its length
• isnull( ) : Takes a char variable as parameter, returns 1 if the string is null
• ltrim( ): Takes a string, does left trim and return the result as a string
• rtrim( ): Takes a string, does right trim and return the result as a string
• to_char( ): Takes a numeric variable as parameter and returns it as a string.
• to_number( ): Takes a string as parameter and returns it as a numeric value.

Sections in SQR revisited…
Setup section
Setup section contains commands that determine the overall characteristics of the program. Setup section is processed at compile time unlike all other sections, which are processed at run time.
A typical setup section Looks like the following
begin-setup
    declare-layout default
    paper-size = (8.5,11)  !8.5 inches x 11 inches
    left-margin = 1        !margins 1 inch on each side  
    right-margin = 1
    top-margin = 1
    bottom-margin = 1        
    end-declare
end-setup
 In the above example, a default layout is declared using Declare-layout command. This layout will be used by default by the report. We can declare any number of layouts in the setup section with different names and change the layout in report using the command
USE-REPORT as follows.
USE-REPORT <layout-name>
<layout-name> is name of the layout created in setup section.
Some other commands that can be used in Setup section are
• Ask
• Declare-Chart
• Declare-Image
• Declare-printer
• Load-lookup
• Declare-variable

Heading & Footing Sections
Heading and Footing sections are used to print heading and footing for each page of the report. Heading and Footing sections will be executed once per every page of the report.
There can be only one heading section and one footing section in an SQR.

A typical heading section looks like the following:
E.g.: Begin-Heading 4
Let $Date = date now ( )
Print $Date (1, 1) edit ‘DD/MM/YYYY’
Print ‘SQR Training’ (2) center
Page-number (3, 1) ‘Page ’
End-Heading

In the above code, 4, indicates the no of lines that will be occupied by heading.
Page-number is a command, which prints the page number after printing the string passed to it at the given position.
The following code snippet shows how a typical footing section of the SQR looks like.

Begin-Footing 2
Page-number (1, 34) ‘Page’
Last-page ( ) ‘of ‘
End-Footing

In the above code, 2, indicates the number of lines occupied by footing. Last-page is a command in SQR, which prints the string passed to it at the given position and then the last page number.
Remember that the position numbering of Heading, footing and body of the report are independent. Each of the section starts with line number as 1.
The size of the body of the report is the difference between the sum of the size of heading and footing section and the total size of the page.

Program section
A program section is where execution of the program starts. It may contain calls to other procedures along with other statements.

Begin-Program
. . .
Do Check-eligibility
. . .
End-program
There can be only one Program section in a SQR. Do command is used to invoke procedures.

Procedure Section
There can be any number of procedures .A procedure in an SQR can be a Global procedure or a local procedure.
Global procedure
A procedure which takes no parameters and which is not explicitly declared as local is a global procedure.
All the variables used in Global procedures are Global variables i.e. we can directly use these variables in other procedures.

BEGIN-PROGRAM
Do Proc-1
Print #count ()
END-PROGRAM

BEGIN-PROCEDURE Proc-1
Let #count = 50
END-PROCEDURE proc1

In the above code Proc-1 is a global procedure. So #count can be directly referred in other procedures.

Local Procedures
A Procedure, which takes parameters, is considered a local procedure.

Begin-procedure Takes_param($emplid,:$emplName)
. . .
End-Procedure

The above procedure is a local procedure as it takes parameters. The parameters, which are prefixed with ‘:’ are read-write variables, where as other are read-only variables. The values that are to be returned to the calling function are passed as read-write variables. In the above example $Emplid is a read-only variable, where as $emplName is a read-write variable. A local procedure can also be declared explicitly using the keyword Local.

Begin-procedure My_Procedure Local
. . .
End-Procedure

Following rules apply to Local procedures:

o Any variable of a local procedure is a non-entity outside that procedure.
o If a local procedure is called recursively, SQR will maintain only latest copy of the variables (Unlike in C, where every copy of variable is stored in recursion stack)
o Any global variable should be referred in a local procedure by adding an underscore (_ ) between the prefix symbol and the variable name. This rule also applies to SQR predefined variables.

Begin-procedure Takes_param($emplid,:$emplName)
Print #_current-date (1,10) edit ‘DD/MM/YYYY’
. . .
End-Procedure
In the above example #_current-date is a predefined variable #currentdate, but as it is in a local procedure it should be referred as #_current-date.

Working with DDL & DML in SQR To work with DDL and DML statements begin-Sql - End-Sql block should be used. All the SQL statements other than SELECT should be put in Begin-SQL - End- SQL
block. The following example shows how to delete rows with state = ‘TN’ in Employee table

Begin-SQL
Delete from Employee where state = ‘TN’
Commit
End-SQL

Similarly we can perform DDL using Begin-SQL – End-SQL block. Remember that we can use variables in place of literals. After Insert, update and Delete, It is necessary to COMMIT the changes. DDL statements in SQR are processed at compile time rather than at run time. Dynamic SQL and ON-ERROR Normally we can use variables in SQL statements in SQR where literals are allowed. But suppose we want to change the order-by clause in SQL depending on the input given by the user, dynamic SQL comes handy. Not only in Order by clause but also in where clause we can use dynamic SQL to change the query dynamically.

Suppose we want to produce a report of employees grouped by state and city, and one more by Name. Lets see how to do it using dynamic SQL

BEGIN-PROGRAM
Let $OrderbyClause = ‘state,’ || ‘City’
Do generate-report
Let $OrderbyClause = ‘Name’
Do generate-report
END-PROGRAM
Begin-Procedure generate-report on-error = error-proc
Begin-select
EMPL.Emplid (+1,1,10)
EMPL.Name (,11,20)
EMPL.City (,32,10)
EMPL.State (,43,3)
EMPL.Phone (,47,10)
From Employee EMPL
Order by [$OrderbyClause]
End-select
End-procedure generate-report


Begin-procedure error-proc
Show ‘Error in the SQL stmt in generate-report Procedure’
Show $sql-error
End-procedure error-proc

In the above example, First generate-report generates output order by state and city. For the second time it uses the same select block but produces output order by Name.
Remember that you should enclose the variable in[ ] while using it in the query. When there is no dynamic SQL used in the SQL block, the SQL statements are compiled and the syntax errors, if any, are reported. But when using dynamic SQL, SQR cannot check the syntax until run time and it is up to us to handle these errors. In order to handle this ON-ERROR option is used.
In the above example on-error error-proc procedure is called which displays the error in the log file.
If there is a Dynamic SQL statement in setup section, then on-error can take any of the following three values or a procedure
o Stop
Halt the program, if there is any error in dynamic SQL statement
o Warn
Used to display a warning message when an error occurs
o Skip
Used to ignore the errors and to continue the program.

Working with Arrays
As in any programming language, arrays in SQR are used to work with identical groups of fields. SQR has specific commands to create, initialize and populate and manipulate arrays.

Creating an Array
create-array command is used to create an array. Its syntax is as shown below

Create-array
name = <NOA> size = <SOA>
field =<field1> : <data type> [:<NOC>] [ = <InitVal1>]
[ field = <field2>:<data type>[:<NOC>] [ = <IntitVal2>]]
. . .
<NOA> is the name of the array
<SOA> is size of the array
<field1>,<field1> etc. are name of the individual fields.
<data type> can be char, number, decimal, float, integer or date
<NOC> is the number of columns of each field
<InitVal1>,<InitVal2> etc are the initial values of the elements

E.g.: Create-Array
name = MyArray size = 30
field = Emplid :number
field = Address :char :3 = ‘N/A’

The name of the array created is MyArray.The size of the array is 30.There is one number field and 3 char fields in the array. Hence the array can hold 30 number values and 30 * 3 char (string) values. The initial values of Address columns are ‘N/A’. You can imagine Address as an array with size 3 inside the
array MyArray. Please note that ‘3’ in the above example is not the number of characters that Address can hold, but the no of strings address can hold. Because the size of char variable is adjusted depending on the input. Accessing Arrays & assigning values to arrays

<var> = <NOA>.<field>(<num1>[,<num2>])
<var> is the variable into which the value is stored
<NOA> is name of the array
<field> is name of the field.
<num1>,<num2> are subscripts of the array.

E.g.: Now lets see how to access the elements of the array created in the above
section i.e. MyArray
#emplid = MyArray.emplid(0)
$add1 = MyArray.Address(0,0)
$add2 = MyArray.Address(0,1)
$add3 = MyArray.Address(0,2)

The above statements are used to access first elements in the array. The first subscript is used to indicate row. Second subscript is used to indicate column (if at all, if it is there) Assigning values can be done in similar way, by swapping LHS and RHS.
MyArray.emplid(#count) = #emplid assigns #Emplid to the
Emplid of #count+1 st row of MyArray. Remember subscripting of array starts from 0 (similar to C)

Resetting values in an array

Clear-array command is used to reset the values in the array to initial values
Clear-array Name = <NOA>
<NOA> is name of the array.
E.g.: clear-array name = MyArray resets all array elements of
MyArray to their initial values

File handling in SQR
SQR supports necessary operations on flat files. We can open, read and write into the files. Flat files can be useful to
o Import data from other systems to database tables
o Unload data into flat files, so that they can be used as an interface to external system.
o Can be used instead of creating temporary tables, when the no of rows to be inserted are less.

There are 3 file related functions that are defined in SQR. They are
o Delete( ) : Takes a string (file name) as input and deletes the file, if it exists.
o Exists( ) : Takes a string as input checks whether that file exists or not.
o Rename( ): Takes two strings as parameters, renames the first parameter (file) to the second parameter.
In all the above cases the functions return zero, if the operation is successful.
E.g.: #status = rename (‘userdata01.dat’, ‘userdata01.txt’)
If userdata01.dat exists, then the function renames it to userdata01. txt and returns 0, i.e. #status contains zero. Now let us see how to open, read and write into flat files.

Opening a file
The syntax for opening a file in SQR is as shown below:
open <file-name> as <num> <mode> record = <reclen>:<type-of-Rec> status = #var
<file-name> is name of the file to be opened.
<num> is a number is used to refer to this file while reading from /writing to/closing the file. We can use a literal, variable or column variable to indicate the file number. It can be any positive number less than 64000.
<mode> can be one of the following three values:
for-reading : is used when the file has to be opened for reading.
for-writing : is used when the file has to be opened for writing. If the file already exists, it will be overwritten.
for-appending : is used when the file has to be opened for appending.
<rec-len> is the length of the record
<type-of-rec> can be one of the following:
o Vary : Indicates that the records in the file are of variable length. In this case <rec-len> becomes max length of the record, also each record is terminated by line terminator*. Binary files can’t be read with this option.
o Fixed: indicates that all the records in the file are of fixed length that is equal to <rec-len>.<rec-len> is exclusive of length of line terminator
o Fixed_Nolf (fixed with no line terminator): indicates that all the records in the file are 120 bytes long and there is no line terminator at the end of the record. This mode is useful when opening binary files.
*line terminator is automatically appended at the end of the record, and it varies from platform to platform.
#var contains 0, if the file is opened successfully.
E.g.:

open ‘userdata.dat’ as 1 For-reading record = 80:Fixed status =#success

In the above statement ‘userdata.dat’ is opened for reading. This file has fixed length record structure and the length of the record is 80. If the file exists, then #success contains 0, this variable can be checked to see whether the file has been opened successfully or not.

Reading from a file

Read command is used to read from a file which is already opened.
The syntax for reading a file is as follows:
Read <num> into <var1>:<size1> [<var2>:<size2>] . . .
Status = <num_var>
<num> is file handle.
One record is read from the file at a time and first <size1> characters of the record are placed into <var1> variable, next <size2> characters are placed into <var2> variable and so on.
<num_var> is a numeric variable, which contains the status of the read command. It is 0, if the read is successful.

• The SQR predefined variable #end-file is used to detect EOF.

E.g.:
1) read 1 into $record:80
The record of file, whose file handle is 1 is read and first 80 characters of the record is placed into $record.
2) read 2 into #Emplid:10 $name:20 $addr:40
The record of file whose file handle is 2 is read and first 10 characters are placed in #Emplid variable, next 20 into $name and the last 40 into $addr.
3) While not #end-file
read 1 into $record:80
print $record ()
end-while

• In the above example , #end-file is used to read the file until end-of-file is reached.

Writing/Appending to a file

Writing/appending is done using write command. If we open the file in for-appending mode then write appends the records to the file. Otherwise it will freshly write into the file. If the file does not exist, it will create a new one. The syntax of write appears similar to Read command, except that now the
contents are written into the file where as while reading a file, the record contents are placed in the variables.
Write <num> from <var1>:<size1>. . . Status = <num_var>
E.g.:
1) write 1 from $Rec:90 status = #write-status
Now $rec is written into the file whose file handle is 1. The status of the write is stored in #write-status. It is 0, if the write is successful.
2) write 2 from $name ‘,’ #amount ‘,’ $date status = #write-status

In this example, $name,‘,’,#amount,‘,’ and $date are appended into one String and that string is written into the file whose handle is 2. As usual, write- status contains 0, if the write is successful.

Closing a file
Close command is used to close a file. The syntax of the command is:
close <num>
<num> can be a number or a variable or a column variable that contains a numeric value. The file corresponding <num> is closed.
E.g.: close 1 closes the file whose file handle is 1

Compiler Directives & Debugging

Compiler directives are used to direct the compiler while compiling the source code. This may be a file inclusion, runtime substitution of variables or conditional compilation. The following are the compiler directives available in SQR.
#include
#include is used to include other files into the source file. At compile time, whatever the file that is included will be appended to the source file. The function of #include is same as in C.

 #include 'setenv.sqc'
The above statement appends the code in setenv.sqc to the file in which the above statement is included.

#define
define is used to declare constants. Declaring a constant in SQR is same as thatof C.
 #define <Const> <value>
e.g.: #define MAX 100
But unlike in C, while referring a constant, the name of the constant should be enclosed in curly braces ({ }). Suppose if you want to refer the above define variable, it should be referred as {MAX}.
All the references to {MAX} will be substituted by 100 at compile time.

#if - #else - #end-if

#if - #else - #end-if   is used for conditional compilation. Its syntax is
as shown below:
#if <var> <opr> <value>
SQR Commands
[#else
SQR commands
#end-if]

<var> can be any system defined variable or any variable that takes input using ASK command or it can be a constant declared using #define. Consider the following example.
<opr> can be any comparison operator (=,<,>,>=,<= and so on)
<value> is any constant value
#if $sqr-database = ‘ORACLE’
Oracle-specific-stmts
#else
Other-stmts
#end-if
At compile time, the value of $sqr-database is checked and if it is Oracle then only Oracle-specific-stmts will be compiled and Other-stmts will not be compiled.

#Ifdef - #End-if & #ifndef - #End-if

#ifdef & #ifndef  are also used for conditional compilation. Its syntax is as follows
#ifdef <substitutionVariable>
SQR-Commands
#endif
Only if <substitutionVariable> is defined, then SQR-Commands will be compiled, otherwise they will not.
#ifndef works the same way except that the code will be compiled if
<SubstitutionVariable> is not defined.

#debug :
This compiler directive is used to debug the code. Any SQR command that follows #debug will be compiled only when the corresponding –debug flag is specified in the command line.
#debug command may be appended with one or more letters or digits to provide flexibility in debugging.
When you use specific suffix letter in –debug command line flag, all #debug statements with same suffix will be activated. This is in addition to the #debug with no suffix.

E.g.:
1) #debug show ‘Entered in the first procedure’
This will be compiled, only when –debug flag is specified in the command line if you are running it through command line If you are running it through SQRW , then the above statement will execute only when you mention –debug flag in report arguments. If you are running it through process scheduler, then you have to put –debug flag in SQR Flags of Process Monitor in Configuration manager
2)
o #debug show ‘Entered in the first procedure’
o #debuga show ‘Testing 2nd procedure’
o #debugb show ‘Testing 3rd procedure’
o #debugc show ‘Testing 4th procedure’
For the above statements, if you specify –debugac in the command line, then
only statements that will be executed are 1, 2 & 4.
You can use #ifdef when there are more than one debug statements -
Consider the following example:
#ifdef debug
Block-of-stmts-to-be-debugged
#end-if
Whenever you specify –debug flag in the command line Block-of-stmtsto- be-debugged will be compiled and executed. We can include all show statements within this #ifdef - #end-if block.You can also use debug with suffixes, if you specify the same in command line.
#ifdef debuga
block-of-stmts-to-be-debugged
#end-if
will be compiled and executed, if u specify –debuga flag in the command
line.

How a SQR Program is processed
Now let us see how a SQR program is processed by comparing it with a C program.
C Program SQR Program

Running an SQR

There are 3 ways to run an SQR.
1. Through Command line
2. Through SQRW
3. By associating it to a PeopleSoft object

Through Command line

When you are running an SQR through command line, in effect you are running it on the client machine. So it is necessary that you should configure your database client to the database server, where all tables that you have to access reside.The syntax is as follows

[sqr or sqrw] [program] [connectivity][flags][args]
For example you have to run ex1.sqr on your Windows machine, then the command should look like this

Sqr ex1 usrname/password@servername

The output will be placed in the same directory as that of SQR with same name but with different extension (.lis or .spf). Remember you should have SQR installed on your system in order to run it through command line.

Through SQRW

Even when you are running the SQR through SQRW, it will run on client. So it is necessary that you should have your database client setup for the particular database server. If you have installed PeopleSoft, then search for SQRW and double click it. It will throw following screen:

In report name, you should mention the complete path of the SQR, with extension .SQR Username, Password, Database are the names of Username, Password and name of the database Server you are accessing. Report Arguments is used to specify various options. Suppose you want the output to be placed in a different directory, log files to be placed in a different directory, you can do so by using Report Arguments.

A typical SQRW window, just before running, looks like this:
The option –f indicates the path of output file
-O indicates the log file
Remember that the paths should be valid, i.e. they must already been created. There are several other options those can be mentioned in Report arguments. Some of them are -


-KEEP
In addition to .lis file, a .spf file is also created, when this option is used.
-DEBUG
Enables debugging. All the statements with #debug prefix are compiled and executed.
-NOLIS :Prevents the creation of LIS, instead an .spf is created.

Running SQR by Associating with PeopleSoft Object
First let us see how SQR is run through PeopleSoft.The request for a process to be run is made via an online page. These pages can be PeopleSoft delivered or those developed by application programmer.When all the information (input) is filled in online pages and a process request is made, PeopleSoft generates Process Request parameters, these parameters are typically Opr ID, Run control ID, Run Location and so on. Along with these the parameters are also included application specific parameters like From Date, End Date and so on. These parameters are passed to Process Scheduler, the Process Scheduler generates SQR command line with flags and arguments that are required to run the SQR, Runs the SQR and passes these arguments and flags. The input is saved into several tables, which are called run control records. We will discuss them in the coming sections. The system then communicate the state of the program to Process Monitor, weather the
program is Initiated, Running, Error, Cancelled or Success and so on. We can run an SQR through PeopleSoft by associating it with one of the PeopleSoft Objects. The following are the steps that are to be followed, in order to associate SQR program to a PeopleSoft object:

• Create/Identify Run Control Record
• Create/Identify Run Control page
• Put this page in a component
• Create Menu and associate it to the component
• Give necessary Access/ Security Permissions
• Add the menu to the portal
• Define Process
• Make the SQR API aware
• Move the SQR to the designated directory
• Run the SQR

Let us take an example SQR that takes two parameters From Date and End date as input, and see how to associate it with PeopleSoft objects.

Before starting, create a new project, so that we can group all the PeopleSoft objects we are using in this example as one.

Create/Identify Run Control Record
A Run Control Record is a table in PeopleSoft that is used to store the parameters passed to SQR.
There are two types of run control Records.
PeopleTools Run control Record
PeopleTools Run control Record is used to store the Application independent information like User ID, Run Control ID, Source path, output destination and so on. Operator ID and Run Control ID are the primary keys of People Tools Run Control Record i.e.PSPRCSRUNCNTL.

Application Run Control Record

Application Run control Record is used to store the Application specific run-time input parameters. Depending upon the input taken by the SQR we can create a run control record or use an existing one. For Application Run Control Record also, Operator ID and Run Control ID are the primary keys. For our example, we can use an already existing Application Run control record or we can create a new one, which has From Date and End date as fields. Let us create a new Application Run Control Record with name as RUN_CNTL_MYREC and add it to the project. Please note that we have to add OPRID and RUN_CNTL_ID, also we have to make them primary keys. The fields, which we are adding to the run control record can be either already existing or we can create them. For our example let’s use the already

existing fields for our record.After creating the run control record add it to the project, so that in the end we can build all the new objects in our project.

Create/Identify Run Control Page
Now we have to create/identify a Run Control Page, which contains Form date and End Date fields on it, or we can create a new Run Control Page. Let us try to create a new run control page.
Every run control page should contains Run control ID, “Run “ Push button, links to process monitor, Report manager and so on. This can be achieved by inserting PRSCRUNCNTL_SBP into the run control page. The sub page is as shown below.

Along with the above sub page our page should also contain FROM_DATE and END_DATE .So drag and drop FROM_DATE and END_DATE fields into a group box on the page.Save it as MY_RUN_CNTL_PAGE and add it to the project. While saving the page, save the Peoplecode associated with the page also.

Put this page in a component
After creating the run control page, we have to create a component and place the Run control page in the component. Let us create a new component, drag and drop the page into the component. Before saving the component we should associate the component with a search record.
A search record is one which is searched to validate the Run control ID that we enter while running the SQR. If a new run Control ID is added, then it will be stored in the search record. The search record is PRCSRUNCNTL (The Run Control ID in the sub page PRCSRUNCNTL_SBP is from this record only) .
We can specify the search record for the component by editing the properties as shown below.

Now save the component as RUNSQR and add it to the project.

Create Menu and associate it to the component
Now we have to create a Menu and associate the component with this menu. Create a new Menu Item, after giving label, drag and drop the component into the menu and save the menu with some name. Let us say RUNSQR_MNU. Add this menu into the project.

Now save the menu with the name RUNSQR_MNU and add it to the project.
Till now we have created Run Control Record, Run Control Page, Component and Menu. Now save the project and build it. This will actually create the new tables in the backend.

Give necessary Access/ Security Permissions
Here in order to give security permissions, we’ll associate the Menu of our component with appropriate permission List, there by enabling its access.
To give access permissions, log into 4-tier mode and go choose the appropriate Permission List by going through the following path:
People Tools → Security → Permissions & Roles → Permission Lists
After choosing permission list, go to Pages tab of the current page and add the Menu to it
by clicking on + sign

After adding Menu to the permission list, Go to Edit Components of the particular Menu
in order to give access the required pages.

Here we can select the pages to which we want to give access.
Add the menu to the Portal
In order to run the sqr, we have to add the menu to portal, so that we can access the particular page associated with the menu by going through the links in the portal. Now let’s try to add our SQR to portal. Theoretically we can add the menu anywhere in the content, but all the menus corresponding to SQRs, which operate on particular functionality, are grouped into one.

To add the menu to portal go to People Tools → Portal → Structure & content. This will show the folders in Root, you can go into the folders where you want to add the menu and then click Add Content Reference Link in the bottom of the page to add the menu.