SQR Interview Q & A

1. What is SQR? Features of SQR?
   SQR (Structured Query Reports) is a programming language used in data processing and data reporting. 

   SQR program can execute multiple queries.
Features:
          i) More flexible
          ii) Scalability for Client and Server architecture.
          iii) Multiple platform portability
          iv) Multiple RDBMS portability
2. What is a FLAG?
It is an attribute given to programming for processing and generating output destination file.
3. What is difference between .LIS and .SPF file?

. LIS (list file) is used for line printer execution. Normally it is used for printing cheques.

.SPF file is portable format, this is used for all window-based applications with any printer. Normally it is used for MSPAINT, Image, and Graphics.

4. How many types of SQR files are there?

There are 5 types of SQR files

. SQC: SQC program files contain commonly included procedures that are called by other SQR programs.
. LIS when an SQR reports generate output it is sent to a file with an. LIS extension.
. LOG: SQR.LOG file is the output file used by People soft. This file contains all information such as errors and displays statements. That is output to the terminal during the execution of SQR.
. MAX: SQR does not dynamically allocate memory. The default program allocations (which were coded for lower        DOS memory) usually have to be increased for complex SQR programs.
Note: People soft uses a .MAX file to increase any SQR parameter where default is not adequate for the program. There is one main .MAX file for processing under SQR W (windows) called ALLMAXES.MAX
. SQT: These program files are precompiled SQR programs. As a People soft 5+ there are no delivered precompiled SQR programs.
5. Explain briefly about flags?
The SQR command supports several flags. Each flag begins with a dash (-). Flags with arguments must be followed directly by the argument with no intervening space.

SQR Command-Line Flags
Flag Description
-A Causes the report output to be appended to an existing file of the user name

-Bnn (Oracle,SYBASE CT-Lib) indicates how many rows to buffer each time data
is retrieved from the database. The default is 10 rows.

-C Specifies that the cancel dialog box appear while the program.

-CB (Callable SQR) forces the communication box to be used.

-DEBUG (xxx) (Sybase) Causes lines preceded by #DEBUG to be compared. Without this
flag, these lines are ignored.

-E(file) Causes error messages to be directed to the named file, or to the default file
report errs. If no errors occur, no file is created.

-F (file) (Directory) overrides the default output file name program .lis

-KEEP In addition to .LIS files, creates an .SPF file for each report that your
program generates.

- LOCK(RR]CS]RO]RL]XX] (SQL Base) Defines the types of locking (isolation level
for the session.

-NR (SQL Base) specifies that the No Recovery mode is used when connecting to the database.

-XCB Do not use the communication box.

-XL Prevents SQR from logging on to the database. Programs run in this
mode cannot contain any SQL statements.

-XMB Disables the error message display so that a program can be run without
interruption by error message boxes.

-XP (SYBASE DB-Lib) Prevents SQR from creating temporary stored pro-
cedures.

-XTB Preserves the trailing blanks in an LIS file.

-ZIV Invokes the SPF viewer after generating program .spf file. Using this flag
implicitly invokes the –KEEP flag to create program .spf file.

-ZMF(File) specifies the full path and name of the SQR Error Message file. The Sqr
err.dat located in the directory indicated by the SQR DIR environment
Variable is the default.

6. How SQR Programs are evaluated and executed at different times?
FIRST PASS: Or Compile stage. SQR processes the following components of the program.
1. All # include external source files (SQCs) are inserted into the source program.
2. All # commands are evaluated.
3. The setup section is processes including allocation and population of memory arrays created by the load-look up command.
4. Optimization of the SQL data access path is determined.
5. Checks syntax of SQR source program.

SECOND PASS: Actual execution of the program source occurs starting at the begin
Report stopping at the end-report.
1. Process data for the body section.
2. Senses page overflow or response to new-page command in code.
3. Process heading section
4. Process footing section
5. Writes entire page buffer to file.
7. How many types of SQR data elements are there?
Columns, variables, Literal
8. What does SQR report structure(program) consists of or Types of sections in SQR Program?
Report or Program – Determines where SQR will begin and end execution of your report. People soft uses this section to control the flow of processing by calling procedures using the do command, thereby utilizing modular programming.
Setup Section – Contains commands, which describe the general report characteristics (Note: the setup section is preprocessed and is automatically run before the program begins execution. This section can only include SQR commands.
Programming section –
A – Begin – SQL: The SQL paragraph allows you to execute any non-select SQL
Statement.
B. Begin-Document-: Allows you to mix background text. With the data retrieved from the database.
C. Begin-Select: Select paragraph is the heart of the SQR program. For each row of data retrieved from the query, acting as an implicit while loop may also process a set of commands there. Here you specify what columns of data to retrieve from the database. Other procedures may be referenced within a select paragraph, which in turn may contain other select paragraphs.
Note: In begin select SQR requires that columns names be flesh to the left margin and SQR commands be indented.
Heading Section
Footing Section
Procedure Section : A procedure is a list of commands or paragraphs, which are processed when referenced by a corresponding, do command. Procedure names must be unique. Procedures can contain regular SQR commands as well as three special kinds of paragraphs: A. SQL B. Document C. Select
9. Explain Print Function?
The print command, is both explicit and implicit, is the focal point for formatting and processing.
Explicit print is used to print database column referenced by its pseudonym (&column name) or a user defined variable.
The Implicit print is only valid for database columns as they are retrieved in the select para.
10. What are positional co-ordinates?
Printing in SQR is placing output on the page grid. Position syntax is used to define where to place data on the page within a given section.
11. Explain briefly about On-break processing?
On-break option on columns will cause each column to break independently from each other. When a break occurs the following happens, After procedure are processed in descending sequence from the highest level to the level of the current break filed.
12. Difference between ASK V/S INPUT?
ASK : It is requests the user for the value and substitutes the value during compilation. It is used only in the setup section.
INPUT : It requests the user for the value at runtime. (i.e., at execution stage). It can be used anywhere in the programming section.
Note: INPUT is mere efficient than ASK.
13. Define an Array?
An Array can be defined to store intermediate results or data retrieved from the database.
14. Define Process Scheduler?
It is an centralized tool that enables application developers, system administrators and application users to manage People soft batch process. Process Scheduler enables you to schedule running process and create job.

15.Difference between Process and Job?
Process: A process is a single task, program or routine (such as SQR reports or Batch Cobol) that runs either on client or server.
Job: It is one or more process of the same or different type, which can run either in series or Parallel. (A group of process can be combined and run as process job)

16. What are the sections and paragraphs available?
There are five sections and three paragraphs available in SQR programming. The sections include

a. Begin-setup End-setup.
b. Begin-program End-program.
c. Begin-procedure End-procedure.
d. Begin-heading End-heading.
e. Begin-footing End-footing

And the paragraphs include

Begin-select paragraph.
Begin-SQL paragraph.
Begin-document paragraph
17. Is it possible to increase the array size once defined?
No, it is not possible to increase the Array size once defined
18. What is Load-lookup? Give the Syntax for Load-Lookup?
The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. With LOAD-LOOKUP, you can reduce the number of tables that are joined in one SELECT. Use this command in conjunction with one or more LOOKUP commands.
Syntax: begin-setup
load-lookup
name=prods
table=products
key=product_code
return_value=description
end-setup
19. Can you Call a SQR from another SQR? How?
We can call one SQR program from another SQR program using Call.callsystem. But this is possible only in UNIX Operating system.
20. Briefly explain Most commonly used SQC's in SQR programs?
: Some of the most commonly used SQC's are
1.STDAPI.sqc : This Is Process Scheduler Interface which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id
2.SETENV.sqc : This is used to set the Default Environment based on the Database such Printer Type, Paper Size,Date formats Etc….
3.NUMBER.sqc : This file contains generalized routines to format numbers.
4.DATETIME.sqc : This file contains generalized routines to format dates and times.
6.RESET.sqc : This is an important Footing SQc.
21. What are the commands used in filehandling of sqr?
Using File Handling we can Import. Steps are
1. Opening a File Using 'OPEN FOR-READING' command.
1. Reading Data From File Using 'READ' command.
Writing Data into Table Using 'BEGIN-SQL' paragraph & 'INSERT' command
22. What are On-break parameters?
ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes. The default action prints the field only when its value changes (PRINT=CHANGE).Mainly used for Redundancy of Printing on a page.
23. How can you Debug SQR programs?
We can Debug SQR using Debugging Commands such as
2. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW
24. What is the difference between sqr and sqc?
SQR SQC
1.This is nothing but the Actual 1.This is like a function in SQR.
Source program.
2.This consists of Program Section. 2.This does not consists of program Section.
3.This can be Compiled & Executed. 3.This cannot be Compiled and Executed.
4.We cannot call one SQR from 4.We can call one SQC from another SQC or another SQR
or SQC. SQR program.
25. What are different types of variables in sqr?
Types of variables in SQR are
1. Column Variables.
2. List Variables.
3. System Variables.
4. Document Variables.
5. Substitution Variables.
User Defined Variables
26. Is there any read-only variable in sqr?
Column Variables & System Variables are Read-Only Variables.
27. How to pass Inputs for sqr program while running from windows?
Using ASK & INPUT Commands.
28. How to pass Inputs to the SQR while running through the Process Scheduler?
Using Procedures & SQC's to access data on Run Control Page (Run Control Fields).
30. What is the purpose of Stdapi.sqc
This is a Process Scheduler Interface sqc which is used to initiate and terminate some field
values such as Process_instance & Run_cntl_id
31. What is the SQT file? What are the advantages of SQT files?
SQT' file is nothing but resultant file after the compilation (i.e.) Runtime File. By using this SQT file for Execution we can increase the Performance of the program by reduciong the Compilation Time.
32. What is the SQC used to read data from translate table?
'READXLAT.sqc' is the SQC used to read data from Translate Table.
33. Which section is mandatory in an SQR program?
'BEGIN-PROGRAM' Section is Mandatory for an SQR program.
34. How will you display an image in SQR?
Using 'DECLARE-IMAGE , END-DECLARE' command.
35. What is use of SETENV.SQC?
This is used to set the Default Environment based on the Database such Printer Type, Paper Size, Date formats Etc….
36. How will you perform ERROR handling in SQR?
We can do Error handling in SQR using some command line flags such as –O, -L,-ZMF,-XMB and using Error handling commands such as
ON-ERROR = Skip/Warn/Stop (for Compile time errors)
ON-ERROR = Procedure Name (for execution stage errors).
37. How many ways of performance tuning are there in SQR?
1. Using LOADF-LOOKUP & LOOKUP.
2. Using ARRAYS.
3. Using MULTIPE REPORTS.
4. Using SQT Files.
5. Using –Bnn Command line Flag.
6. Running on BATCH SERVER.
7. Using Proper Programming Logic in SQR such as Using BREAK statement in EVALUATE Command.
8. Proper SQL tuning such as using Sub queries instead of Joins.
38. Can you call procedure in oracle from Sqr? How?
To call a database procedure using Begin-select paragraph, the syntax is as follows:
Begin-select
[(report arguments)]
from Dual
End-select
To call from Begin-sql, the syntax will be
Begin-SQL
;;
End-SQL
39. Can you connect more than one database using one Sqr? How?
Yes.
40. What are the differences between Load lookup and Array

ARRAYS LOAD-LOOKUP

1. Arrays can be declared in any section. 1. Load-Lookup is declared in only in
SETUP section only.
2. Number of rows are not automatically 2. Numbers of rows are automatically added. gives a error if the number of added.
Exceeds the specified number.
3. We should insert data in to Array by 3. Rows are automatically inserted in to
programming. Load-Lookup.
4.We can retrive any number of fields. 4.We can retrive only Two fields from
from a table. a table.
5.We have Length & Data type. 5.We don't have Length & Data type.
6.We cannot directly print from Array. 6.We can directly print from
Load-Lookup.
41. What are the steps required to run the SQR from peoplesoft environment?
Three steps we have to fallow to run the SQR program from the peoplesoft environment (Process Scheduler). These steps include
a. Making the SQR program API aware by calling two procedures (stdapi-init, stdapi-term) from the Begin-program section and including the Stdapi.sqc in the bottom of the program.
b. Create new run control table or using existing run control table and creating run control page, component and register the component. Creating new run control SQC if we create a new run control table.
c. Giving the permission to the user by giving process groups.
42. How to pass command line flags to a sqr report running through process scheduler?
Using override options in the process definition in the Process Scheduler in the People Tools.
43. What are On-break parameters?
ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes . The default action prints the field only when its value changes (PRINT=CHANGE).Mainly used for Redundancy of Printing on a page.
44 .How does peoplesoft Process monitor knows that the process (Say sqr report) is at what stage such as initiated, in process, completed etc

From the Field Value of 'RUNSTATUSDESCR'field from 'PS_PMN_PRCSLIST' table.
45. How can u Debugging SQR programs?

We can Debug SQR using Debugging Commands such as
3. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW
46. How to refer a global variable in local procedure?
After special character (ex.#,&) we use underscore(_) after that variable name.
47. What is -Bnn flag?
This is used to specify the number of rows that are to be taken from Dbase to the Buffer because default the system takes 10 rows at a time.
48. What is -Tnn flag?
This is mainly used for testing purpose. We can restrict the system to generate only a specific number of pages instead of generating all pages for testing. This is used in Windows Environment.
49. What is -RS & -RT Flags?
RS is used to generate SPF file ie; stopping the program after the comilation. _RT is used to run the SPF file .This is also one kind of Performance increasing technique by saving the compilation time.

50. What are the sections in Sqr? Which one is necessary?
Setup, Heading, Footing, Program & Procedure Sections. Program section is the Mandatory section.
51. What is -debug command line flag?

This is used for Debugging in SQR.

52. How to place an corporate logo in sqr?
Using "Declare Image" command

53. what are the debugging commands in sqr?
Show & Display are debugging commands

54. what is the difference between them?
Show is used to display more than one variable at a time.
Display is used to display only one variable at a time.

Complete SQR

A. General Reference

	SQR – (as of Version 6) SQR is a weakly typed language. Variables can be created when first referenced. Many bugs can be attributed to mistyping variable names unless Declare-Variable is used
Performance	SQR has a cursor pool that handles up to 30 cursors. A large program with more than 30 sql statements can cause a rotation of sql to cursor handles. Normally static sql may run like dynamic being re-prepared with each sql execution causing a performance hit. Consider the load-lookup option (see below) for small result sets.
Variable definition	&… database column or expression name. Can be any type (char, number, date) Declared automatically for columns defined in query (except for dynamic columns and database or aggregate functions) To reference a database variable, preface it with the table alias if given, eg. Begin-select   col1   $x = &t.col1   From  mytable t  End-select $y = &t.col1 $... text or date variable  #... numeric variable – floating pint, decimal or integer %... list variable @... variable name for marker location (used to identify positions to begin for printing inside a BEGIN-DOCUMENT paragraph Use Declare-Variable command to explicitly set declare variables of the type you want (strong typing of variable). Declare-Variable can only be used in a) Setup section or b) first statement of a local procedure Begin-Setup    Declare-Variable         Decimal(nn) #var1  #var2    (specify nn for number of decimals)          Float  #var3 #var4          Integer #var5  #var 6          Text $var7          Date  $var8    End-Declare End-Setup         Note a Date variable defined in Declare-Variable is a ‘real’ date as opposed to defining a variable as $var_date (which is really a string)
Variable scope	Variables are either global or local (local to procedure)
Literals	Text or numeric constants Text literal surrounded by single quotes 0-9 begin any numeric literals, eg -543.21 or 1.2E5
Variable naming	Can be almost any name of almost any length (except for reserved words) Do not use ‘_ ‘ or ‘:’ as first char Not case sensitive SQL initializes vars to null (text and date) or zero (numeric) Numeric variables are 1 of 3 types – FLOAT, INTEGER ro DECIMAL (See DECLARE-VARIABLE) Variables and columns are known globally throughout report, except if used in local procedure (one with args declared with LOCAL argument – See BEGIN-PROCEDURE)
Arrays	Arrays are 0 based indexed create-array name=myarray size=500      !(500number rows)    field=colname1:char=’init value’    field=colname2:number:2=1.0   !(number:2 indicates 2 occurances                                     in a row) Assigning value Put  #x into myarray(1) colname2(1) Or Put $y #x1 #x2 into myarray(3)     !puts values into array in order of                                     fields in array Get a value Get $y from myarray(2) colname Create-Array Clear-Array  -reinitializes array) Get, Put  - can reference multiple variables in on statement Get #city_tot #count_tot from states(#j) cities counties Put $company $name into  employees(#j) Let  - reference 1 variable/ 1 array position at a time Array-Add, Array-Subtract, Array-Multiply, Array-Divide SQR did not give an error, but also did not give correct results with : put &np_racf_logon into racf_array(#racf_id_ix -1 ) racf_id It worked ok with: let #racf_id_ix =  #number_racf_ids  - 1 put &np_racf_logon into racf_array(#racf_id_ix ) racf_id
Comment	!    To us in as text, type it twice - !! (Same for ‘ –single quote – ‘’ to print 1 quote)
Continuation	-       (hyphen) at end of line  - doesn’t seem to work. What’s the catch? Just for continuation of literals? No longer needed?
Concatentation	Let $where = ‘zyc’ ||  ‘abc’
List variables	Contain ordered list of SQR variables (can not nest lists within lists) Create list LET %LIST1 = LIST(num1|string1, num2|string2,…)
Displaying values for trace	If you get an error make sure any variables in show/display are correctly specified (eg. With &, $, table synonym qualifier, etc.) Display var1:edit mask     ! display 1 var with edit Show  ‘var1:’ $var1  ‘var2:’ $var2   ! display multiple text strings                                                   ! and/or variables Show $var1   #var2 edit 9,999.99
Program Structure	5 sections     - Program     - Setup     - Heading     - Footing     - Procedure #include … #.... … begin-setup          Optional section but executes first if present, ..                           Must be placed at beginning of program end-setup Begin-program      Only required section - executes after begin-setup   do …   do … end-program begin-procedure     …     begin-select        …     end-select end-procedure Begin-heading       Processed just be report page written to output,    …                        after page body completed or before New-Page End-heading           issued Begin-footing    … End-footing #include … Begin-report      Use may be discontinued and should be replaced    …                  with begin-program End-report
Default output files	Output file has same name as program with .lis extension (Print …. output goes to the .lis file) Output file put to same directory as your program Log file has same name as program with .log extension (Show … command output shows up in the .log file)
Command line/ Runtime flags	Sqrw mysqr.sqr uid/pswrd runtimeflags arguments Sqrw  ..      Compile/execute (use ‘\’ in directories) Sqr   …      Compile/execute on UNIX  (use ‘/’ in directories) Runtime flags -DEBUGxxxxx  x can be any letter or digit (up to 10 allowed) . Causes compile/execution of debug code #debug and #debugx (where x is one of letters in list) -f    directory where sql will palce the report output file    eg. –fc:\windows\temp -o  define name of sql log file     eg. –oc:\temp\sqr.log -S  show program sql and nunber of times compiled(if dynamic) and executed, rows returned COMPILES = 1 EXECUTES = 1 ROWS     = 36254 CURSOR #3:   SQL = SELECT … -Tnn  generate only nn pages or program output report (all order by clauses in select statements ignored and only first report – if more than 1, is produced) -E[file]  to direct all error messages to a file -RS            compile program only (compiled file given .sqt extension) -RT            execute compiled .sqt version sqrw mysqr.sqr uid/pswd –RS …    (compile) sqrw  mysqr.sqt uid/pswd  -RT …   (execute .sqt file) sqrt  mysqr.sqt  uid/pswd …          (execute using sqrt program) Arguments Arg1  arg2         list arguments in order of Ask then Input @argfile.txt       arguments, 1 per line, in text file, first 2 lines should                         userid/password and db connectivity string Z/OS example command line DSN SYSTEM(DB2A) RUN PROG(SQR)  PLAN(SQR815T)   LIB('SYS3.SQR.V821.SQR.UNICODE.LOAD') PARMS('/ SP DSN/PS80DEV -FSQROUT -GPRINT=YES -P -PRINTER:LP   -XLFF -ISI( -TBZ') Running via UltraEdit Have drive mapped to sqrw.exe (may also be sqrwt,sqrwp,sqrwv) ? is program name, %f tells UltraEdit to insert program name database/userid  –i parm list of directories that SQR searchs for include files, ending each with a directory character. Separate directories with ‘,’ or ‘;’. For Unix using semicolon, precede each semicolon with ‘\’ escape. –o parm  log message file –zif parm full path and name of SQR initialization file sqr.ini –f parm overrides directory/file (if specified) for output file name program.lis –printer parm printer type xx to be used when creating output printer files. –s display status of all cursors at end of the report run (number times sql compiled, execute, total number rows selected) UltraEdit command string follows m:\ps80prd\bin\sqrps\sqr\db2\BINW\sqrw.exe ?%f  PS80PRD/$FOERTS -iC:\QUALSQR\sqr\  -oC:\TEMP\sqr.log -zifw:\PS80dev\sqr\pssqr.ini  -fc:\temp\ -printer:hp -s
Issue Operation system commands	Call System Using $command   #status  ! $command holds the                                                 !system command or program to                                                 ! execute Can hard code command string if you want Call System using $command #status   Wait|Nowait For synchronous or async mode.
Arithmetic commands	Add #var1 to #var2 Subtract #var1 from #var2 Multiply #var1 times #var2    round n  (round to n decimals) Divide #var1 into #var2  round n  On-Error=zero  (optional – can be high value, zero or halt processing – the default)
Move	Move can do data conversions from one data type to another, and data editing if edit mask provided. Move &phone to &display_phone (xxxx)bxxx-xxxx Move #average to $display_average $9,999.99 Move ‘Month DD, YYYY’ to date_mask Move &effdt to $Effective_dt :$date_mask   (when mask in variable the variable must first start with ‘:’) Move &counter to #number_of_emps Number Move &Annual_Rate to #Annual_rate Money Move $Hire_Date to $Start_date  Date
String commands	Find ‘John’ in $full_name 0 #position   (-1 into #position if not found) Extract $area_code from $phone_number 1 3 Encode ‘<27>KL11233’ to $Bold  Concat $Full_zip with $address   (zip appended to address) Let &average = ‘average = ‘ Concat #average to &average String $emplid $emplname $birth_dt by ‘,’ into $empl_record Unstring $empl_name by ‘-‘  $first_name $mid_initial $last_name
Let command	Let target_variable = expression  (expression can be operands, operators and functions)
Functions	See documentation
Reporting	The heading and footing of a report is generated after the body(detail) of the report is created.  All output lines accumulated first in memory and when page complete then page written out.
Procedures	Can be global or local 2 ways to make procedure local Begin-Procedure List_employees Local    (local keyword makes local) … End-procedure Begin-Procedure List_Employess($Company)  (arguments make local) … End-procedure Local procedures Any query results are local to procedure To reference a global variable in the procedure add and underscore after its special character, eg #_count, $_street All SQ reserved words are global and underscores are needed ($_sql-error) If a local procedure is called recursively only 1 copy of its local variables are maintained.
SQL select	Begin-select   loops=n   !if loops specified, read that many rows only Emplid         !column names must start on col 1 with no comma Name, city   ! or multiple columns can be listed separated by comma    Do xyz      ! any procedures must be indented From   ….        Where … End-select All request columns are assigned first, then any procedures are called. Assigning a value to a variable Begin-select    avg(*) &ee_avg     Print ‘employee avg = ‘ (1,1)     Print &ee_avg (, +1) From … End-select Implicit printing  can be done by placing position parm immediately after the column name Begin-select  Emplname (+1,1) From … End-select Select *   is not allowed in SQR
Native SQL	SQL other than selects can be coded in begin-sql Begin-procedure Begin-SQL  ON-Error=error-proc      !if error execute error-proc Update employees    Set ….  Where ….;           !separate multiple sqls with ‘;’  Delete from … End-sql End-procedure Begin-SQL can also appear in Begin-Setup section Begin-Setup   Begin-SQL On-Error=Warn  ! In setup can use Warn, Stop,Skip   ….   End-SQL End-Setup Dynamic sql If all or part of SQL based on a text string, reference the SQL : $where_phrase = ‘…’ Begin-select Col1, Col2, From    Some_table #IFDEF MVS    \$where_phrase\ #ESLE    [$where_phrase] #ENDIF Warn causes SQR to display warning message Stop causes SQR to stop program Skeip causes SQR to skip errors and continue running the program
SQL Commit	Sybase/SQL Server use    Begin Transaction    Commit Transaction Informix BEGIN WORK END WORK Oracle (/DB2?)    Commit   - (may close all open cursors) A commit is done by SQL when program finishes w/o errors
Load-Lookup	Creates lookup array with 2 fields, - Key and Return_Value If placed in Setup section table loaded only once, if placed in procedure executed whenever procedure called. Begin-Setup Load-Lookup Name=Employee_names        Rows=1000     !default is 100 if omitted        Table=Employees    !table name        Key=Emplid         Return_value=Empl_name    Where=division=’XYZ’   !selection criteria, can contain subselect) End-Setup                  ! see manual for other load-lookup options Begin-Program    …   Lookup Employee_names &emplid $empl_name  …. End-program If load-lookup is used in a procedure, the where phrase can be dynamically created.
If	If …. Else End-If
Evaluate	Evaluate &var   When = ‘T’       …       Break    !exit evaluate – don’t do subsequent ‘when’ statements    When ‘L’       ….       Break     When-Other       ….       Break End-Evaluate
Loops	While ( condition true)       …                     ! coding a break will cause an exit from the loop End-while
Print command	Print …. (x,y,x)   ! x – line position on page, y – column number                            ! z – number of positions to print A ‘+’ or ‘-‘ in front of number makes it relative to number used in prior print command. If no ‘+’ or ‘-‘ used the position is absolute on the page Substitition variables can also be used Begin-Procedure Init #Define emplid-pix 1 #define emplname-pix 10 End-procedure Begin-Select Emplid (+1,{emplid-pix}) Emplname (, {emplname-pix}) From… End-select Formatting command follow the placement Print $emplname (x,y,z)  Underline Center … Fill the line Print ‘-‘ (1,1,100) Fill line with 100 hyphens Edit masks   - see manual for all edit masks Print $zip   (1,1) edit xxxxx-xxxx  Print #price (1,+4) edit 9,999.99    Positioning Postion (5,3)    ! position to line 5, col 3 Print #price ()   ! print price at current position Skipping lines between detail Begin-procedure xyz Begin-sql Emplid (1,1,10) Name  (2,1,20)  Next-Listing Skiplines=1 Need = 2   !skip 1 line, need 2 lines for                                                        ! next group (or do new-page) From … End-sql End-procedure Horizontal Spacing Columns 5  10 20   !  define 3 columns Print $emplid (1,1,10)   !print emplid in first logical column Next-Column Print $emplname(+1,2,20) !print emplname in 2nd logical column Use-Column 3 Print $address (+1,2,30)  !print address in 3rd column Use-Column 0     ! stop printing by logical column, can turn back on                             ! by using Use-column n. Control breaks Begin-select &company (…)  On-Break Level = 1  Before=Company_name                           After=Company_totals  Skiplines = 1                           Save=$prev_company &emplid  ( …) On-Break Level = 2  before=Empl_name                         After=Empl_summary save=$prev_emplid &emplname (…) &eff_date  (….) &salary  (…) End-select More options exist. Check manual
Variables in SQR reports	Bind variables – can change value between invocations of select Begin-select EmplName From Personal_data Where emplid = &emplid      !bind variable End-select Substitution Variables – compile time variables (set at compile time) #Define col_emplid  12                  ! define substitution var #Define col_empl_name 30 … Print ‘Emplid’   (0,1,{col_emplid})   ! using substitution var Oftentimes substitution variables are defined in external source (include file) #Include ‘stdcolumns’   ! include files can be nested Dynamic query variables – used to build dynamic SQL code When used in sql, the variables must be enclosed in [&where]     square brackets  - Unix? \&where\      backslashes (MVS/AS/400)
User input	Begin-setup   Ask emplid ‘Enter employee id’  !Ask can only be used in setup                                                  ! section Input #deduction_year maxlen=4 'Enter year for which deductions are tobe applied' type=integer status=#input_status format=yyyy if #input_status <> 0   Show ' #deduction_year must be numeric and between 2006 and 2020   #return-status = 16   stop end-if End-setup …. Begin-select Emplname From personal_data Where emplid = {emplid} End-select Ask values are sequentially looked for on 1) command line, 2) argument file,  3) user prompt Ask values are assigned at compile time and can not be changed during runtime Begin-Program   Input $eff_date ‘Enter effective date’  type=date   … End-Program Argument file is identified on command line with an ‘@’ symbol Sqrw mysqr.sqr @effdate.txt Input values are 1 per line

Peoplesoft Process Scheduler notes

Run Control	PSPRCSRUNCNTL – Contains OPRID -  signon id – eg FOERTS     OPRID and RUNCTLID are RUNCNTLID – run control id – eric     key fields Other columns such as printer destination, output file destination, etc. Report specific parms come from report specific table begin-select r1.oprid r1.run_cntl_id r1.recruiter_id r1.job_req_nbr from ps_np_run_phw410 r1 where r1.oprid=$prcs_oprid   !passed in as arg and r1.run_cntl_id=$prcs_run_cntl_id  !passed in                                       !as arg end-select end-procedure Get-run-ctl
Reading/Writing files	Files can be read/written in sequential access only Opening a file Open ‘testfile’ as 1 For-Reading  Record=100:Vary    ! chars beyond                         !  100 will be ignored. Vary indicates records must                         !  be terminated with a line terminator (system                         !  dependent) Open $testfile as 1 For-Reading status=#filestat1 IF #Filestat1 != 0    Display ' '    Display ‘'*** Error in File Open 1 ***'    Display ' '    Stop End-if Open $output as #file-ix For-Writing Record=100:Fixed ! line                         !  terminator not included in 100 Open $output as #file-ix For-Writing Record=100:Fixed_Nolf  !no line                                                                         ! feed Open $output as #file-ix For-Append Closing Close 1 Close #file-ix Reading Read #file-ix into $input-record:100 Read #file-ix into $emplid:10  $emplname:10 $address:30 Read #file into $input-record Unstring $input-record By ‘,’  into $emplid $emplname $address Extract $emplname from $input-record 10 10 Let $emplname =substr($input_record,10,10) End of file Read #file into $input-record   If #end-file = #file      ….   End-if Read status Read #file If #read_stat <>  0     … End-if Trailing blanks are omitted  when the record is read. If reading binary data, open the file as fixed or fixed_nolf Read 1 into #Total:4 #Avg:4 Binary fields can be 1 to 4 bytes in length Date fields if written  in SQR date variable format can be read into either a date or a string variable. The date variable must be one of the following formats. 1)     format specified by SQR_DB_DATE_FORAMT 2)     your database specific format 3)     the database independent format of ‘SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]’ Writing a file Write #file From $output:80 Status=$output-stat Write #file from $emplid:10 ‘,’ $last_name:20 ‘,’  $cdate:18 The print command can also be used to write output but this is not as flexible. Consult reference for more info. Sorts can be done by creating the input, calling the system sort (if Unix via Call System using ‘sort ….’), then opening and reading the sorted file. Common practice While 1     Read 1 into $record:80    If #end-file = 1          Break    End-if    Do process-Input-record End-while While  Not #end-file     Read 1 into $record:80    If #end-file          Break    End-if    Do process-Input-record End-while Close 1 End-procedure
Compiler directives	#Include   -  insert external source file #Debug – execute sqr code in the debugging mode    #Debugx  sqr_command     Activated by using –DEBUG as command line argument   Eg. –DEBUGxyz  on command line will activate    #Debug    Show   …’    #Debugx  Show ‘ …’    #Debugy  Show ‘ …’    #Debugz  Show ‘ …’   Also can do   #debuga #ifDEF  MVS   #debuga      Show ‘Running on MVS’   #debuga #endif #If, #Else, #End-if/#Endif (with or without hyphen OK) To change way SQR compiles specified pieces of source code    #IFDEF MVS          ….    #Else          ….    #Endif #IfDef, #Endif             Useful in combination with DEBUG to turn on sections of             code             #Ifdef Debug                         Show  …                         Do …..               #Endif Begin-setup   Ask yes_no  ‘Enter Y or N’ End-setup #if {yes_no} = ‘Y’    …. #else    …. #endif #IfNDef, #End-if
Charts	See reference for printing charts
File output	Mainframe vs PC output #ifdef mvs    let $OutPutFile = '{FILEPREFIX}' || $FileName || '{FILESUFFIX}'    Open $OutPutFile as 1 for-Writing        record = 346:Fixed_nolf             status=#FileStat #else    let $OutPutFile = 'c:\temp\pib391W.csv'    Open $OutPutFile as 1 for-Writing        record = 346:Fixed        status=#FileStat #end-if
System specific SQC’s	Peoplesoft SQC’s that are specific to the environment and care must be taken to properly keep NT vs mainframe versions OPSYS.sqc – defines environment SQR running in    Eg.   #DEFINE NT     Or            #define MVS    mutually exclusive Setupdb.sqc   - various database options
Load-lookup	Load an in-core table and then use for lookups. The table is of the form key/value and the lookup is via a binary search. begin-setup load-lookup             name=prods             table=products             key=product_code             return_value=description end-setup ... begin-select order_num (+1,1) product_code             lookup prods &product_code $desc             print $desc (,15) from orderlines end-select

B. Code templates

!********************************************************************** !Program ID:                                                          * !********************************************************************** ! Description of the Report:                                          * !                                                                     * !********************************************************************** ! Author:                                                             * ! Date:                                                               * !********************************************************************** ! Maintenance History                                                 * !********************************************************************** ! SCR#   Programmer       Date    Description                         * !                                                                     * !********************************************************************** #include 'xxxxxx.sqc' !Identify the environment #include 'yyyyyy.sqc' !Printer and page-size initialization !********************************************************************** begin-setup #define col_company         1 #define col_name            6   declare-variable     Integer #var1  #var2   end-declare   #include setupdb.sqc #ifndef EBCDIC                       ! If not running on IBM MVS or AS/400   declare-printer LP-definition    type=LINEPRINTER   init-string=<27>E<27>(0N<27>&l1O<27>&l8D<27>&l5E<27>&l66F<27>(s16.66H<27>&k2G !            |    |      |       |       |       |        |           | !            |    |      |       |       |       |        |            --> CR !            |    |      |       |       |       |         --> Line Prntr font !            |    |      |       |       |        --> 66 text lines !            |    |      |       |        --> top margin = 5 lines !            |    |      |        --> 8 lines per inch !            |    |       --> Landscape mode !            |     --> ISO 8859-1 symbol set !             --> Reset     before-bold=<27>[r     after-bold=<27>[u   end-declare   #else                                ! EBCDIC Ports   declare-printer LP-definition     type=LINEPRINTER   end-declare   #end-if  declare-layout listing     ORIENTATION=landscape     LEFT-MARGIN=0.0 in     TOP-MARGIN=0.0 in     MAX-LINES = 58     MAX-COLUMNS = 177   end-declare   declare-report A    layout=listing   end-declare   declare-report B    layout=listing   end-declare end-setup !********************************************************************** begin-program end-program !********************************************************************** begin-program end-program !********************************************************************** begin-heading 1   for-reports=(A)   print 'Comp'          (+1,{col_company})   print 'Name'            (+0,{col_name}) end-heading !*************************************************************** begin-procedure Process-Main begin-SELECT  Loops  = A.EMPLID A.NAME  …   Do stuff FROM   … Where   … !********************************************************************** Begin-Procedure Open-Files   let #OutFileNumber = 1   let $OutFileName = '{FILEPREFIX}' || 'PRGID01W| '{FILESUFFIX}'   OPEN $OutFileName     as #OutFileNumber    for-Writing        record=1296:FIXED_NOLF        status=#StatusOutFile   if #statusoutfile <> 0      show 'Error in Opening PRGID01W File'      let #return-code = 1      stop   end-if let $FileName2 = '{FILEPREFIX}' || $FileName2 || '{FILESUFFIX}' open $Filename2 as 2 for-reading record=200:{fixed} status=#filestat2 IF #filestat2 != 0    DISPLAY ' '    DISPLAY '*** Error in File Open 2 ***'    DISPLAY ' '    STOP END-IF End-Procedure Open-Files !********************************************************************** Begin-Procedure Write-Out-File !**********************************************************************   Write #OutFileNumber   from  $field1:11         $field2:11 End-Procedure Write-Out-File !********************************************************************** Begin-Procedure Close-Out-File !**********************************************************************     close #OutFileNumber End-Procedure Close-Out-File #Include 'datetime.sqc'  …