Cover image for SQR in PeopleSoft and other applications
Title:
SQR in PeopleSoft and other applications
Author:
Landres, Galina, 1953-
Personal Author:
Publication Information:
Greenwich, CT : Manning, 1999.
Physical Description:
xxxi, 572 pages : illustrations ; 23 cm
Language:
English
Added Author:
ISBN:
9781884777776
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.73.S674 L36 1999 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

SQR in PeopleSoft and Other Applications covers all SQR language elements and features and shows the best ways of utilizing SQR's capabilities. The book is presented in tutorial style; it starts from the basics and progresses gradually to complex topics. More than a hundred realistic examples are provided along the way, and various approaches are discussed for each problem. Covers PeopleSoft version 7.5 and SQR versions up to 4.2.3. It also highlights the differences between versions 3.x and 4.x.


Author Notes

Galina Landres, a 20-year professional and PeopleSoft specialist, has developed IS solutions for Waldenbooks, Entex, Coopers and Lybrand and Seagram. Vlad Landres has 25-years' experience with systems design and programming for the banking and insurance industries. Their consulting company is SQRLand.


Table of Contents

prefacep. xix
special thanksp. xxi
about the authorsp. xxii
about the cover illustrationp. xxiii
downloading examplesp. xxi
introductionp. xxiv
Part 1 SQR basicsp. 1
1 Introducing SQRp. 3
1.1 SQR architecturep. 4
1.2 Database supportp. 4
1.3 Operating system supportp. 6
1.4 What's new in the recent releasesp. 6
2 Structured Query Languagep. 9
2.1 Relational database modelp. 10
2.2 Simple Select statementsp. 12
2.3 Using relational and Boolean operators in the Where clausep. 15
2.4 Aggregate functionsp. 17
2.5 Subqueriesp. 20
2.6 Table joinsp. 20
2.7 Table updatesp. 22
2.8 Referential integrityp. 25
2.9 Performance considerationsp. 27
3 Getting startedp. 29
3.1 Building your first SQR programp. 30
3.2 SQR Dialog Boxp. 31
3.3 SQR outputp. 34
3.4 Adding more complexityp. 35
4 SQR data elements and data manipulationsp. 38
4.1 SQR columns, variables, and literalsp. 39
4.2 Predefined SQR variablesp. 41
4.3 Working with datesp. 43
4.4 Manipulating data elementsp. 44
Arithmetic commandsp. 45
The Move commandp. 45
String manipulation commandsp. 47
The Let commandp. 49
4.5 Built-in functions in SQRp. 52
Numeric functionsp. 53
File-related functionsp. 53
Date functionsp. 54
String functionsp. 56
Miscellaneous functionsp. 57
5 Basic program structurep. 59
5.1 SQR pagep. 60
5.2 How SQR processes the source programp. 61
5.3 Five sections of an SQR programp. 62
5.4 Program sectionp. 62
5.5 Setup sectionp. 63
5.6 Heading sectionp. 65
5.7 Footing sectionp. 67
5.8 Procedure sectionp. 68
6 Working with data from a databasep. 73
6.1 The Select paragraphp. 74
6.2 How to reference selected columns and SQL expressionsp. 79
6.3 Explicit and implicit printingp. 79
6.4 Selecting data from multiple tablesp. 82
6.5 Using the Load-Lookup and Lookup commands to improve performancep. 83
7 Taking full advantage of SQLp. 89
7.1 Using the SQL paragraph in the procedure sectionp. 90
7.2 DML vs. DDL statementsp. 93
7.3 Using the SQL paragraph in the Setup Sectionp. 94
8 Loops and decision logic in SQRp. 96
8.1 Logical expressionsp. 97
8.2 Operands in logical expressionsp. 97
8.3 Relational, string, and numeric operators in logical expressionsp. 98
8.4 Functionsp. 99
8.5 Using If ... [Else] operatorsp. 99
8.6 The Evaluate statement in conditional processingp. 100
8.7 Using the While command in loopsp. 102
9 Enhancing your SQR reportp. 105
9.1 Using the Print commandp. 106
9.2 Formatting your outputp. 109
9.3 Using edit masksp. 110
Text format masksp. 110,
Numeric format masksp. 111
Date and time format masksp. 112
9.4 More about edit masksp. 114
9.5 Using the Position commandp. 115
9.6 Controlling the vertical spacingp. 116
9.7 Controlling the horizontal spacingp. 118
Part 2 Advanced features of SQRp. 121
10 Using break logic in your SQR programp. 123
10.1 Using the On-Break option of the Print commandp. 125
10.2 Using Level qualifiers for multiple level breaksp. 128
10.3 Using procedures when breaks occurp. 130
10.4 Using the Save qualifier of the On-Break optionp. 135
10.5 Using Print=Never option in the Print commandp. 138
10.6 Controlling page breaksp. 141
11 Run-time and compile-time variablesp. 147
11.1 SQR bind variablesp. 148
11.2 Substitution variablesp. 150
11.3 Dynamic query variablesp. 154
11.4 More about dynamic query variablesp. 157
12 Working with arraysp. 163
12.1 SQR arraysp. 164
12.2 How to create an arrayp. 165
12.3 Placing data into arraysp. 167
12.4 Initializing arraysp. 169
12.5 Retrieving data from arraysp. 169
12.6 Performing arithmetic operations on elements of an arrayp. 170
12.7 Sorting array elementsp. 176
12.8 Searching data in arraysp. 179
13 Creating multiple reportsp. 184
13.1 Defining multiple reportsp. 185
13.2 Handling multiple reports in one programp. 188
13.3 An example of a multiple report programp. 189
13.4 Output files in SQR programs with multiple reportsp. 194
14 Creating SQR Portable Filesp. 198
14.1 How to create an SPF filep. 199
14.2 Advantages of SPF Filesp. 200
14.3 Using the SQR Viewerp. 201
14.4 Converting SPF files to printer-specific filesp. 203
15 Generating lettersp. 206
15.1 Using the Document paragraph to create form lettersp. 207
15.2 Document markersp. 212
15.3 Using variable length sectionsp. 218
16 Using graphicsp. 224
16.1 Declaring a business chartp. 225
16.2 Creating an arrayp. 227
16.3 Printing a chartp. 228
16.4 Using images in reportsp. 234
16.5 Printing bar codesp. 236
16.6 Drawing boxes and solid linesp. 238
17 Working with flat filesp. 243
17.1 Files in SQRp. 244
17.2 Using the input/output operations in SQRp. 244
Opening a filep. 244
Closing a filep. 246
Reading from a filep. 246
Writing to a filep. 250
17.3 Different techniques for creating flat file outputp. 252
Using input/output commandsp. 252
Creating comma-delimited file outputp. 254
Using the Print command to create a flat filep. 255
17.4 Using flat files to import data into your databasep. 257
17.5 Using operating system commands to sort filesp. 260
18 Interacting with operating systems and other applicationsp. 264
18.1 Executing an SQR program from the command linep. 265
SQR command line flagsp. 265
SQR command line argumentsp. 268
Using the argument filesp. 268
18.2 Executing a pre-compiled SQR programp. 269
18.3 Executing your SQR programs in batch modep. 271
18.4 Issuing operating system commands from an SQR programp. 271
18.5 Calling external programs from SQRp. 275
Calling a Pro-Cobol program from an SQR program under UNIXp. 275
Calling an SQR program from another SQR program under UNIXp. 277
Calling a PeopleSoft Cobol program from SQRp. 278
18.6 Calling SQR from other programsp. 281
Using SQR APIp. 281
Using PeopleCode to schedule an SQR program execution from PeopleSoftp. 282
18.7 Linking with a user function written in "C"p. 284
Creating a user functionp. 285
Integrating a user function with SQRp. 286
Adding a function prototypep. 286
Adding an entry to the Userfuncs arrayp. 287
Adding your "C" function to Ufunc.cp. 288
Re-linking SQR and testing your new functionp. 288
Adding user-defined functions in Windows NTp. 289
19 Internet enablingp. 291
19.1 SQR and the Internetp. 292
19.2 No program code changesp. 292
19.3 Defining a title and background imagep. 294
19.4 Adding more HTML featuresp. 297
HTML tabular proceduresp. 299
HTML heading proceduresp. 305
HTML hypertext link proceduresp. 307
19.5 How an SQR program accepts parameters from the Internetp. 316
20 Debugging techniquesp. 321
20.1 Using the Show and Display commandsp. 322
20.2 Using conditional compiler statementsp. 323
The #Debug commandp. 323
Other conditional compiler directivesp. 324
20.3 Using SQR command line flags to enable SQR debugging informationp. 326
21 Good programming practicesp. 329
21.1 SQR and the year 2000 problemp. 330
21.2 Going globalp. 333
21.3 Creating platform-independent programsp. 338
21.4 Database-independent programsp. 341
21.5 Using the top-down approachp. 344
21.6 Local procedures vs. global proceduresp. 345
21.7 Handling error conditionsp. 346
21.8 Other useful suggestionsp. 347
Part 3 SQR and PeopleSoftp. 351
22 Running SQR in PeopleSoft applicationsp. 353
22.1 SQR and PeopleSoftp. 354
22.2 A high-level viewp. 355
22.3 PeopleSoft objectsp. 356
22.4 Selecting a menup. 357
22.5 Run controlp. 358
22.6 The Process Schedulerp. 361
22.7 The Process Monitorp. 363
Deleting, canceling, or putting on hold report requestsp. 365
Viewing the report outputp. 366
22.8 More about Run Control recordsp. 368
23 Attaching an SQR program to PeopleSoft objectsp. 374
23.1 Behind the scenesp. 375
23.2 Selecting a Run Control recordp. 376
23.3 Selecting a Run Control panelp. 377
23.4 Different methods of searching for a Run Control panelp. 382
23.5 Creating a Panel Groupp. 386
23.6 Selecting a menu for your reportp. 390
23.7 Granting security access to a new menu itemp. 394
23.8 Creating a Process Definition for your programp. 397
The Process Definitions panelp. 400
Process Definition Optionsp. 402
Panel Transfersp. 404
23.9 Placing the program into the right directoryp. 404
23.10 Testing your Process Definitionp. 406
24 Making an SQR program API Awarep. 410
24.1 Using PeopleSoft-delivered SQC filesp. 411
24.2 Incorporating SQC files into your programp. 412
24.3 Testing your changesp. 418
24.4 Communicating errors back to the Process Schedulerp. 420
25 Accepting input parameters from PepleSoft online panelsp. 424
25.1 Using application-specific SQC files to obtain input parametersp. 425
How the Temporary Employees program accepts its input parametersp. 425
Changing your SQR program to accept from and thru dates as input parametersp. 428
Adding unique input parameters to your SQR programp. 431
25.2 Creating your own Run Control records and panelsp. 432
Building a custom Run Control panelp. 436
Modifying the panel groupp. 438
Restoring the operator's securityp. 439
25.3 Creating your own SQC filesp. 439
Creating an SQC file to select parameters from the Run Control recordp. 440
Creating an SQC file to format selected input parametersp. 441
25.4 Changing your SQR program to accept parameters from the Run Control recordp. 442
25.5 Testing your SQR programp. 445
26 Working with effective-dated tablesp. 450
26.1 Understanding effective-dated recordsp. 451
26.2 Multiple records with the same effective datep. 452
26.3 Different techniques of selecting data from effective-dated tablesp. 453
Selecting the current data rowp. 453
Selecting the current data row from a table which includes multiple records with the same effective datesp. 456
Using the Loops parameter in the select paragraph to limit the number of selected rowsp. 457
Selecting the top row from an effective-dated tablep. 459
Selecting the current and prior rowsp. 460
Using Exists in the Where clause to check for prior rowsp. 466
26.4 Other frequently used operations with effective-dated tablesp. 467
Identifying orphan rowsp. 467
Calculating date intervals between eventsp. 468
Calculating time difference in years since employee's last promotionp. 468
Producing an employe fiftth anniversary listp. 472
appendix A Sample Databasep. 475
appendix B SQR Command Line Flagsp. 483
appendix C Built-in Functionsp. 490
appendix D SQR Command Syntaxp. 507
bibliographyp. 562
indexp. 565