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.

  1. 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
 ──────────────────────────────────────────────────────────────────────────────
 FM/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
  2. Move the cursor to the groupby fragment.
  3. Press Enter. The syntax for the GROUP BY clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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 FM/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.
  4. Move the cursor to the groupingexpr fragment.
  5. Press the InsRpt function key (F5). A pop-up panel is displayed in which you can enter a column name.
  6. 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
  7. Press the Exit function key (F3). The column name SKILLTYPE is added to the GROUP BY clause. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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, FM/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 FM/Db2 detects that you are specifying more than one repeatable item (as in the following steps), it inserts the repeat separator.

  8. Again, move the cursor to the groupingexpr fragment.
  9. Press the InsRpt function key (F5) to display the pop-up panel.
  10. Type EMPGRADE in the pop-up panel.
  11. Press the Exit function key (F3). The repeat separator and column name EMPGRADE are added to the GROUP BY clause. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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
  12. Repeat steps 8 through 11 for STARTYEAR. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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
Note: If you only require one repeatable item in the fragment, after you move the cursor to the groupingexpr fragment (step 4), you can press the InsRpt function key (F5) or Enter.

Related tasks