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
:
- 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
- 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
- Using the SQL Edit session
- Scrolling the Row Selection Criteria field
- Expanding the Row Selection Criteria field
Related references