SQR Concepts Part-2

Error Handling in SQRs
a) Compilation stage using Begin-SQL on-error = skip/warn/stop
b) Execution stage using Begin-Select/SQL on-error={Procedure Name}

Performance Tuning
a) Load Lookup
b) Arrays
c) Multiple reports
d) Using SQT files
e) SQL Tuning
f) Bulk Operations using -Bnn
g) Proper programming logic

Run-time Variables: Ask & Input

Testing & Debugging
a) Display / Show
b) #ifDef/-Debug
c) Loops = nn
d) -Tnn : pages

Types of SQC's
1) Environment SQCs
    Used to make SQR OS & Database independent.
    Ex: setenv.sqc
2) Setup Section SQCs
    Consists of setup section code.
    Ex: setup31.sqc, setup01.sqc - Portrait
          setup32.sqc, setup02.sqc - Landscape
3) Program Section SQCs
    Consists of program section code. This looks like xxprogxx.sqc
    Ex: hrprog31.sqc
4) Header SQCs
    Consists of Heading section coding. This looks like sxxhdgxx.sqc
    Ex: stdhdg31.sqc
5) Footer SQCs
    Consists of Footing section coding. This looks like xxxfigxx.sqc
    Ex: reset.sqc
6) API Aware SQCs
    Used to execute SQR Programs on PS Batch Server
    Ex: stdapi.sqc
7) Run-time SQCs
    Consists of Ask/Input commands
8) Run Control SQCs
    Consists of Run Control Parameters coding (only in n-tier)
9) Functional SQCs
    Used to support different formats of data.
    Ex: datetime.sqc to support different date formats.
10) Module Specific SQCs
      Consists of module specific coding.

Command Line Flags
-Bnn: Bulk Operation
-ZIV
-E
-O
-C
-F
-RS
-RT
-Tnn


File Handling Commands
a) Open
b) Close
c) Read
d) Write

Running SQR on n-tier
1) Make SQR API Aware.
    a) Include stdapi.sqc and setenv.sqc
    b) Call stdapi-init and stdapi-term Procedures
2) Place SQR file in SQR directory.
3) Create Run Control Definitions
    a) Run Control Table: OPRID and RUN_CNTL_ID must be high level key fields.
    b) Run Control Page: PRCSRUNCNTL_SBP is inserted into the page.
    c) Run Control Component: PSPRCSRUNCNTL is used as Search record.
4) Create Process Definition

SQR Concepts Part-1

Structured Query Reporting (SQR) supports all Databases (all SQL Statements - DML, DDL, DCL & TCL) and Operating Systems.
-> Used for Reporting.
-> Used for Background SQL Processing to update Database.
-> Used to migrate data from legacy system to PS system.
-> Used to perform File Integration.
-> Used where programming logic is more.

SQR Commands
1) Section Commands
    a) Begin-Setup
    b) Begin-Heading
    c) Begin-Footing

    d) Begin-Program

    e) Begin-Procedure

2) Paragraph Commands

    a) Begin-Select

    b) Begin-SQL

    c) Begin-Document

3) Other Commands like Print, Show, Let etc.

SQR Execution

2-tier: Run SQRW.exe (-ZIV Command line Flag)   

n-tier: Run using Process Scheduler/Batch Server

SQR File Types
.SQR - Source Code file

.LIS - List file / Output file

.SPF - Portable Format file

.SQC - Function Library file

.INI - Initialisation file (Config file for SQR)

.SQT - Runtime file

.MAX - Maximisation file

.ERR - Error file

.LOG - Log file

.HTML, .PDF & .CSV - Output file types

Variables: Temporary memory location in SQR buffer to store values.

Types of Variables

a) Column variables

b) SQR variables (Text, Numeric & Date Variables)

c) Reserved variables

d) Document markers

e) Substitution variables

Scope Variables

a) Global

b) Local

Types of Reports

a) Tabular report

b) Master Detail report

c) Form report

d) Mailing labels

e) Letters & Documents

f) Cross Tabular report

g) Graphical report

PeopleCode

This Post is moved to PeopleSoft Notes Blog