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
|
|
|
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)
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
! 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
|
|
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)
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
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
*
!**********************************************************************
!
!
*
!**********************************************************************
#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
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
! | -->
! --> 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
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'
…
|
No comments:
Post a Comment