Transcript SQR Runner 11/6/2015 Agenda          Introductions SQR Basics SQR Source Structure SQR Program Characteristics Program Considerations SQR Alias Report Manager Reports Efficient Coding Tips SQR Runner Access & Training 11/6/2015

SQR Runner
11/6/2015
1
Agenda









Introductions
SQR Basics
SQR Source Structure
SQR Program Characteristics
Program Considerations
SQR Alias
Report Manager Reports
Efficient Coding Tips
SQR Runner Access & Training
11/6/2015
2
SQR Basics
11/6/2015
3
What is SQR
 Structured Query Reporter
 Programming language used to create
reports from databases where SQL was
traditionally used
 Dynamically generates queries and formats
the results for printing
11/6/2015
4
Variables
$ - Used for date or string variables ($Institution)
# - Numeric variables (#Counter)
& - SQR table fields (&Hire_dte)
! - Comments
_ - extend naming conventions ($Admit_Code)
11/6/2015
5
Global vs. Local Variables
 Global – (default) referenced anywhere in
the program
 Local – only within the procedure where
defined
11/6/2015
6
Predefined Variable
Reserved Variable
 Not declared in your SQR, but can be used
Examples
$CURRENT-DATE
#END
– Data base environment used
$SQR-Program – SQR program name
$DBNAME
* Refer to SQR Manual (pg 44) for Predefined Variable List
& Description
11/6/2015
7
Literals
 String Literals - enclosed in single quotes
Examples
‘This is a Test’
‘05-May-2008’
 Numeric Literal – include digits with
optional decimal point and leading sign
Examples
675.03
-410.91
0
11/6/2015
8
Manipulating Commands
 ARITHMETIC - add, subtract/multiply &
divide
 MOVE - from field to field, data conversion,
editing using special edit masks
Examples
MOVE &Phone TO $Dsply-Ph (xxx)xxx-xxxx
MOVE #Salary TO $Dsply-Sal $9,999,999.99
11/6/2015
9
Manipulating Commands…
 FIND – helps locate starting position of a substring within a string or data field. Returns first
position of first byte of sub-string
Example
FIND ‘John’ in $FULL_NAME 8 #POSITION
$FULL_NAME = ‘0012700 Smith John Sr 962 Oak St…’
FIND RESULT: #position = 14
11/6/2015
10
Manipulating Commands…
 Let – can replace a number of data
manipulation & logic commands
Example
Let $YTD_AVG= #YTD_TOTAL/#NUM_OF_DAYS
 Operators – arithmetic, string or relational
>
<
>=
<=
<>
11/6/2015
Greater than
Less than
Greater than or equal to
Less than or equal to
Not equal
11
SQR Source Structure
11/6/2015
12
Setup Section
Optional…If used, must come first!
 Contains commands that determine
characteristics of the program
 Processed during compilation stage

* Example on next slide
11/6/2015
13
Setup Section
11/6/2015
14
Program Section
Required!
 Defines beginning & the end of the program
 All commands following ‘BEGINPROGRAM’ are executed one-by-one
 Execution stops when ‘END-PROGRAM’ is
read

* Example on next slide
11/6/2015
15
Program Section
11/6/2015
16
Heading Section

Controls report
Heading
11/6/2015
17
Footing Section

Controls report Footing
11/6/2015
18
Procedure Section
Multiple procedures allowed
 Used for structural management and easier
maintenance of program
 Most SQRs include multiple procedures

* Examples on next slides
11/6/2015
19
Procedure Section
11/6/2015
20
Procedure Section

Additional Procedures….
11/6/2015
21
SQR Program Characteristics
11/6/2015
22
READ ONLY ENVIRONMENT
Specific words are NOT ALLOWED in
the Source Code
 Any encounter of these words will cause
compilation errors and must be removed
for source code to successfully compile

Examples
CALL…UPDATE…DELETE…LOAD…INSERT
11/6/2015
23
‘SELECT’ Statements
Statement heart of SQR program
 Operators

 Unqualified select
Example: Select * from PS_NAME
 Relational
Examples: =,<,>, >=, <=, <>
 Boolean
Examples: AND, OR, NOT
11/6/2015
24
‘SELECT’ Statements…
 Specific fields - Examples: Select emplid, name
 Distinct - Example: Select Distinct supervisor from
PS_NAME for employment
 Aggregate Functions - Examples: AVG, SUM, COUNT,
MAX, MIN
 Group – define subsets of select output based on a
certain column
 Having – impose restrictions on selection results
 Like – selection based upon partial knowledge of
columns content. Example: Where name LIKE ‘%John’
11/6/2015
25
SQR Output Files
Message Log – SQR populates with
Program setup
 Trace File – contains output from
‘SHOW’ command
 Report Output File – contains output
from ‘PRINT’ command


* Examples on next slides
11/6/2015
26
SQR Output Files – Message Log
11/6/2015
27
SQR Output Files – Trace File
11/6/2015
28
SQR Output Files – Report Output File
11/6/2015
29
SQR Programming Considerations
11/6/2015
30
Print Commands
Print – directs PDF, CVS output to report file
 Show – directs output to TRACE file for
data/field verification and debugging purposes

11/6/2015
31
SQR Report Structure
Heading/Footing sections of report are
generated AFTER the page body is filled
 Once the page is completed, then it is written
to output file and then erased from memory
to begin creating the next report page

11/6/2015
32
SQR Functions
Numerous numeric and data related builtin functions
 Use a FUNCTION within a FUNCTION

 Inner

FUNCTION must return a value type
compatible with that of the outer function
FUNCTION list – SQR manual, Appendix
“C” (page 568)
Example: Let $Today = datenow ( )
(Places current date on local machine into $Today)
11/6/2015
33
Sample Program - Basic
11/6/2015
34
SQR Report Structure
SQR does not print every time ‘PRINT’
command is issued
 Instead…accumulates each line in memory
 Determines number of lines in report per page
 Allows specified number of lines for Heading
and/or Footing

11/6/2015
35
SQR Program Stages

Compile Stage
External source files inserted into source program
 Substitute variables resolved
 Compile directories evaluated
 Memory and buffers allocated
 Syntax checked

11/6/2015
36
SQR Program Stages

Execution Stage
Error free compilation
 Starts execution @ ‘BEGIN-PROGRAM’
 Stops execution @ ‘END-PROGRAM’

11/6/2015
37
SQR Print Reports

Two types of SQR Print Reports
 Explicit
11/6/2015
- Used in Sample SQRs
38
SQR Print Reports…
 Implicit
- prints column without PRINT
command

You can mix the two types in a SQR
program
 Be
aware of sequence. Print sequence is
determined by the order of the commands in
the select paragraph
11/6/2015
39
SQC Standard Query Catalog
 Standardized common coding that is typically
used in multiple places and users
Example: (see handout)
“UMHDRA.SQC”
 # Include ‘UMHDRA.SQC’
 SQR inserts specified SQC into the source code
during compilation stage
 Typically, standard includes are placed at bottom
of program for clarity & maintenance
11/6/2015
40
SQR Error Recovery
11/6/2015
41
SQR Alias
11/6/2015
42
Normal SQR Selection Criteria
 Alias not needed in this situation
11/6/2015
43
SQR Selection Criteria Using Alias

Used to differentiate between two tables with
similar field names or multiple references to the
same table
11/6/2015
44
Alternative SQR Selection Coding to
Using Alias
11/6/2015
45
Report Manager Reports
11/6/2015
46
Table Joins
11/6/2015
47
Effective
Dated Table
11/6/2015
48
Effective
Dated Table
11/6/2015
49
Performance Coding TIPS





Use indexed fields whenever possible in WHERE
clause
Avoid GROUP BY & DISTINCT – they invoke
internal sorts
Use ORDER BY with indexed fields when
possible
Avoid Boolean NOT…use <> instead
Avoid arithmetic expressions in SELECT,
WHERE & HAVING
11/6/2015
50
Performance Coding Tips
Use ‘NOT EXISTS’ instead of ‘NOT IN’ in
sub-queries
 Avoid using fields of different type &
length in logical operations
 In sub-select statements, use smaller tables
in inner select

11/6/2015
51
SQR Runner Access & Training
11/6/2015
52
Navigation

Login to MaineStreet Portal
Enterprise Applications
 Campus Solutions Reporting DB
 University of Maine System
 Utilities
 Run Your SQR

11/6/2015
53
Run Your SQR! - Add a New Value
The first time you run an SQR you must
create a Run Control ID
 Click the Add a New Value tab

11/6/2015
54
Run Your SQR! - Add a New Value
Enter your User ID (EmplID)
 Enter a Run Control ID
 Click the Add button

11/6/2015
55
Run Your SQR! Page
11/6/2015
56
Training Resources

UPK Player navigation
 Login
to MaineStreet portal
 Training Tools & Materials
 Campus Solutions folder
 UPK folder
 UPK Player link
 Reporting
for Campus Solutions
 Run Your SQR_CSRPT
11/6/2015
57
Training Resources

Documentation
 Internal
Reporting web page
 http://www.maine.edu/system/oft/psfinancial/reporting.php
 SQR
Runner
 Manual
- SQR in PeopleSoft and Other
Applications, PeopleSoft V.8 – Galina Landres & Vlad
Landres
11/6/2015
58