Example 3 (Adding to the SQL statement)

Suppose now that you want to add a condition to the SQL statement in the previous exercise (see Advanced: the completed statement (example 2)) to show only rows of the table PARTS with a part number greater than 12456:

  1. Press the Exit function key (F3) to return to the syntax for the subselect clause. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: subselect clause                     Status: statement complete

 ─ select ─ from ─┬─────────┬─┬───────────┬─┬──────────┬─
                  └─ where ─┘ └─ groupby ─┘ └─ having ─┘









 SELECT DISTINCT * FROM PARTS



 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 where fragment.
  3. Press Enter. The syntax for the WHERE clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

Prototyping: where clause                         Status: clause incomplete

 ─ WHERE ─ srchcon ─










 SELECT DISTINCT * FROM PARTS WHERE



 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 has added the required keyword WHERE to the SQL statement.

  4. Move the cursor to the srchcon fragment.
  5. Press Enter. The syntax for the search condition clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: search condition clause              Status: clause incomplete

                              ◄───────────────────────────────────────────┐
 ─┬───────┬─┬─ predicate ───┬───┬───────────────────────────────────────┬─┴─
  └─ NOT ─┘ └─(─ srchcon ─)─┘   └─┬─ AND ─┬─┬───────┬─┬─ predicate ───┬─┘
                                  └─ OR ──┘ └─ NOT ─┘ └─(─ srchcon ─)─┘







 SELECT DISTINCT * FROM PARTS WHERE



 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
  6. Move the cursor to the first predicate fragment.
  7. Press Enter. The syntax for the predicate clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: predicate clause                     Status: clause incomplete

 ─┬─ basicpred ──────┬─
  ├─ quantifiedpred ─┤
  ├─ betweenpred ────┤
  ├─ distinctpred ───┤
  ├─ existspred ─────┤
  ├─ inpred ─────────┤
  ├─ likepred ───────┤
  └─ nullpred ───────┘




 SELECT DISTINCT * FROM PARTS WHERE



 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
  8. Move the cursor to the basicpred fragment.
  9. Press Enter. The syntax for the basic predicate clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: basic predicate clause               Status: clause incomplete

 ─┬─ expression ─┬─ = ──┬─┬─ expression ─────┬─────────┬─
  │              ├─ <> ─┤ └─(─ fullselect ─)─┘         │
  │              ├─ < ──┤                              │
  │              ├─ > ──┤                              │
  │              ├─ <= ─┤                              │
  │              └─ >= ─┘                              │
  │                                                    │
  │   ◄─ , ──────────┐              ◄─ , ─────────┐    │
  └─(─── expression ─┴─)─┬─ = ──┬─(─── expression ─┴─)─┘
                         └─ <> ─┘



 SELECT DISTINCT * FROM PARTS WHERE



 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
  10. Move the cursor to the expression fragment (to the left of the list of operators).
  11. Press Enter. The syntax for the expression clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: expression clause                    Status: clause incomplete

  ◄─ oper ─────────────────────────┐
 ───┬─────┬─┬─ function ──────────┬┴─
    ├─ + ─┤ ├─(─ expression )─────┤
    └─ - ─┘ ├─ constant ──────────┤
            ├─ column ────────────┤
            ├─ hostvar ───────────┤
            ├─ register ──────────┤
            ├─(─ scalarfullsel ─)─┤
            ├─ duration ──────────┤
            ├─ caseexpr ──────────┤
            ├─ castspec ──────────┤
            └─ sequenceref ───────┘

 SELECT DISTINCT * FROM PARTS WHERE



 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. Move the cursor to the column fragment.
  13. Press Enter. A pop-up panel is displayed in which you can enter a column name.
  14. Type PARTNO in the pop-up panel. 
      Process   Options   Utilities   Help
 ─ ┌───────────────────────── Freeform Entry ─────────────────────────┐ ───────
 F │                                                                  │
   │ Enter a column name to be inserted in the SQL:                   │
 P │ PARTNO__________________________________________________________ │ lete
   │ ________________________________________________________________ │
   │ ________________________________________________________________ │
 ─ │ ________________________________________________________________ │
   │ ________________________________________________________________ │
   │ Command ===> ______________________________________ Scroll PAGE  │
   │  F1=Help      F2=Split     F3=Exit      F7=Backward  F8=Forward  │
   │  F9=Swap     F12=Cancel                                          │
   └──────────────────────────────────────────────────────────────────┘
            ├─(─ scalarfullsel ─)─┤
            ├─ duration ──────────┤
            ├─ caseexpr ──────────┤
            ├─ castspec ──────────┤
            └─ sequenceref ───────┘

 SELECT DISTINCT * FROM PARTS WHERE PARTNO



 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
  15. Press the Exit function key (F3). The column name PARTNO is added to the SELECT statement.
  16. Press the Exit function key (F3). The syntax for the basic predicate clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: basic predicate clause               Status: clause incomplete

 ─┬─ expression ─┬─ = ──┬─┬─ expression ─────┬─────────┬─
  │              ├─ <> ─┤ └─(─ fullselect ─)─┘         │
  │              ├─ < ──┤                              │
  │              ├─ > ──┤                              │
  │              ├─ <= ─┤                              │
  │              └─ >= ─┘                              │
  │                                                    │
  │   ◄─ , ───────────┐              ◄─ , ──────────┐  │
  └─(─── expression ──┴─)─┬─ = ──┬─(─── expression ─┴─)┘
                          └─ <> ─┘



 SELECT DISTINCT * FROM PARTS WHERE PARTNO



 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
  17. Move the cursor to the greater than (>) symbol.
  18. Press Enter. A greater than (>) symbol is displayed in the partly-built SELECT statement.
  19. Move the cursor to the expression fragment (to the right of the list of operators).
  20. Press Enter. The syntax for the expression clause is displayed. 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: expression clause                    Status: clause incomplete

  ◄─ oper ──────────────────────────┐
 ───┬─────┬─┬─ function ──────────┬─┴─
    ├─ + ─┤ ├─(─ expression )─────┤
    └─ - ─┘ ├─ constant ──────────┤
            ├─ column ────────────┤
            ├─ hostvar ───────────┤
            ├─ register ──────────┤
            ├─(─ scalarfullsel ─)─┤
            ├─ duration ──────────┤
            ├─ caseexpr ──────────┤
            ├─ castspec ──────────┤
            └─ sequenceref ───────┘

 SELECT DISTINCT * FROM PARTS WHERE PARTNO >



 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
  21. Move the cursor to the constant fragment.
  22. Press Enter. A pop-up panel is displayed in which you can enter a constant.
  23. Type 12456 in the pop-up panel. 
      Process   Options   Utilities   Help
 ─ ┌────────────────────── Freeform Entry ───────────────────────┐ ────────────
 F │                                                             │
   │ Enter a constant to be inserted in the SQL:                 │
 P │ 12456______________________________________________________ │ lete
   │ ___________________________________________________________ │
 ─ │ ___________________________________________________________ │
   │ ___________________________________________________________ │
   │ ___________________________________________________________ │
   │ Command ===> _________________________________ Scroll PAGE  │
   │  F1=Help        F2=Split       F3=Exit        F7=Backward   │
   │  F8=Forward     F9=Swap       F12=Cancel                    │
   └─────────────────────────────────────────────────────────────┘




 SELECT DISTINCT * FROM PARTS WHERE PARTNO >



 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
  24. Press the Exit function key (F3).
    Figure 1. Advanced: the completed statement (example 3)
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: expression clause                    Status: clause incomplete

  ◄─ oper ───────────────────────────┐
 ───┬─────┬─┬─ function ───────────┬─┴─
    ├─ + ─┤ ├─(─ expression )──────┤
    └─ - ─┘ ├─ constant ───────────┤
            ├─ column ─────────────┤
            ├─ hostvar ────────────┤
            ├─ register ───────────┤
            ├─(─ scalarfullsel ─)──┤
            ├─ duration ───────────┤
            ├─ caseexpr ───────────┤
            ├─ castspec ───────────┤
            └─ sequenceref ────────┘

 SELECT DISTINCT * FROM PARTS WHERE PARTNO > 12456




 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 SELECT statement: 
    SELECT DISTINCT * FROM PARTS WHERE PARTNO > 12456

Related tasks