Example 2 (Changing the SQL statement)

Having built the SQL statement in the previous exercise to show all rows of the table PARTS (see Advanced SELECT prototyping: the completed statement (example 1)), suppose you want to change this to eliminate all but one of each set of duplicate rows:

  1. Move the cursor under the word ALL in the generated statement.
  2. Press Enter. The syntax for the SELECT clause is displayed. 
      Process   Options   Utilities   Help
 ______________________________________________________________________________
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: select clause                        Status: statement complete

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


 SELECT ALL * 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
  3. Move the cursor to the keyword DISTINCT.
  4. Press Enter. DISTINCT replaces ALL in the built SELECT statement.
    Figure 1. Advanced: the completed statement (example 2)
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)            Advanced SELECT Prototyping

 Prototyping: select clause                        Status: statement complete

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


 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
The panel shows the completed SELECT statement: 
SELECT DISTINCT * FROM PARTS

Related tasks