PeopleTools 8.4: SQR for PeopleSoft Language Reference

1 PeopleTools : SQR for PeopleSoft Language Reference PeopleTools : SQR for PeopleSoft Language Reference SKU Tr84 SQL-B 0302 PeopleBooks Contributors: Teams from PeopleSoft Product Documentation and Development. Copyright 2002 PeopleSoft , Inc. All rights reserved. Printed in the United States. All material contained in this documentation is proprietary and confidential to PeopleSoft , Inc. (" PeopleSoft "), protected by copyright laws and subject to the nondisclosure provisions of the applicable PeopleSoft agreement. No part of this documentation may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, including, but not limited to, electronic, graphic, mechanical, photocopying, recording, or otherwise without the prior written permission of PeopleSoft . This documentation is subject to change without notice, and PeopleSoft does not warrant that the material contained in this documentation is free of errors.

2 Any errors found in this document should be reported to PeopleSoft in writing. The copyrighted software that accompanies this document is licensed for use only in strict accordance with the applicable license agreement which should be read carefully as it governs the terms of use of the software and this document, including the disclosure thereof. PeopleSoft , the PeopleSoft logo, PeopleTools , PS/nVision, PeopleCode, PeopleBooks,PeopleTalk, and Vantive are registered trademarks, and "People power the internet." and Pure Internet Architecture are trademarks of PeopleSoft , Inc. All other company and product names may be trademarks of their respective owners. The information contained herein is subject to change without notice. PeopleSoft PROPRIETARY AND CONFIDENTIALCONTENTS iiiContentsSQR for PeopleSoft Language Reference PrefaceAbout This ixBefore You ixPeopleSoft Application xRelated Documentation .. xHard-copy Documentation.

3 XiPeopleBooks Standard Field xiTypographical Conventions and Visual xiiiPage and Panel Introductory xivComments and 1 Understanding SQR for PeopleSoftUnderstanding SQR for PeopleSoft 1-1 Understanding 1-1 SQR Program Structure .. 1-2 SQR Syntax 1-3 Rules for Entering SQR Commands .. 1-3 Understanding the SQR Command Line .. 1-4 SQR Command-Line Arguments .. 1-4 SQR Command-Line Flags .. 1-5 Understanding SQR Data Elements .. 1-13 Columns .. 1-13 Variables .. 1-14 Literals .. 1-18 Viewing Sample Reports .. 1-18 Chapter 2 SQR Command ReferenceADD .. 2-2 ALTER-COLOR-MAP .. 2-2 ALTER-CONNECTION .. 2-13 SQR FOR PeopleSoft Language REFERENCECONTENTS iv PeopleSoft PROPRIETARY AND CONFIDENTIALALTER-REPORT .. 2-15 ARRAY-ADD, ARRAY-DIVIDE, ARRAY-MULTIPLY, 2-20 BEGIN-EXECUTE .. 2-24 BEGIN-HEADING .. 2-30 BEGIN-SETUP .. 2-35 BREAK .. 2-40 CALL, CALL SYSTEM .. 2-40 CLEAR-ARRAY .. 2-46 CLOSE.

4 2-46 COLUMNS .. 2-47 COMMIT .. 2-48 CONCAT .. 2-49 CONNECT .. 2-50 CREATE-ARRAY .. 2-54#DEBUG .. 2-65 DECLARE-CONNECTION .. 2-66 DECLARE-IMAGE .. 2-69 DECLARE-PRINTER .. 2-76 DECLARE-PROCEDURE .. 2-85 DECLARE-TOC .. 2-87 DECLARE-VARIABLE .. 2-89# 2-92 DISPLAY .. 2-93 DIVIDE .. 2-97DO .. 2-98#ELSE .. 2-99 ELSE .. 2-100 SQR FOR PeopleSoft Language REFERENCEPEOPLESOFT PROPRIETARY AND CONFIDENTIALCONTENTS vENCODE .. 2-100 END-DECLARE, END-DOCUMENT, END-EVALUATE, END-FOOTING, END-HEADING .. 2-101#END-IF, # 2-102 END-IF .. 2-102 END-PROCEDURE, END-PROGRAM, END-SELECT, END-SETUP, END-SQL, END-WHILE, END-EXECUTE .. 2-102 EVALUATE .. 2-103 EXECUTE (SYBASE and MS-SQL Server).. 2-108 EXTRACT .. 2-109 FIND .. 2-111 GET-COLOR .. 2-113 GOTO .. 2-115 GRAPHIC BOX, GRAPHIC HORZ-LINE, GRAPHIC 2-115#IF .. 2-118IF .. 2-120#IFDEF .. 2-121# 2-122#INCLUDE .. 2-123 LAST-PAGE .. 2-126 LET .. 2-127 LOAD-LOOKUP.

5 2-158 LOOKUP .. 2-162 LOWERCASE .. 2-163 MBTOSBS .. 2-164 MULTIPLY .. 2-169 NEW-REPORT .. 2-170 NEXT-COLUMN .. 2-171 NEXT- listing .. 2-172 OPEN .. 2-176 POSITION .. 2-177 PRINT .. 2-178 PRINT-BAR-CODE .. 2-197 PRINT-CHART .. 2-200 PRINT-DIRECT .. 2-208 SQR FOR PeopleSoft Language REFERENCECONTENTS vi PeopleSoft PROPRIETARY AND CONFIDENTIALPRINT-IMAGE .. 2-209 PUT .. 2-210 READ .. 2-212 ROLLBACK .. 2-215 SBTOMBS .. 2-215 SECURITY .. 2-216 SET-COLOR .. 2-217 SET-GENERATIONS .. 2-219 SET-LEVELS .. 2-220 SHOW .. 2-220 STOP .. 2-225 STRING .. 2-226 SUBTRACT .. 2-227 TOC-ENTRY .. 2-229 UPPERCASE .. 2-230 USE .. 2-231 USE-PRINTER-TYPE .. 2-232 USE-PROCEDURE .. 2-234 WHILE .. 2-235 WRITE .. 2-237 Chapter 3 Generating HTML OutputHTML General Purpose 3-1 HTML Heading 3-5 HTML Highlighting Procedures .. 3-7 HTML Hypertext Link 3-10 HTML List Procedures .. 3-11 HTML Table Procedures .. 3-16 Chapter 4 Invoking SQR ExecuteRunning SQR Execute.

6 4-1 SQR Execute 4-1 SQR FOR PeopleSoft Language REFERENCEPEOPLESOFT PROPRIETARY AND CONFIDENTIALCONTENTS viiChapter 5 Using SQR PrintUnderstanding SQR Print .. 5-1 Generating Output from the Command 5-1 SQR Print Command-Line 5-2 Generating Output in Microsoft Windows .. 5-5 Chapter 6 Avoiding Older SQR CommandsUnderstanding Older SQR 6-1 Older SQR Command Details .. 6-2 DATE-TIME .. 6-2 DECLARE 6-4 DECLARE PROCEDURE .. 6-10 DOLLAR-SYMBOL .. 6-11 GRAPHIC 6-13 MONEY-SYMBOL .. 6-14NO-FORMFEED .. 6-16 PAGE-SIZE .. 6-16 PRINT ..CODE .. 6-17 PRINTER-DEINIT .. 6-18 Chapter 7 Using SQR Sample ProgramsUnderstanding SQR Samples .. 7-1 SQR Sample 7-1 Chapter 8 Using the FileInstallation of .. 8-1[Default-Settings] Section .. 8-2 Processing-Limits Section .. 8-6[Environment: environment] Sections .. 8-8[Locale:locale-name] 8-9[Fonts] 8-11 Adding [Fonts] entries .. 8-12 Specifying Character Sets in 8-12[HTML-Images] Section.

7 8-12 SQR FOR PeopleSoft Language REFERENCECONTENTS viii PeopleSoft PROPRIETARY AND CONFIDENTIAL[PDF Fonts] Section .. 8-13[Enhanced-HTML] Section .. 8-14[Colors] Section .. 8-14 Chapter 9 Understanding SQR MessagesUnnumbered Messages .. 9-1 Numbered Messages .. 9-3 Glossary Index PeopleSoft PROPRIETARY AND CONFIDENTIALPREFACE ixSQR for PeopleSoft Language Reference Preface This Reference describes and demonstrates the structure, command set, and syntax of the Structured Query Report (SQR) Language . It also provides a directory to the library of sample SQR programs, an overview of the SQR initialization file, and a listing of SQR messages. This Reference is intended for SQR and SQL developers who must report on data from a wide range of enterprise Data Sources. Before using this Reference , familiarize yourself with the Data Sources from which you are reporting and the connectivity between those Data Sources and your operating system.

8 The About This PeopleBook section contains general product line information, such as related documentation, common page elements, and typographical conventions. This book also contains a glossary with useful terms that are used in PeopleBooks. SeePeopleSoft This PeopleBook This book provides you with the information that you need for implementing and using PeopleTools applications. Complete documentation for this release is provided on the CD-ROM Your access to PeopleSoft PeopleBooks depends on which PeopleSoft applications you've licensed. You may not have access to all of the PeopleBooks. This section contains information that you should know before you begin working with PeopleSoft products and documentation, including PeopleSoft -specific documentation conventions, information specific to each PeopleSoft product line, and information on ordering additional copies of our documentation. Before You Begin To benefit fully from the information covered in this book, you should have a basic understanding of how to use PeopleSoft applications.

9 We recommend that you complete at least one PeopleSoft introductory training course. You should be familiar with navigating the system and adding, updating, and deleting information by using PeopleSoft windows, menus, and pages. You should also be SQR FOR PeopleSoft LANGUAGEREFERENCEPREFACE xPEOPLESOFT PROPRIETARY AND CONFIDENTIAL comfortable using the World Wide Web and the Microsoft Windows or Windows NT graphical user interface. Because we assume that you already know how to navigate the PeopleSoft system, much of the information in these books is not procedural. That is, these books do not typically provide step-by-step instructions on using tables, pages, and menus. Instead, we provide you with the information that you need to use the system most effectively and to implement your PeopleSoft application according to your organizational or departmental needs. PeopleBooks expand on the material covered in PeopleSoft training classes.

10 PeopleSoft Application Fundamentals Each PeopleSoft application PeopleBook provides implementation and processing information for your PeopleSoft database. However, there is additional, essential information describing the setup and design of your database contained in a companion volume of documentation called PeopleSoft Application Application Fundamentals contains important topics that apply to many or all PeopleSoft applications across each product line. Whether you are implementing only one PeopleSoft application, some combination of products within a product line, or an entire PeopleSoft system, you should be familiar with the contents of this central PeopleBook. It contains fundamental information such as setting up control tables and administering security. The PeopleSoft Applications Fundamentals PeopleBook contains common information pertinent to all applications in each product line, such as defining general options.