To get a list of fields in the record, enter SHOW FIELDS at the Guide prompt.

Example 5-3 Displaying Record Fields

+---------------------------------- PRODUCT -----------------------------------+ |COST REORDRQTY | |DESC | |MINSTOCK | |PRICE | |PRODNBR <--- key field | |QTYONHAND | +------------------------------------------------------------------------------+ Guide> SHOW FIELDS EXIT = Exit INTOUCH Guided Query Language HELP = Help

The key field(s) are high-lighted.

Enter SHOW COMMANDS at the Guide prompt to display a list of the commands which have been entered.

Example 5-4 Displaying Commands

+--------------------------------- Commands -----------------------------------+ |TITLE "CLIENT LIST" PRINT CLIENT(LAST) | |OPEN CLIENT PRINT CLIENT(CITY) | |INCLUDE CLIENT(ID) > "80500" PRINT CLIENT(STATE) | |SORT CLIENT(LAST) ASCENDING | |PRINT CLIENT(ID) | |PRINT CLIENT(FIRST) | +------------------------------------------------------------------------------+ Guide> SHOW COMMANDS EXIT = Exit INTOUCH Guided Query Language HELP = Help

5.6 Selecting and Sorting Records

Use the INCLUDE and EXCLUDE commands to select structure records.

Use the SORT options to sort the structure records.

5.7 Printing Fields

Guide> PRINT PRODNBR, DESC, COST

GQL displays a mock-up of the PRODUCT report:

Example 5-5 Mock Report

DD-MON-YYYY Report on PRODUCT Page: NNN Pos: 43 Purchase Prod# Product Description Cost ----- ------------------------- ----------- XXXXX XXXXXXXXXXXXXXXXXXXXXXXXX $XXX,XXX.XX Guide> EXIT = Exit INTOUCH Guided Query Language HELP = Help

5.8 To Generate the Report

Report generation messages and statistics are displayed.

After the report is generated, you are given a choice of where to print the the report. Press the [RETURN] key to select the default SCREEN option or use the arrow keys to select another option.

Example 5-6 Report Print Options

+------------------------------+ | GQL | | Report Generation Statistics | | Report on PRODUCT | | | | | | | | Records searched: 15 | | Records selected: 15 | | Records read : 0 | | Pages printed : 1 | | EST time to go : | | | +------------------------------+ +----------------- Output Options -----------------+ | Screen | Laser Printer | Exit | | Printer Port | Email | | | System Printer | Save to Disk | | +--------------------------------------------------+

When you have finished looking at the report, use the arrow keys to select the EXIT option and then press the [RETURN] key. This will return you to the Guide prompt.

5.9 Saving the Report File

This will save the report commands in a disk file under the name you selected.

5.10 Exiting GQL

The Guided Query Language commands tell GQL what to do. GQL processes the commands and generates a report. This chapter provides a detailed explanation of each of the Guided Query Language commands. Some report examples are also included. The report examples show what commands to use to create specific report information.

Synonyms

A command can have one or more synonyms. The synonym performs the same function as the command. In the following example, SORT and its synonym, ARRANGE, organize data in sequence:

SORT BY customer(custnbr)

ARRANGE BY customer(custnbr)

For information on how to create synonyms, see Chapter 7, The Guided Query Language Vocabulary File and Section A.4, Adding Synonyms.

Line Continuation

If the command is too long to fit on one line, the command can be continued on the following line(s). To continue a command line on the next line(s), end the line with an ampersand (&).

6.1 %INCLUDE

FORMAT:

%INCLUDE file_spec

EXAMPLE:

%INCLUDE check_account.inc

PURPOSE:

%INCLUDE includes a user-written routine.

The %INCLUDE command is used to load a user-written routine into the program that GQL creates to execute the requested query.

GQL will check that the file to be included does exist. GQL does NOT do any syntax checking of the code contained within the included file.

The %INCLUDE command is used to include the user-written routines specified by a SET INPUT ROUTINE command. Please refer to SET INPUT ROUTINE for further information.

The default extension for files to be included is ".INC"

6.2 APPEND

FORMAT:

APPEND [filename.ext]

EXAMPLE:

APPEND myfile.tmp

PURPOSE:

APPEND adds a file containing commands to the current list of commands, without your having to use the EDIT command. If no filename is given, you will be asked for a filename.

6.3 BREAK [ON]

FORMAT:

BREAK [ON] structure(field) [DESCRIPTION 'text' [POSITION nn]] [PAGE] [NOTOTALS]

EXAMPLE:

BREAK ON invoice(custnbr) BREAK ON prodnbr DESCRIPTION 'Extended price sub-total:' POSITION 2

PURPOSE:

Use BREAK to indicate where level breaks (breaks in categories of information) will occur in the report. For example, you might want a break to occur between product names. BREAK is used to subtotal data. BREAK is always used after SORT arranges the data in a particular order.

ADDITIONAL:

At a level break, the BREAK command prints subtotals for any column keeping totals. An extra blank line is printed. When more than one level break is given, the first level break is the highest, or most significant level, and the last level break is the lowest, or least significant level. In most cases, the break level sequence should be on the same fields and in the same order as the sort sequence.

The BREAK command instructs GQL to perform level breaks on the field specified.

The DESCRIPTION qualifier attaches text (a description) to subtotals.

The POSITION qualifier allows users to position the text string for subtotals.

The NOTOTALS qualifier suppresses the printing of totals when the level break occurs.

The PAGE qualifier starts a new page when the break occurs.

The following commands create a report which breaks on the product number.

Example 6-1 Example of BREAK Command

OPEN DETAIL SORT BY PRODNBR SORT BY LINEID INCLUDE INVNBR < 10330 EXCLUDE INVNBR = 10320 BREAK ON PRODNBR PRINT PRODNBR PRINT LINEID PRINT QTY PRINT PRICE PRINT EXTPRICE WITH TOTALS GO

Example 6-2 Example of BREAK Command - Output

21-Jan-1999 Report on DETAIL Page 1 Line Prod Item Order Selling Extended Nbr ID# Qty Price Price ----- --------- ------ ---------- ---------- 20120 10304-002 2 1,495.00 2,990.00 20120 10323-004 1 1,495.00 1,495.00 ========== 4,485.00 22600 10314-002 1 325.00 325.00 22600 10323-005 1 325.00 325.00 22600 10327-002 1 325.00 325.00 22600 10328-002 1 325.00 325.00 22600 10328-003 4 325.00 1,300.00 22600 10329-003 2 325.00 650.00 ========== 3,250.00 22800 10301-002 2 375.00 750.00 22800 10301-004 1 375.00 375.00 22800 10301-006 2 375.00 750.00 22800 10304-003 1 375.00 375.00 22800 10313-001 2 375.00 750.00 22800 10321-002 1 375.00 375.00 22800 10324-001 4 375.00 1,500.00 22800 10325-003 10 375.00 3,750.00 ========== 8,625.00

Example 6-3 Example of BREAK Command with DESCRIPTION Qualifier

OPEN DETAIL SORT BY PRODNBR SORT BY LINEID INCLUDE INVNBR < 10330 EXCLUDE INVNBR = 10320 BREAK ON PRODNBR DESCRIPTION 'Extended price sub-total:' PRINT PRODNBR PRINT LINEID PRINT QTY PRINT PRICE PRINT EXTPRICE WITH TOTALS GO

Example 6-4 Example of BREAK Command with DESCRIPTION Qualifier - Output

21-Jan-1999 Report on DETAIL Page 1 Line Prod Item Order Selling Extended Nbr ID# Qty Price Price ----- --------- ------ ---------- ---------- 20120 10304-002 2 1,495.00 2,990.00 20120 10323-004 1 1,495.00 1,495.00 ========== Extended price sub-total: 4,485.00 22600 10314-002 1 325.00 325.00 22600 10323-005 1 325.00 325.00 22600 10327-002 1 325.00 325.00 22600 10328-002 1 325.00 325.00 22600 10328-003 4 325.00 1,300.00 22600 10329-003 2 325.00 650.00 ========== Extended price sub-total: 3,250.00 22800 10301-002 2 375.00 750.00 22800 10301-004 1 375.00 375.00 22800 10301-006 2 375.00 750.00 22800 10304-003 1 375.00 375.00 22800 10313-001 2 375.00 750.00 22800 10321-002 1 375.00 375.00 22800 10324-001 4 375.00 1,500.00 22800 10325-003 10 375.00 3,750.00 ========== Extended price sub-total: 8,625.00