Row selection criteria in freeform style

To enter freeform row selection criteria, you use the Row Selection Criteria entry field at the top of the Column Selection/Edit panel. The entry field is protected and you cannot update it if:
  • It already contains a WHERE clause which was generated by column, or
  • You have used the TEDIT command to display the Column Selection/Edit panel

In this entry field, enter any valid SQL WHERE clause to specify selection criteria to select the data you want to view or edit.

For example, if you have two columns, WORKDEPT and LASTNAME, and you want to display only details of people in department A00 whose family name starts in the range A to M:

  1. Enter the following selection criteria: 
    WORKDEPT = 'A00' AND LASTNAME BETWEEN 'A%' AND 'M%'
    Alternatively, you can use the FM/Db2 reference numbers as abbreviations for the column names. For example, you can write the preceding expression as: 
    #5 = 'A00' AND #4 BETWEEN 'A%' AND 'M%'

    If you use abbreviations for column names, FM/Db2 expands them to the full column name when you press Enter. If you omit the keyword WHERE, FM/Db2 inserts it when you press Enter.

    The Column Selection/Edit panel now looks like this:
    Figure 1. Primary Column Selection/Edit panel showing row selection criteria
       Process   Options   Utilities   Help
 ────────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)               Column Selection/Edit                 Line 1 of 16
 TABLE FMNUSER.EMP
 ------- Row Selection Criteria ---- (Use SQL/PF4 for full screen edit) -------
 1 Sel: WORKDEPT = 'A00' AND LASTNAME BETWEEN 'A%' AND 'M%'             +

 Cmd Seq SHEX CL# Column name          Data type(length)  Null Default  Order A/D
                  ****  Top of data  ****
         S      1 EMPNO                CHARACTER(6)            None             
         S      2 FIRSTNME             VARCHAR(12)             None             
         S      3 MIDINIT              CHARACTER(1)            None             
         S      4 LASTNAME             VARCHAR(15)             None             
         S      5 WORKDEPT             CHARACTER(3)        Y   Null             
         S      6 PHONENO              CHARACTER(4)        Y   Null             
         S      7 HIREDATE             DATE                Y   Null             
         S      8 JOB                  CHARACTER(8)        Y   Null             
         S      9 EDLEVEL              SMALLINT            Y   Null             
         S     10 SEX                  CHARACTER(1)        Y   Null             
         S     11 BIRTHDATE            DATE                Y   Null             
         S     12 SALARY               DECIMAL(9,2)        Y   Null             
  Command ===> _____________________________________________________ Scroll PAGE
   F1=Help      F2=Split     F3=Exit      F4=SQL       F5=RFind     F6=RunTemp
   F7=Up        F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel
  2. Press the Exit function key (F3).
    FM/Db2 displays the selected rows:
    Figure 2. Example showing selected columns
      Process   Options   Utilities   Help
────────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFG2)                   Table Edit                      3 rows fetched
TABLE FMNUSER.EMP                                                  Format TABL
       EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE
       #1     #2           #3      #4              #5       #6      #7
       CH(6)  VARCHAR(12)  CH(1)   VARCHAR(15)     CH(3)    CH(4)   DATE
       PU--+> <---+----1-> -       <---+----1----> <-NF     <-->    <---+---->
000000 ****  Top of data  ****
000001 000110 VINCENZO<    G       LUCCHESI<       A00      3490    16.05.1958
000002 200010 DIAN<        J       HEMMINGER<      A00      3978    01.01.1965
000003 000010 CHRISTINE<   I       HAAS<           A00      3978    01.01.1965
000004 ****  End of data  ****








Command ===> _____________________________________________________ Scroll PAGE
 F1=Help      F2=Zoom      F3=Exit      F4=CRetriev  F5=RFind     F6=RChange
 F7=Up        F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel

Related tasks

Related references