Example 4 (Removing part of the SQL statement)

To remove part of a SELECT statement you have built using advanced SELECT prototyping, use the Deselect function key (F4). The effect of the Deselect function key depends on which fragment of the syntax is currently active. For more information, see the description of the DESELECT command.

Suppose that you want to remove the condition you added to the SQL statement in the previous exercise (see Advanced: the completed statement (example 3)) that restricted the rows shown to those with a part number greater than 12456. This means that you need to remove the WHERE clause from the SELECT statement.

You can remove the WHERE clause by either of these methods:

  • Using the Deselect function key (F4):
    1. Place the cursor on the WHERE clause in the generated SELECT statement.
    2. Press Enter to select the WHERE clause. It changes to green, and the syntax for the WHERE clause is redisplayed.
    3. Place the cursor on the command line.
    4. Press the Deselect function key (F4).
  • Returning to the prototyping panel that contains the where fragment and deselecting it:
    Note: The following steps assume the current panel on your screen is the last panel in Example 3 as shown in Advanced: the completed statement (example 3).
    1. Press the Exit function key (F3). FM/Db2 returns you to the syntax for the basic predicate clause.
    2. Press the Exit function key (F3). FM/Db2 returns you to the syntax for the predicate clause.
    3. Press the Exit function key (F3). FM/Db2 returns you to the syntax for the search condition clause.
    4. Press the Exit function key (F3). FM/Db2 returns you to the syntax for the WHERE clause.
    5. Press the Exit function key (F3). FM/Db2 returns you to the syntax for the subselect clause. This panel contains the where fragment. 
        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 WHERE PARTNO > 23456



 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 where fragment.
    7. Press the Deselect function key (F4).
      Figure 1. Advanced: the statement with WHERE clause removed (example 4)
        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
      The panel shows the SELECT statement without the WHERE clause: 
      SELECT DISTINCT * FROM PARTS

Related tasks