|
SQR in PeopleSoft and Other Applications Second Edition PeopleSoft v. 8 Galina Landres and Vlad Landres 2003 | 696 pages ISBN: 1932394001 |
|||
 |
$35.00 | PDF ebook | |
|
$69.95 | Softbound print book | |
Table of Contents
preface xix
preface to the first edition xxi
special thanks xxiii
about the authors xxv
about the cover illustration xxvi
about this book xxvii
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
- 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
- Reflects current version of PeopleSoft v.8
- Shows how to integrate SQR programs and PeopleSoft
- Provides 100+ examples of real life source code
- 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
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
DESCRIPTION
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.
This book
Audience
ABOUT THE AUTHORS...
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.