EXAMPLE:

PROGRAM make_invoice_report

PURPOSE:

GQL normally creates a program with the name "GUIDE_RUN_xxxxxx.TMP" in your SYS$SCRATCH directory. If the report is to be used often, you can give this program a name so that it can be run from outside GQL. The default file extension is ".INT".

The UNIQUE qualifier adds a unique alpha-numeric string to the program extension to guarantee that program names will be unique. For example, the command PROGRAM MAKE_INVOICE_REPORT UNIQUE causes GQL to save the INTOUCH program in a filename similar to:

6.22 REFRESH

FORMAT:

REFRESH

EXAMPLE:

REFRESH

PURPOSE:

The REFRESH command erases the screen and redisplays the current titles and column headings.

6.23 RELATE

FORMAT:

RELATE structure_1(field) TO [rel_type] structure_2(keyfield) RELATE [rel_type] structure_1(field) TO structure_2(key-field)

EXAMPLE:

RELATE line(invoice_no) TO invoice(invoice_no) RELATE invoice(custnbr) TO customer(custno) RELATE partial invoice(invoice_no) TO line(line_key)

PURPOSE:

The RELATE command sets up a lookup relationship between the primary structure (structure_1) and another structure (structure_2). Structure_2 is any structure that has not yet been related and contains information you need to extract. You must use the RELATE command so that GQL knows how to access the information in structure_2.

Here is an example where the RELATE command would be used.

You have opened the INVOICE structure which gives you the customer number (CUSTNBR). You now need the customer name (NAME) that corresponds to this customer number. The customer name is in the CUSTOMER file. To get the customer name, use the following command to relate the two structures:

RELATE invoice(custnbr) TO customer(custnbr)

For each INVOICE record, GQL uses the data in its CUSTNBR field to get the matching CUSTOMER record.

Field names do not need to be the same for the RELATE command to work. They need only contain the same type of data.

Structures MUST be related in the same order in which they were opened. For Example:

OPEN A OPEN C OPEN B RELATE A(FIELD_1) TO C(FIELD_1) RELATE C(FIELD_1) TO B(FIELD_1)

Example 6-25 Example of RELATE Command

OPEN DETAIL OPEN INVOICE OPEN CUSTOMER RELATE DETAIL(INVNBR) TO INVOICE(INVNBR) RELATE INVOICE(CUSTNBR) TO CUSTOMER(CUSTNBR) PRINT CUSTOMER(CUSTNBR), NAME, LINEID, PRODNBR, QTY, EXTPRICE GO

Example 6-26 Example of RELATE Command - Output

22-Jan-1999 Report on DETAIL Page 1 Line Item Prod Order Extended Cust# Customer name ID# Nbr Qty Price ----- ------------------------- --------- ----- ------ ---------- 21670 Anywhere Travel Service 10301-001 31150 1 2,495.00 21670 Anywhere Travel Service 10301-002 22800 2 750.00 21670 Anywhere Travel Service 10301-003 24200 1 299.00 21670 Anywhere Travel Service 10301-004 22800 1 375.00 21670 Anywhere Travel Service 10301-005 31020 1 595.00 21670 Anywhere Travel Service 10301-006 22800 2 750.00 21670 Anywhere Travel Service 10301-007 33090 2 2,990.00 21670 Anywhere Travel Service 10301-008 31150 8 19,960.00 21670 Anywhere Travel Service 10301-009 28800 2 498.00 10546 PacWest Corp 10302-001 28400 3 447.00 21670 Anywhere Travel Service 10303-001 33090 1 1,495.00 21670 Anywhere Travel Service 10303-002 24200 3 897.00 21670 Anywhere Travel Service 10303-003 31030 2 1,590.00 21138 Peabody Plumbing 10304-001 33180 1 2,095.00 21138 Peabody Plumbing 10304-002 20120 2 2,990.00

6.23.1 RELATE to a Relative File

Here is an example of how the RELATE command can be used with a RELATIVE file.

You have a TRANSACTION structure which contains transaction codes and other data. You also have the CODES structure which is a RELATIVE file and contains the transaction descriptions. As each TRANSACTION record is processed, you want to print the transaction code description from the CODES file. To get the code description, you would use the following statement to relate the two structures:

RELATE transaction(code) TO codes

When you wanted to print the description, you could use this statement:

PRINT codes(description)

GQL uses the data in the CODE field of the current TRANSACTION record to locate the appropriate CODES record and print the description.

6.23.2 RELATE Options---VAX/DBMS Structures

FORMAT:

RELATE [rel_type] struct(field) TO lookup_str SET set_name [: EXPRESSION expr_text]

EXAMPLE:

RELATE component TO part SET 'part_uses' : EXPRESSION 'owner' RELATE part TO many component SET 'part_used_on'

PURPOSE:

The two structures must each have a field with common information, such as an invoice number. The two fields do not have to have the same name. The field in the second structure (master) must be a key field. Each time you get to a new record, GQL uses the value in the first field to look up the master record in the second structure by means of the key field.

	rel_type	Type of relationship to establish
		ONE - one to one relationship
		MANY - one to many relationship using duplicate keys
		PARTIAL - one to partial key
	struct	Detail structure or synonym
	lookup_str	Master structure or synonym
	set_name	Set name to walk
	expr_text	Expression to be passed to the database engine

Please refer to the INTOUCH, Interfaces to Database Engines manual for further information about accessing VAX/DBMS data.

6.24 SAVE

FORMAT:

SAVE filename

EXAMPLE:

SAVE client_list

PURPOSE:

The SAVE command writes the preceding commands to the filename. The default file extension is .GUIDE.

After you have completed creating a GQL report or changing a previously SAVE'd report, you can save the commands for later execution by using the SAVE command.

6.25 SET COMMANDS

6.25.1 SET COMMANDS [SHORT | LONG]

FORMAT:

SET COMMANDS SHORT | LONG

EXAMPLE:

SET COMMANDS SHORT SET COMMANDS LONG

PURPOSE:

By default, commands are truncated to fit when displayed with SHOW COMMANDS. SET COMMANDS LONG allows a longer command to be displayed in the "show" box.

6.25.2 SET CUTOFF

FORMAT:

SET CUTOFF max_records

EXAMPLE:

SET CUTOFF 50

PURPOSE:

The SET CUTOFF command limits the number of records to be printed. This feature is useful if you want to test the format of a report when using a large data file.

6.25.3 SET EDITOR

FORMAT:

SET EDITOR [INTERNAL | EXTERNAL]

PURPOSE:

You can choose the editor you want to use. SET EDITOR INTERNAL allows you to use GQL's editor. SET EDITOR EXTERNAL (the default) allows you to use the VMS editor.

FORMAT:

SET GRANDTOTAL 'desc_text' [POSITION nn] [NOTOTALS]