SQR in PeopleSoft and Other Applications, Second Edition
PeopleSoft v. 8
Galina Landres and Vlad Landres
  • September 2003
  • ISBN 9781932394009
  • 696 pages

Programmers, database developers, administrators responsible for PeopleSoft support, functional users, and project managers are discovering SQR, or Structured Query Report Writer, which has become increasingly popular since PeopleSoft selected it as its main SQL processing and reporting tool. This new language liberates programmers from the constraints of SQL and allows them to concentrate on the application aspects of their programs. This new edition covers all SQR language elements and features, showing developers the best ways of utilizing the languages capabilities and demonstrating good programming habits. Written in a "let's do it together" tutorial style, this book starts with the basics and leads users toward a full understanding of the subject. Part one describes the SQR language with all of its features, while part two covers all aspects of interaction between SQR programs and PeopleSoft. This makes the book a working manual for both SQR programmers and PeopleSoft developers.

Table of Contents detailed table of contents


preface to the first edition

special thanks

about the authors

about the cover illustration

about this book

Part I SQR basics

1. Introducing SQR

1.1. SQR architecture

1.2. Database support

1.3. Operating system support

1.3.1. What’s new in the recent releases

2. Structured Query Language

2.1. Relational database model

2.2. Simple SELECT statements

2.3. Using relational and Boolean operators in the WHERE clause

2.4. Aggregate functions

2.5. Subqueries

2.6. Table joins

2.7. Table updates

2.8. Referential integrity

2.9. Performance considerations

3. Getting started

3.1. Building your first SQR program

3.2. SQR Dialog Box

3.3. SQR output

3.4. Adding more complexity

4. SQR data elements and data manipulations

4.1. SQR columns, variables, and literals

4.2. Predefined SQR variables

4.3. Working with dates

4.4. List variables

4.5. Manipulating data elements

4.5.1. Arithmetic commands

4.5.2. The Move command

4.5.3. String manipulation commands

4.5.4. The Let command

4.6. Built-in functions in SQR

4.6.1. Numeric functions

4.6.3. Date functions

4.6.4. String functions

4.6.5. Miscellaneous functions

5. Basic program structure

5.1. SQR page

5.2. How SQR processes the source program

5.3. Five sections of an SQR program

5.4. Program section

5.5. Setup section

5.6. Heading section

5.7. Footing section

5.8. Procedure section

6. Working with data from a database

6.1. The Select paragraph

6.2. How to reference selected columns and SQL expressions

6.3. Explicit and implicit printing

6.4. Selecting data from multiple tables

6.5. Using the Load-Lookup and Lookup commands to improve performance

7. Taking full advantage of SQL

7.1. Using the SQL paragraph in the procedure section

7.2. DML vs. DDL statements

7.3. Using the SQL paragraph in the Setup Section

8. Loops and decision logic in SQR

8.1. Logical expressions

8.2. Operands in logical expressions

8.3. Relational, string, and numeric operators in logical expressions

8.4. Functions

8.5. Using If …​ [Else] operators

8.6. The Evaluate statement in conditional processing

8.7. Using the While command in loops

9. Enhancing your report

9.1. Using the Print command

9.2. Formatting your output

9.3. Using edit masks

9.3.1. Text format masks

9.3.2. Numeric format masks

9.3.3. Date and time format masks

9.4. More about edit masks

9.5. Using the Position command

9.6. Controlling the vertical spacing

9.7. Controlling the horizontal spacing

9.8. Changing report’s heading or footing

9.9. Delaying printing of data

9.10. Generating PDF files

Part II Advanced features of SQR

10. Using break logic in your SQR program

10.1. Using the On-Break option of the Print command

10.2. Using Level qualifiers for multiple level breaks

10.3. Using procedures when breaks occur

10.4. Using the Save qualifier of the On-Break option

10.5. Using Print = Never option in the Print command

10.6. Controlling page breaks

11. Run-time and compile-time variables

11.1. SQR bind variables

11.2. Substitution variables

11.3. Dynamic query variables

11.4. More about dynamic query variables

12. Working with arrays

12.1. SQR arrays

12.2. How to create an array

12.3. Placing data into arrays

12.4. Initializing arrays

12.5. Retrieving data from arrays

12.6. Performing arithmetic operations on elements of an array

12.7. Sorting array elements

12.8. Searching data in arrays

13. Creating multiple reports

13.1. Defining multiple reports

13.2. Handling multiple reports in one program

13.3. An example of a multiple report program

13.4. Output files in SQR programs with multiple reports

14. Creating SQR Portable Files

14.1. How to create an SPF file

14.2. Advantages of SPF files

14.3. Using the SQR Viewer

14.4. Converting SPF files to printer-specific files

15. Generating letters

15.1. Using the Document paragraph to create form letters

15.2. Document markers

15.3. Using variable length sections

16. Using graphics

16.1. Declaring a business chart

16.2. Creating an array

16.3. Printing a chart

16.4. Using images in reports

16.5. Printing bar codes

16.6. Drawing boxes and solid lines

17. Working with flat files

17.1. Files in SQR

17.2. Using the input/output operations in SQR

17.2.1. Opening a file

17.2.2. Closing a file

17.2.3. Reading from a file

17.2.4. Writing to a file

17.3. Different techniques for creating flat file output

17.3.1. Using input/output commands

17.3.2. Creating comma-separated file output

17.3.3. Using the Print command to create a flat file

17.4. Using flat files to import data into your database

17.5. Using operating system commands to sort files

18. Interacting with operating systems and other applications

18.1. Executing an SQR program from the command line

18.1.1. SQR command line flags

18.1.2. SQR command line arguments

18.1.3. Using the argument files

18.2. Executing a precompiled SQR program

18.3. Executing your SQR programs in batch mode

18.4. Issuing operating system commands from an SQR program

18.5. Calling external programs from SQR

18.5.1. Calling a Pro-Cobol program from an SQR program under UNIX

18.5.2. Calling an SQR program from another SQR program under UNIX

18.5.3. Calling a PeopleSoft Cobol program from SQR

18.6. Calling SQR from other programs

18.6.1. Using SQR API

18.6.2. Using PeopleCode to schedule an SQR program execution from PeopleSoft

18.7. Linking with a user function written in C

18.7.1. Creating a user function

18.7.2. Integrating a user function with SQR

18.7.3. Adding a function prototype

18.7.4. Adding an entry to the USERFUNCS array

18.7.5. Adding your C function to Ufunc.c

18.7.6. Re-linking SQR and testing your new function

18.7.7. Adding user-defined functions in Windows NT

18.8. Generating a Word document

19. Internet enabling

19.1. SQR and the Internet

19.2. No program code changes

19.3. Using a table of contents

19.4. Bursting your HTML output

19.5. Defining a title and background image

19.6. Adding more HTML features

19.6.1. HTML tabular procedures

19.6.2. HTML heading procedures

19.7. How an SQR program accepts parameters from the Internet

20. Debugging techniques

20.1. Using the Show and Display commands

20.2. Using conditional compiler statements

20.2.1. The # Debug command

20.2.2. Other conditional compiler directives

20.3. Using SQR command line flags to enable SQR debugging information

21. Good programming practices

21.1. Going global

21.2. Creating platform-independent programs

21.3. Database-independent programs

21.4. SQL or procedure calls?

21.5. Using the top-down approach

21.6. Local procedures vs. global procedures

21.7. Handling error conditions

21.8. Other useful suggestions

Part III SQR and PeopleSoft

22. Running SQR in PeopleSoft applications

22.1. SQR and PeopleSoft

22.2. A high-level view

22.3. PeopleSoft objects

22.4. Selecting a menu

22.5. Run control

22.6. The Process Scheduler Request dialog

22.7. The Process Monitor

22.7.1. Deleting, cancelling, or putting on hold report request

22.7.2. Viewing the Process Request parameters

22.7.3. Reviewing the Message Log and Trace files

22.7.4. Viewing the report output

23. Attaching an SQR program to PeopleSoft objects

23.1. Behind the scenes

23.2. PeopleSoft Internet Architecture

23.3. Selecting a Run Control record

23.4. Selecting a Run Control page

23.5. Different methods of searching for a Run Control page

23.6. Creating a component

23.7. Selecting a menu for your report

23.8. Granting security access to a new menu item

23.9. Creating a Process definition for your program

23.9.1. The Process Definition page

23.9.2. Process Definition Options

23.9.3. Override Options page

23.9.4. Destination page

23.9.5. Page transfer

23.9.6. Notification page

23.10. Placing the program into the right directory

23.11. Testing your Process Definition

24. Making an SQR program API Aware

24.1. Using PeopleSoft-delivered SQC files

24.2. Incorporating SQC files into your program

24.3. Testing your changes

24.4. Communicating errors back to the Process Scheduler

25. Accepting input parameters from PeopleSoft pages

25.1. Using application-specific SQC files to obtain input parameters

25.1.1. How the Temporary Employees program accepts its input parameters

25.1.2. Changing your SQR program to accept from and thru dates as input parameters

25.1.3. Adding unique input parameters to your SQR program

25.2. Creating your own Run Control records and pages

25.2.1. Building a custom Run Control page

25.2.2. Modifying the component

25.2.3. Creating the Process definition

25.2.4. Granting Permissions

25.3. Creating your own SQC files

25.3.1. Creating an SQC file to select parameters from the Run Control record

25.3.2. Creating an SQC file to format selected input parameters

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

25.5. Testing your SQR program

26. Using process recurrences and job streams

26.1. Recurrence definition

26.2. Scheduling programs for execution on a recurring basis

26.3. Using Job Streams

26.3.1. Creating a Component for a Job stream

26.3.2. Creating a Menu Item for our new Job stream

26.3.3. Granting security access to the new objects

26.3.4. Creating a Job definition

26.3.5. Scheduling a Job for Execution

27. Implementing security in SQR

27.1. Why SQR needs security

27.2. Preventing an SQR from running outside the Process Scheduler

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

28. Working with effective-dated tables

28.1. Understanding effective-dated records

28.2. Multiple records with the same effective date

28.3. Different techniques of selecting data from effective-dated tables

28.3.1. Selecting the current data row

28.3.2. Selecting the current data row from a table which includes multiple records with the same effective dates

28.3.3. Using the Loops parameter in the Select paragraph to limit the number of selected row

28.3.4. Selecting the top row from an effective-dated table

28.3.5. Selecting the current and prior rows

28.3.6. Using Exists in the Where clause to check for prior rows

28.4. Other frequently used operations with effective-dated tables

28.4.1. Identifying orphan rows

28.4.2. Calculating date intervals between events

28.4.3. Calculating time difference in years since employee’s last promotion

28.4.4. Producing an employee fifth anniversary list

Appendix A: Sample database

Appendix B: SQR command line flags

Appendix C: Built-in functions

Appendix D: SQR command syntax



What's inside

  • Reflects current version of PeopleSoft v.8
  • Shows how to integrate SQR programs and PeopleSoft
  • Provides 100+ examples of real life source code

About the reader

  • Programmers responsible for the development and support of SQR-based applications
  • PeopleSoft developers who write and maintain SQR programs designed to function in the PeopleSoft environment
  • PeopleSoft functional users, project leaders and managers
  • Database administrators

About the author

Galina Landres has developed IS solutions for Waldenbooks, Entex, Coopers & Lybrand, Seagram, and Universal Studios. Vlad Landres has 25 years experience with systems design, programming, and project management for the banking and insurance industries. Both live in Stamford, Connecticut.

placing your order...

Don't refresh or navigate away from the page.
eBook $55.99 PDF only
SQR in PeopleSoft and Other Applications, Second Edition (eBook) added to cart
continue shopping
go to cart

Prices displayed in rupees will be charged in USD when you check out.

FREE domestic shipping on three or more pBooks