Transcript PICK 1 - An Introduction for TUG U2U 205
PICK 2 – Reports and Stored Procedures Course 410
PICK 2 – Reports and Stored Procedures
Ken R. Hall, Ph.D.
Hyland Technology Group, Inc.
Portland, Oregon
www.hylandtech.com
503-702-7841
Speaker’s Qualifications
• • •
Ken Hall
has over 25 years experience developing applications in PICK, Universe, and Unidata.
Ken
developed applications for mail order, manufacturing, leasing, publishing, and distribution businesses.
Ken
was the I.T. Manager & software developer for a major wholesale distributor which & software developer used customized SHIMS.
Learning Objectives
•
As a result of this presentation, you will learn how to:
– Create ad-hoc reports – Create stored procedures – Format reports for display and printing – Use SELECT lists to ease reporting – Use relational data in reports – Find additional documentation for commands
Presentation Agenda
• PICK Query Language – LIST & SORT command syntax • Creating and Using Lists of Record Ids – SELECT, SAVE-LIST, and GET-LIST • Creating DICT items for special reports – Conversions for output formatting • Adding related data to reports – File Translates in DICT items • Creating Stored Procedures – PROCs and PAragraphs
My Assumptions
• Using SHIMS on PICK flavor Universe – Other flavors of PICK have slightly different commands • You know how to get to TCL and display Dictionaries • You know that Records are made up of fields or attributes that are referred to by their numeric sequence
Features of PICK Query Language
• Free-form English-like sentence structure • Automatic Report formatting • Relational and logical data presentation • Selectable output – terminal,printer,tape • Control break and sub-total capabilities • Mathematical functions prior to output • Powerful data formatting functions
What is a DICT item?
• DICT items format output for LIST & SORT • PICK DICT items have a specific format: – Field 1 – A – attribute definition or S – synonym – Field 2 – Field number of the record – Field 3 – Column Header for LIST & SORT – Fields 5&6 – Correlative Field definitions – Field 7 – Output conversion formatter (Field Def.) – Field 8 – Conversion before processing – Field 9 – Justify Output – L=left R=right T=text – Field 10 – Display Field Width
List Customer Dictionary
>LIST DICT CUSTOMER DICT CUSTOMER 02:44:54pm 01 Oct 2006 Page 1 Field......... Type & Field........ Conversion.. Column......... Output Depth & Name.......... Field. Definition... Code........ Heading........ Format Assoc..
Number CUST.NO A 0 CUSTOMER 6R M ":N(STATE):" ":N(ZIP) NUMBER @ID D 0 CUST.SAMPLE 10L S CITY.STATE.ZIP S 0 A;N(CITY):", CITY/STATE/ZIP 30L M SERVICE.DATE A 1 D2/ SERVICE 8L M DATE NAME A 2 25L M ADDRESS.1 A 3 ADDRESS 25L M LINE ONE ADDRESS.2 A 4 ADDRESS 25L M LINE TWO CITY A 5 15L M STATE A 6 2L M ZIP A 7 ZIP 10L M CODE Press any key to continue...
LIST & SORT command syntax
• LIST
Displaying field data
• By using field names in LIST or SORT, you can display data in columnar format – LIST CUSTOMER NAME CITY STATE
LIST CUSTOMER NAME CITY STATE 02:46:58pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... CITY........... STATE 1002 Alfreds Futterkiste Berlin GERMA NY 1005 Around the Horn London 1008 Blondel pere et fils Strasbourg 1017 Consolidated Holdings 1011 Bottom-Dollar Markets Tsawassen BC 1014 Centro comercial Moctezum Mexico D.F.
a 1020 Du monde entier Nantes 1041 Island Trading 1023 Familia Arquibaldo Sao Paulo SP 1029 Franchi S.p.A. Torino Press any key to continue...
Displaying Sorted Data
>SORT CUSTOMER WITH COUNTRY "Mexico" "France" BY COUNTRY BY NAME NAME CITY COUNTRY SORT CUSTOMER WITH COUNTRY "Mexico" "France" BY COUNTRY BY NAME NAME CITY COUNTR Y 03:14:37pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... CITY........... COUNTRY...
1008 Blondel pere et fils Strasbourg France 1010 Bon app' Marseille France 1020 Du monde entier Nantes France 1025 Folies gourmandes Lille France 1028 France restauration Nantes France 1044 La corne d'abondance Versailles France 1081 Specialites du monde Paris France 1092 Victuailles en stock Lyon France 1093 Vins et alcools Chevalier Reims France 1003 Ana Trujillo Emparedados Mexico D.F. Mexico y helados 1004 Antonio Moreno Taqueria Mexico D.F. Mexico 1014 Centro comercial Moctezum Mexico D.F. Mexico a 1065 Pericles Comidas clasicas Mexico D.F. Mexico 1088 Tortuga Restaurante Mexico D.F. Mexico Press any key to continue...
Displaying Records with some limit
•
List Customers with a Credit Limit: >LIST CUSTOMER WITH CREDIT.LIMIT NAME CREDIT.LIMIT
LIST CUSTOMER WITH CREDIT.LIMIT NAME CREDIT.LIMIT 07:13:31pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... CREDIT....
LIMIT.....
1119 Ajax Computer Electronics 15,000.00
1118 ABC Electronics Corp 10,000.00
2 records listed.
Compound Selection Criteria
• WITH
Displaying Records with a specified limit
•
List Customers with a Credit Limit of $10,000: >LIST CUSTOMER WITH CREDIT.LIMIT "10000" NAME CREDIT.LIMIT
LIST CUSTOMER WITH CREDIT.LIMIT "10000" NAME CREDIT.LIMIT 07:21:01pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... CREDIT....
LIMIT.....
1118 ABC Electronics Corp 10,000.00
1 records listed.
Displaying Records with a wildcard limit
>LIST CUSTOMER WITH NAME "A]" NAME COUNTRY LIST CUSTOMER WITH NAME "A]" NAME COUNTRY 07:30:14pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... COUNTRY...
1002 Alfreds Futterkiste Germany 1005 Around the Horn UK 1119 Ajax Computer Electronics 1003 Ana Trujillo Emparedados Mexico y helados 1004 Antonio Moreno Taqueria Mexico 1118 ABC Electronics Corp USA 6 records listed.
Displaying Records with ID suppressed
>LIST CUSTOMER WITH NAME "A]" CUST.NO NAME COUNTRY ID-SUPP LIST CUSTOMER WITH NAME "A]" CUST.NO NAME COUNTRY ID-SUPP 07:35:20pm 01 Oct 2006 PAGE 1 CUSTOMER NAME..................... COUNTRY...
NUMBER..
1002 Alfreds Futterkiste Germany 1005 Around the Horn UK 1119 Ajax Computer Electronics 1003 Ana Trujillo Emparedados Mexico y helados 1004 Antonio Moreno Taqueria Mexico 1118 ABC Electronics Corp USA 6 records
>
listed.
Create DICT item to count records
>ED DICT CUSTOMER CNT New record.
----: I 0001= A 0002= 0 0003= COUNT 0004= 0005= 0006= 0007= 0008= F;C1 0009= R 0010= 8 0011= Bottom at line 10.
----: FI "CNT" filed in file "DICT CUSTOMER".
Count Customers by Country
>SORT CUSTOMER BY COUNTRY BREAK-ON COUNTRY TOTAL CNT (D
• • • • • • • • • • • •
SORT CUSTOMER BY COUNTRY BREAK-ON COUNTRY TOTAL CNT DET-SUPP 07:40:16pm 01 Oct 2006 PAGE 1 CUST.SAMPLE COUNTRY... COUNT...
11 Argentina 2 Austria 2 Belgium 2 Brazil 9 Canada 3 Denmark 2 Germany 11 Press any key to continue...
Headings and Footings
• HEADING clause – A text string in quotes following the HEADING keyword – Special characters in single quotes add dynamic data • ‘P’ inserts page number • ‘D’ inserts current date • ‘T’ inserts current date and time • ‘L’ inserts a line feed • ‘B’ inserts value from BREAK-ON field • HEADING “Report of Customers ‘T’ Page ’PL’” – Yields heading with title, timestamp and page numbers on each page • Footing works same way
BREAK-ON for Subtotals
• BREAK-ON clauses added to sorted output will add lines that can display totals • SORT CUSTOMER BY COUNTRY BREAK-ON COUNTRY TOTAL SALES – Yields a report of sales by country with totals for each country and a grand total – Note the TOTAL modifier to the SALES field – TOTAL may include text for display in single quotes
Output Modifiers
• DET-SUPP – used with BREAK-ON to suppress detail lines between data changes • ID-SUPP – used to prevent display of Ids • NOPAGE – used to stop page break at end of screen with terminal output • HDR-SUPP – turns off output of header & footer • COL-HDR-SUPP – turns off output of headings, footings, and column headers • LPTR – sends report to the active printer
Storing list of records to report
• • • •
SELECT statements create lists of record ids that can be used by the next statement or stored for later use
– SELECT
Store lists with SAVE-LIST
Create & Save a List of Ids
>SELECT CUSTOMER WITH NAME "B“ 0 record(s) selected to SELECT list #0.
>SELECT CUSTOMER WITH NAME "B]“ 10 record(s) selected to SELECT list #0.
>>SAVE-LIST CUST.B
10 record(s) SAVEd to SELECT list "CUST.B".
Get and Use a List
> GET-LIST CUST.B
10 record(s) selected to SELECT list #0.
>> LIST CUSTOMER NAME LIST CUSTOMER NAME 08:01:16pm 01 Oct 2006 PAGE 1 CUST.SAMPLE NAME.....................
1008 Blondel pere et fils 1011 Bottom-Dollar Markets 1101 Bills Shoes & Gopeds 1006 Berglunds snabbkop 1009 Bolido Comidas preparadas 1105 Bakers Goods 1012 B's Beverages 1102 Bobs Used Cycles 1007 Blauer See Delikatessen 1010 Bon app' 10 records listed.
Other List Commands
• COPY-LIST
Displaying Data from another File
• In DICT items, File Translates can lookup data in another File – –
>ED DICT INV.SAMPLE NAME 19 lines long.
– – – – – – – – – – – –
----: L10 0001: S 0002: 2 0003: NAME 0004: 0005: 0006: 0007: 0008: TCUST.SAMPLE;X;2;2 0009: L 0010: 25 ----: Use Field 2 as ID to CUST.SAMPLE file and retrieve Field 2 (Name)
Displaying Data from another File
• Data in a field of a record is an ID to another File • T
Other similar commands
• LIST-ITEM
– Check the Universe documentation for usage
Creating Stored Procedures
• Long statements can be created in stored procedures for ease of use and debugging • Complex sets of statements are difficult to key in correctly so use stored procedures – For instance, multiple selects followed by a printed report • Two kinds of stored procedures – PROC – PICK stored procedures – PAragraph – Universe stored procedures
Creating PROCs
• PROCs start with PQ or PQN in first field • PROCs use input and output buffers • PROCs pass SELECT lists to the following statement • PROCs store commands after an “H” at the start of each field • PROCs can stack commands • PROCs require a P, PP, or PH to execute commands • PROCS are started from the VOC
Customer Report PROC
PQ C Report Customers with Name starting with a D HSELECT CUSTOMER WITH NAME “D]” STON HSSELECT CUSTOMER BY COUNTRY H BY NAME H ZIP CH P X LPTR < HLIST CUSTOMER NAME CITY COUNTRY
CUSTOMER Report PAragraph
PA * Report Customers with Name starting with a D SELECT CUSTOMER WITH NAME “D]” SSELECT CUSTOMER BY COUNTRY BY NAME LIST CUSTOMER NAME CITY COUNTRY ZIP * LPTR could be added to line above to send output to the printer
Creating and Executing PROCs
• SHIMS has a command to execute PROCs that are stored in the PROK file – At TCL, type
EX
record “procname” in PROK file to execute • ED PROK
PROC buffers and Input
• PROC has 2 input buffers and 2 output buffers • Data can be Input into the buffer and passed to the output buffer as needed • The 2 output buffers are used to stack commands
Getting Data into Input Buffers
• First reset the input buffers with RI • Next set the input buffer to the correct argument with S1, S2, S3, etc • Input fixed data with IH • Input data from a prompt with IP followed by the prompt character • Display data with D1, D2, D3, …, or D0
Prompt and Input Data
• PQ • C next 2 lines reset input buffer and set buffer to first arguement • RI • S1 C next line displays on screen O Enter Customer Name + C next line prompts with colon for input IP: C next line displays the input buffer D0
Passing input to output
• C Our PROC now has data in the first argument of the primary input buffer • C put statement in output buffer • HLIST CUSTOMER WITH NAME • C line below adds the quoted name • A”1 • C next line executes the PROC • P
Conditional Statements and Labels
• PROC has an IF statement • IF A1 = “Jones” G 10 – If the first input argument is “Jones” go to statement label 10 • Labels are numbers separated by a space from a valid proc function – 10 H HEADING “Sales to Jones ‘D’ Page ‘PL’” – C line below jumpt to label 20 – G 20
Terminal Commands
• You can specify when on the terminal (row,column) that you want output to display – Use T (row,col),”string” to display a string at a specified position – Use T (-1) to clear the screen – Use T (row,col), + to position the cursor at row, column for further input or display • For examples of this see MENU.FILE in SHIMS – The SHIMS menus are written in PROC built by a pre processor
Summary
• Created ad hoc reports using LIST & SORT • Explored some of the power of DICTs • Used SELECT and SSELECT to create lists for later processing • Demonstrated how to create stored procedures in PROCs or PAragraphs
Universe Documentation
• For more detail on the topics covered explore the Universe Manuals On-Line
http://www-306.ibm.com/software/data/u2/pubs/library/
• A great resource for additional information
Q & A
Thank You!
Ken R. Hall, Ph.D.
Hyland Technology Group, Inc.
www.hylandtech.com
503-702-7841