PICK 2 – Reports and Stored Procedures Course 410 by ja2304

VIEWS: 0 PAGES: 44

									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 ken.hall@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 NUMBER CUST.SAMPLE CITY/STATE/ZIP D2/ SERVICE DATE ADDRESS LINE ONE ADDRESS LINE TWO ZIP CODE 6R M

@ID D CITY.STATE.ZIP S
SERVICE.DATE A

0 0 A;N(CITY):", ":N(STATE):" ":N(ZIP) 1 2 3 4
5 6 7

10L 30L
8L

S M
M

NAME ADDRESS.1 ADDRESS.2
CITY STATE ZIP

A A A
A A A

25L 25L 25L
15L 2L 10L

M M M
M M M

Press any key to continue...

LIST & SORT command syntax
• LIST <filename>
– „<recordid1>‟„<recordid2>‟ …
• Record IDs are enclosed in single quotes

– WITH <dictitem> = “<value1>” “<value2>”…
• Also #, >, <, <=, >=, GT, LT, LE, EQ • Field values are enclosed in double quotes

– BY <dictitem> – BY-DSND <dictitem> – DICT items specify column of output
• <output1> <output2> ….

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 CUST.SAMPLE NAME..................... CITY........... STATE 1002 1005 1008 1017 1011 1014 1020 1041 1023 1029 Alfreds Futterkiste Around the Horn Blondel pere et fils Consolidated Holdings Bottom-Dollar Markets Centro comercial Moctezum a Du monde entier Island Trading Familia Arquibaldo Franchi S.p.A. Berlin London Strasbourg Tsawassen Mexico D.F. Nantes GERMA NY 1

BC

Sao Paulo Torino

SP

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 1010 1020 1025 1028 1044 1081 1092 1093 1003 1004 1014
1065 1088

Blondel pere et fils Bon app' Du monde entier Folies gourmandes France restauration La corne d'abondance Specialites du monde Victuailles en stock Vins et alcools Chevalier Ana Trujillo Emparedados y helados Antonio Moreno Taqueria Centro comercial Moctezum a Pericles Comidas clasicas Tortuga Restaurante

Strasbourg Marseille Nantes Lille Nantes Versailles Paris Lyon Reims Mexico D.F. Mexico D.F. Mexico D.F.
Mexico D.F. Mexico D.F.

France France France France France France France France France Mexico Mexico Mexico
Mexico 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 Oct 2006 PAGE 1 CUST.SAMPLE NAME..................... CREDIT.... LIMIT..... 01

1119 1118

Ajax Computer Electronics ABC Electronics Corp

15,000.00 10,000.00

2 records listed.

Compound Selection Criteria
• WITH <fieldname1> = “something” AND WITH <fieldname2> = “somethingelse” • WITH <field1> # “something” OR WITH <field2> = “anotherthing”
– OR is optional and implied if missing

• Any number of WITH clauses can be added up to length of input buffer • Any number of values in quotes can be added after the equals sign

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 PAGE 1 CUST.SAMPLE NAME..................... COUNTRY... 1002 1005 1119 1003 1004 1118 Alfreds Futterkiste Around the Horn Ajax Computer Electronics Ana Trujillo Emparedados y helados Antonio Moreno Taqueria ABC Electronics Corp Germany UK Mexico Mexico USA 01 Oct 2006

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 1005 1119 1003

Alfreds Futterkiste Around the Horn Ajax Computer Electronics Ana Trujillo Emparedados y helados 1004 Antonio Moreno Taqueria 1118 ABC Electronics Corp
6 records listed.
>

Germany UK
Mexico

Mexico USA

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 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... DET-SUPP

• • • • • • • • • • •

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 <filename> WITH … – SSELECT <filename> BY … WITH …

• Store lists with SAVE-LIST <listname> • Retrieve lists with GET-LIST <listname> • Lists are used by next LIST, SORT, other TCL commands, or BASIC programs

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 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. PAGE 1

Other List Commands
• COPY-LIST <listname>
– TO:<newlistname> - copies a list

• DELETE-LIST <listname>
– Deletes list from the system

• EDIT-LIST <listname>
– Invokes the ED on the list to allow you to modify the 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.

– – – – – – – – – – – –

----: 0001: 0002: 0003: 0004: 0005: 0006: 0007: 0008: 0009: 0010: ----:

L10 S 2 NAME

TCUST.SAMPLE;X;2;2 L 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<filename>;X;;<fieldno> correlative in DICT item specifies how to find the data • Data is formatted as specified in DICT item

Other similar commands
• LIST-ITEM <filename> <recordid>…
– lists records in internal format like CT

• SORT-ITEM <filename> <recordid> … • LIST-LABEL and SORT-LABEL
– Produces formatted mailing labels – Syntax like LIST & SORT – Added set of input parameters that specify label format output, eg. Number across, lines per label, etc. – 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< HLIST CUSTOMER NAME CITY COUNTRY H ZIP CH LPTR P X

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 <procname> to execute record “procname” in PROK file

• ED PROK <procname> to create PROC
– May also use WED or WinEdit

• Both PROCs and Paragraphs can be created in the VOC or MD

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 preprocessor

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 ken.hall@hylandtech.com 503-702-7841


								
To top