Example 1 (Building a simple SQL statement)

The following series of panels show each stage of using advanced SELECT prototyping to build an SQL statement to show all rows of the table PARTS.

  1. Select option 2 (Advanced) from the SQL Prototyping and Execution panel to display the initial Advanced SELECT Prototyping panel.
  2. Move the cursor to the fullselect fragment.
  3. Press Enter. The syntax for the full SELECT clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: full select clause                   Status: clause incomplete

                       ◄──────────────────────────────────────────┐
 ─┬─ subselect ──────┬───┬──────────────────────────────────────┬─┴─
  └─(─ fullselect ─)─┘   └─┬─ UNION ─────┬─┬─ subselect ──────┬─┘
                           └─ UNION ALL ─┘ └─(─ fullselect ─)─┘











 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
  4. Move the cursor to the subselect fragment.
  5. Press Enter. 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

    Notice that the select and from fragments are displayed on your screen in red indicating that the subsections relating to these fragments must be completed before these fragments can be resolved.

  6. Move the cursor to the select fragment.
  7. Press Enter. The syntax for the SELECT clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: select clause                        Status: clause incomplete

           ┌─ ALL ──────┐
 ─ SELECT ─┼────────────┼─┬─ * ────────────────────────────────────────┬─
           └─ DISTINCT ─┘ │ ◄─ , ────────────────────────────────────┐ │
                          └───┬─ expression ─┬───────────────────┬─┬─┴─┘
                              │              │ ┌─ AS ─┐          │ │
                              │              └─┴──────┴─ column ─┘ │
                              └─┬─ table ───────┬─ .* ─────────────┘
                                ├─ view ────────┤
                                └─ correlation ─┘


 SELECT ALL


 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 now shows the partly-built SELECT statement: 
    SELECT ALL
    This is because:
    • The keyword SELECT is a required keyword for this fragment. As you have selected this fragment, FM/Db2 has entered it into the SELECT statement.
    • ALL is a default keyword (and, in this case, what you want).
  8. Move the cursor to the asterisk (*).
  9. Press Enter. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: select clause                        Status: clause complete

           ┌─ ALL ──────┐
 ─ SELECT ─┼────────────┼─┬─ * ────────────────────────────────────────┬─
           └─ DISTINCT ─┘ │ ◄─ , ────────────────────────────────────┐ │
                          └───┬─ expression ─┬───────────────────┬─┬─┴─┘
                              │              │ ┌─ AS ─┐          │ │
                              │              └─┴──────┴─ column ─┘ │
                              └─┬─ table ───────┬─ .* ─────────────┘
                                ├─ view ────────┤
                                └─ correlation ─┘


 SELECT ALL *


 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 now shows the partly-built SELECT statement: 
    SELECT ALL *
  10. Press the Exit function key (F3). 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 ─┘










 SELECT ALL *


 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 the select fragment has now changed on your screen from red to green indicating that the subsection relating to this fragment has been completed and this fragment can be resolved.

    You now need to complete the subsection relating to the from fragment.

  11. Move the cursor to the from fragment.
  12. Press Enter. The syntax for the FROM clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: from clause                          Status: clause incomplete

         ◄─ , ──────┐
 ─ FROM ─── tabref ─┴─









 SELECT ALL * FROM


 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 now shows the partly-built SELECT statement: 
    SELECT ALL * FROM
    This is because the keyword FROM is a required keyword.

    Again, notice that the tabref fragment is displayed on your screen in red, indicating that the subsection relating to this fragment is yet to completed before this fragment can be resolved

  13. Move the cursor to the tabref fragment.
  14. Press Enter. The syntax for the table specification clause is displayed. 
      Process   Options   Utilities   Help
 ______________________________________________________________________________
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: table reference clause               Status: clause incomplete

  ─┬─┬─ table ─────┬─┬──────────┬──────────┬─
   │ ├─ view ──────┤ └─ correl ─┘          │
   │ └─ tablocref ─┘                       │
   ├─┬─────────┬─(─ fullselect ─)─ correl ─┤
   │ └─ TABLE ─┘                           │
   ├─ tabfunref ───────────────────────────┤
   ├─ itableref ───────────────────────────┤
   └─ joinedtable ─────────────────────────┘





 SELECT ALL * FROM

 Command ===> _____________________________________________________ Scroll PAGE
  F1=Help      F2=Split     F3=Exit      F4=Deselect  F5=InsRpt    F6=Execute
  F7=Backward  F8=Forward   F9=Swap     F10=PrvRpt   F11=NxtRpt   F12=Cancel
  15. Move the cursor to the table fragment.
  16. Press Enter. A panel is displayed in which you can enter a table name.
  17. Type PARTS in the panel. 
      Process   Options   Utilities   Help
 ─ ┌───────────────────────── Freeform Entry ─────────────────────────┐ ───────
 F │                                                                  │
   │ Enter a table name to be inserted in the SQL:                    │
 P │ PARTS___________________________________________________________ │ lete
   │ ________________________________________________________________ │
 ─ │ ________________________________________________________________ │
   │ ________________________________________________________________ │
   │ ________________________________________________________________ │
   │ Command ===> ______________________________________ Scroll PAGE  │
   │  F1=Help      F2=Split     F3=Exit      F7=Backward  F8=Forward  │
   │  F9=Swap     F12=Cancel                                          │
   └──────────────────────────────────────────────────────────────────┘
  └─ joinedtable ─────────────────────────┘





 SELECT ALL * FROM




 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
  18. Press the Exit function key (F3). The syntax for the table specification clause is displayed.
    Figure 1. Advanced SELECT prototyping: the completed statement (example 1)
      Process   Options   Utilities   Help
 ______________________________________________________________________________
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: table reference clause               Status: statement complete

  ─┬─┬─ table ─────┬─┬──────────┬──────────┬─
   │ ├─ view ──────┤ └─ correl ─┘          │
   │ └─ tablocref ─┘                       │
   ├─┬─────────┬─(─ fullselect ─)─ correl ─┤
   │ └─ TABLE ─┘                           │
   ├─ tabfunref ───────────────────────────┤
   ├─ itableref ───────────────────────────┤
   └─ joinedtable ─────────────────────────┘





 SELECT ALL * FROM PARTS

 Command ===> _____________________________________________________ Scroll PAGE
  F1=Help      F2=Split     F3=Exit      F4=Deselect  F5=InsRpt    F6=Execute
  F7=Backward  F8=Forward   F9=Swap     F10=PrvRpt   F11=NxtRpt   F12=Cancel
    The panel shows the completed SELECT statement: 
    SELECT ALL * FROM PARTS
    Notice that the table fragment has now changed on your screen from red to green, indicating that the subsection relating to this fragment has been completed and the fragment can be resolved. Also notice that the status indicator (Status:) shows “statement complete”.

Related tasks

Related references