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:
- Press the Exit function key (F3) to return to the syntax for the
subselect clause.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the where fragment.
- Press Enter. The syntax for the WHERE clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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 ZDT/Db2 has added the required keyword WHERE to the SQL statement.
- Move the cursor to the srchcon fragment.
- Press Enter. The syntax for the search condition clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the first predicate fragment.
- Press Enter. The syntax for the predicate clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the basicpred fragment.
- Press Enter. The syntax for the basic predicate clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the expression fragment (to the left of the list of operators).
- Press Enter. The syntax for the expression clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the column fragment.
- Press Enter. A pop-up panel is displayed in which you can enter a column name.
- 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
- Press the Exit function key (F3). The column name PARTNO is added to the SELECT statement.
- Press the Exit function key (F3). The syntax for the basic predicate
clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the
greater than
(>) symbol. - Press Enter. A
greater than
(>) symbol is displayed in the partly-built SELECT statement. - Move the cursor to the expression fragment (to the right of the list of operators).
- Press Enter. The syntax for the expression clause is displayed.
Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
- Move the cursor to the constant fragment.
- Press Enter. A pop-up panel is displayed in which you can enter a constant.
- 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
- Press the Exit function key (F3).
The panel shows the completed SELECT statement:Figure 1. Advanced: the completed statement (example 3) Process Options Utilities Help ────────────────────────────────────────────────────────────────────────────── ZDT/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
SELECT DISTINCT * FROM PARTS WHERE PARTNO > 12456
Related tasks