Table of Contents

Part I SQR basics 1

1 Introducing SQR 3

SQR architecture 4

Database support 6

Operating system support 7

What's new in the recent releases 7

2 Structured Query Language 11

Relational database model 12

Simple SELECT statements 14

Using relational and Boolean operators in the WHERE clause 17

Aggregate functions 18

Subqueries 22

Table joins 23

Table updates 25

Referential integrity 28

Performance considerations 29

3 Getting started 32

Building your first SQR program 33

SQR Dialog Box 34

SQR output 37

Adding more complexity 38

4 SQR data elements and data manipulations 41

SQR columns, variables, and literals 42

Predefined SQR variables 44

Working with dates 47

List variables 48

Manipulating data elements 49

Arithmetic commands 49 - The Move command 49 - String manipulation commands 51 - The Let command 54

Built-in functions in SQR 58

Numeric functions 58 - File-related functions 58 - Date functions 59 - String functions 61 - Miscellaneous functions 63

5 Basic program structure 65

SQR page 66

How SQR processes the source program 67

Five sections of an SQR program 68

Program section 69

Setup section 69

Heading section 71

Footing section 73

Procedure section 74

6 Working with data from a database 79

The Select paragraph 80

How to reference selected columns and SQL expressions 85

Explicit and implicit printing 85

Selecting data from multiple tables 88

Using the Load-Lookup and Lookup commands to improve performance 89

7 Taking full advantage of SQL 95

Using the SQL paragraph in the procedure section 96

DML vs. DDL statements 99

Using the SQL paragraph in the Setup Section 100

8 Loops and decision logic in SQR 102

Logical expressions 103

Operands in logical expressions 103

Relational, string, and numeric operators in logical expressions 104

Functions 105

Using If ... [Else] operators 105

The Evaluate statement in conditional processing 106

Using the While command in loops 108

9 Enhancing your report 111

Using the Print command 112

Formatting your output 115

Using edit masks 116

Text format masks 116 - Numeric format masks 117 - Date and time format masks 118

More about edit masks 120

Using the Position command 121

Controlling the vertical spacing 122

Controlling the horizontal spacing 124

Changing report?s heading or footing 125

Delaying printing of data 126

Generating PDF files 129

Part II Advanced features of SQR 133

10 Using break logic in your SQR program 135

Using the On-Break option of the Print command 137

Using Level qualifiers for multiple level breaks 140

Using procedures when breaks occur 142

Using the Save qualifier of the On-Break option 147

Using Print = Never option in the Print command 150

Controlling page breaks 153

11 Run-time and compile-time variables 159

SQR bind variables 160

Substitution variables 162

Dynamic query variables 166

More about dynamic query variables 169

12 Working with arrays 175

SQR arrays 176

How to create an array 177

Placing data into arrays 179

Initializing arrays 181

Retrieving data from arrays 181

Performing arithmetic operations on elements of an array 182

Sorting array elements 188

Searching data in arrays 191

13 Creating multiple reports 197

Defining multiple reports 198

Handling multiple reports in one program 201

An example of a multiple report program 202

Output files in SQR programs with multiple reports 208

14 Creating SQR Portable Files 211

How to create an SPF file 212

Advantages of SPF files 213

Using the SQR Viewer 214

Converting SPF files to printer-specific files 216

15 Generating letters 219

Using the Document paragraph to create form letters 220

Document markers 224

Using variable length sections 231

16 Using graphics 237

Declaring a business chart 238

Creating an array 240

Printing a chart 241

Using images in reports 247

Printing bar codes 249

Drawing boxes and solid lines 251

17 Working with flat files 256

Files in SQR 257

Using the input/output operations in SQR 257

Opening a file 257 - Closing a file 259 - Reading from a file 259 - Writing to a file 263

Different techniques for creating flat file output 264

Using input/output commands 265 - Creating comma-separated file output 267 - Using the Print command to create a flat file 269

Using flat files to import data into your database 270

Using operating system commands to sort files 273

18 Interacting with operating systems and other applications 277

Executing an SQR program from the command line 278

SQR command line flags 278 - SQR command line arguments 281 - Using the argument files 281

Executing a precompiled SQR program 282

Executing your SQR programs in batch mode 284

Issuing operating system commands from an SQR program 284

Calling external programs from SQR 288

Calling a Pro-Cobol program from an SQR program under UNIX 288 - Calling an SQR program from another SQR program under UNIX 290 - Calling a PeopleSoft Cobol program from SQR 290

Calling SQR from other programs 293

Using SQR API 293 - Using PeopleCode to schedule an SQR program execution from PeopleSoft 294

Linking with a user function written in C 297

Creating a user function 297 - Integrating a user function with SQR 299 - Adding a function prototype 299 - Adding an entry to the USERFUNCS array 300 - Adding your C function to Ufunc.c 301 - Re-linking SQR and testing your new function 301 - Adding user-defined functions in Windows NT 301

Generating a Word document 302

19 Internet enabling 310

SQR and the Internet 311

No program code changes 311

Using a table of contents 315

Bursting your HTML output 319

Defining a title and background image 322

Adding more HTML features 324

HTML tabular procedures 326 - HTML heading procedures 332 - HTML hypertext link procedures 335

How an SQR program accepts parameters from the Internet 343

20 Debugging techniques 348

Using the Show and Display commands 349

Using conditional compiler statements 350

The # Debug command 350 - Other conditional compiler directives 351

Using SQR command line flags to enable SQR debugging information 353

21 Good programming practices 356

Going global 357

Creating platform-independent programs 362

Database-independent programs 364

SQL or procedure calls? 367

Using the top-down approach 368

Local procedures vs. global procedures 369

Handling error conditions 370

Other useful suggestions 371

Part III SQR and PeopleSoft 375

22 Running SQR in PeopleSoft applications 377

SQR and PeopleSoft 378

A high-level view 379

PeopleSoft objects 380

Selecting a menu 381

Run control 383

The Process Scheduler Request dialog 384

The Process Monitor 388

Deleting, cancelling, or putting on hold report request 391 - Viewing the Process Request parameters 391 - Reviewing the Message Log and Trace files 392 - Viewing the report output 394

23 Attaching an SQR program to PeopleSoft objects 400

Behind the scenes 401

PeopleSoft Internet Architecture 402

Selecting a Run Control record 404

Selecting a Run Control page 406

Different methods of searching for a Run Control page 410

Creating a component 415

Selecting a menu for your report 419

Granting security access to a new menu item 421

Creating a Process definition for your program 432

The Process Definition page 434 F Process Definition Options 436 - Override Options page 440 - Destination page 440 - Page transfer 442 - Notification page 442

Placing the program into the right directory 444

Testing your Process Definition 445

24 Making an SQR program API Aware 450

Using PeopleSoft-delivered SQC files 451

Incorporating SQC files into your program 452

Testing your changes 459

Communicating errors back to the Process Scheduler 461

25 Accepting input parameters from PeopleSoft pages 465

Using application-specific SQC files to obtain input parameters 466

How the Temporary Employees program accepts its input parameters 466 - Changing your SQR program to accept from and thru dates as input parameters 469 - Adding unique input parameters to your SQR program 472

Creating your own Run Control records and pages 473

Building a custom Run Control page 477 - Modifying the component 480 - Creating the Process definition 480 - Granting Permissions 480

Creating your own SQC files 482

Creating an SQC file to select parameters from the Run Control record 482 - Creating an SQC file to format selected input parameters 483

Changing your SQR program to accept parameters from the Run Control record 484

Testing your SQR program 487

26 Using process recurrences and job streams 492

Recurrence definition 493

Scheduling programs for execution on a recurring basis 496

Using Job Streams 502

Creating a Component for a Job stream 503 - Creating a Menu Item for our new Job stream 505 - Granting security access to the new objects 506 - Creating a Job definition 509 - Scheduling a Job for Execution 512

27 Implementing security in SQR 516

Why SQR needs security 517

Preventing an SQR from running outside the Process Scheduler 517

Using PeopleSoft Security views in SQR to implement row-level security 519

28 Working with effective-dated tables 523

Understanding effective-dated records 524

Multiple records with the same effective date 525

Different techniques of selecting data from effective-dated tables 526

Selecting the current data row 526 - Selecting the current data row from a table which includes multiple records with the same effective dates 529 - Using the Loops parameter in the Select paragraph to limit the number of selected rows 530 - Selecting the top row from an effective-dated table 532 - Selecting the current and prior rows 533 - Using Exists in the Where clause to check for prior rows 539

Other frequently used operations with effective-dated tables 540

Identifying orphan rows 540 - Calculating date intervals between events 541 - Calculating time difference in years since employee?s last promotion 541 - Producing an employee fifth anniversary list 545

 
appendix A Sample database 549
appendix B SQR command line flags 557
appendix C Built-in functions 568
appendix D SQR command syntax 586
bibliography 650
index 651