Using advanced SQL prototyping

In this section, the following terms are used:
clause
A distinct part of the SELECT statement, such as a WHERE clause.
element
A single item within the syntax (such as a keyword, variable, reference to a fragment, or special character).
fragment
A portion of the SELECT statement syntax. Fragments can contain references to other fragments.
instance
A repeatable item that has been assigned a value. For example, the following GROUP BY clause contains three instances: 
GROUP BY COL1 , COL2 , COL3
keyword
An item that must be spelled exactly as shown. Keywords (for example, FROM) are shown in uppercase in the syntax diagram displayed on the Advanced SELECT Prototyping panel.
repeatable item
An item that can be repeated within the syntax. Repeatable items are indicated by a repeat arrow that can contain a repeat separator. For example: 

1  GROUP BY + , column
variable
An element that requires you to enter a value.

Advanced SELECT prototyping lets you develop a complex SELECT statement using a recursive set of specification panels.

Each panel displays a fragment of the SELECT statement syntax, presented in the form of a syntax diagram (in a similar way to a reference manual). You build the SELECT statement by selecting the optional fragments of the syntax you want to use, discarding those that you do not need, and entering items where required. The panels guide you through the syntax and ensure that you include all the required fragments of the syntax. Each panel shows the partly-formed SELECT statement as you progressively build it.

As you build the SELECT statement, you can return to any part of the statement to:
  • Change values.
  • Add a fragment of syntax.
  • Remove a fragment of syntax.
Note:
  1. If you have a 24-line screen, to see the SQL statement as it is being built you must remove the function key lines from your display by entering PFSHOW OFF.
  2. If the generated SQL statement extends over several lines, you can scroll forward or backward with the Forward function key (F8) and Backward function key (F7) respectively.

To display the Advanced SELECT Prototyping panel, select option 2 (Advanced) on the SQL Prototyping, Execution and Analysis panel.

The following primary commands are available when you use advanced SELECT prototyping:
  • CANCEL
  • DESELECT
  • EXECUTE
  • INSRPT
  • NEXTRPT
  • PREVRPT
  • SQL

Related tasks

Related references