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).

      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

• Example 2 (Changing the SQL statement)
• Executing a SELECT statement after you have built it using prototyping