|
|
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 |
|
|
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/ |
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
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
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
|
|
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 #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 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 ! 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
Open $output as #file-ix For-Writing
Record=100:Fixed_Nolf !no line
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
|
|
!********************************************************************** !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' …
|