Handling repeatable items in the syntax
Some parts of the SELECT statement syntax contain repeatable items where one or more repeatable items are delimited by a repeat separator. The repeat separator for some repeat fragments is a comma (,), for some others it is a constant or an operand, and for others it is a space. If you specify more than one repeatable item, and the separator is constant, Advanced SELECT prototyping inserts the correct separator for you; otherwise, you must select the separator like any other element.
Let's take a simple example to show how you can build a fragment of the SELECT clause with repeatable items. Say you want to show certain information (specified in the rest of the SELECT statement) about a company's employees, grouped by their skill type, grade, and the year they started.
- Repeat steps 1 to 5 of the first example (see Example 1 (Building a simple SQL statement)) so that the syntax for the subselect
clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/Db2 (DFG2) Advanced SELECT Prototyping Prototyping: subselect clause Status: clause incomplete ─ select ─ from ─┬─────────┬─┬───────────┬─┬──────────┬─ └─ where ─┘ └─ groupby ─┘ └─ having ─┘ Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
- Move the cursor to the groupby fragment.
- Press Enter. The syntax for the GROUP BY clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/Db2 (DFG2) Advanced SELECT Prototyping Prototyping: group by clause Status: clause incomplete ◄─ , ────────────┐ ─ GROUP BY ─── groupingexpr ─┴─ GROUP BY Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
Notice that ZDT/Db2 shows the required keywords GROUP BY in the partly-built SQL clause on the panel.
You can see that the syntax for the GROUP BY clause consists of:- The required key words GROUP BY (shown on your screen in white),
- The repeatable item groupingexpr (shown on your screen in red), and
- A comma (,) as the repeat separator.
- Move the cursor to the groupingexpr fragment.
- Press the InsRpt function key (F5). A pop-up panel is displayed in which you can enter a column name.
- Type
SKILLTYPE
in the pop-up panel.Process Options Utilities Help ─ ┌───────────────────────── Freeform Entry ─────────────────────────┐ ─────── F │ │ │ Enter a grouping expression to be inserted in the SQL: │ P │ SKILLTYPE_______________________________________________________ │ lete │ ________________________________________________________________ │ │ ________________________________________________________________ │ ─ │ ________________________________________________________________ │ │ ________________________________________________________________ │ │ Command ===> ______________________________________ Scroll PAGE │ │ F1=Help F2=Split F3=Exit F7=Backward F8=Forward │ │ F9=Swap F12=Cancel │ └──────────────────────────────────────────────────────────────────┘ GROUP BY Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
- Press the Exit function key (F3). The column name SKILLTYPE is
added to the GROUP BY clause.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/Db2 (DFG2) Advanced SELECT Prototyping Prototyping: group by clause Status: clause complete ◄─ , ────────────┐ ─ GROUP BY ─── groupingexpr ─┴─ GROUP BY SKILLTYPE Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
Notice that, at this stage, ZDT/Db2 has not added a repeat separator to the statement. If you are only specifying one repeatable item, this is the correct syntax. However, if ZDT/Db2 detects that you are specifying more than one repeatable item (as in the following steps), it inserts the repeat separator.
- Again, move the cursor to the groupingexpr fragment.
- Press the InsRpt function key (F5) to display the pop-up panel.
- Type
EMPGRADE
in the pop-up panel. - Press the Exit function key (F3). The repeat separator
and column name EMPGRADE are added to the GROUP BY clause.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/Db2 (DFG2) Advanced SELECT Prototyping Prototyping: group by clause Status: clause complete ◄─ , ────────────┐ ─ GROUP BY ─── groupingexpr ─┴─ GROUP BY SKILLTYPE , EMPGRADE Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
- Repeat steps 8 through 11 for STARTYEAR.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/Db2 (DFG2) Advanced SELECT Prototyping Prototyping: group by clause Status: clause complete ◄─ , ────────────┐ ─ GROUP BY ─── groupingexpr ─┴─ GROUP BY SKILLTYPE, EMPGRADE, STARTYEAR Command ===> _____________________________________________________ Scroll PAGE F1=Help F2=Split F3=Exit F4=Deselect F5=InsRpt F6=Executed F7=Backward F8=Forward F9=Swap F10=PrvRpt F11=NxtRpt F12=Cancel
The panel shows the completed GROUP BY clause:GROUP BY SKILLTYPE , EMPGRADE , STARTYEAR
Related tasks