Row selection by column
To enter row selection criteria by column, type 1 on
the command line on the Column Selection/Edit panel and press Enter.
FM/Db2 displays the Row Selection Criteria panel.
On the line for each column for which you want to specify selection criteria, you specify an operator (like = or >) in the Op column and, in most cases, a value in the Value column.
For example, to select only employees with a surname of SMITH from a table of employees:
- On the line for the column LASTNAME, type
=in the Op column andSMITH(optionally enclosed in single quotation marks) in the Value column:Figure 1. Example 1: Specifying a simple predicate to match a single value Cmd Con ( Column name Data type(length) Op Value <-> - <---+----1----+----2----+----> <---+----1----+----> <-> <---+---- *** **** Top of data **** EMPNO CHARACTER(6) AND FIRSTNME VARCHAR(12) AND MIDINIT CHARACTER(1) AND LASTNAME VARCHAR(15) = SMITHThis predicate works well in the situation where the data in the table is stored in uppercase. However, if the data in the table is in mixed case (for example, 'Smith') or contains errors (for example, 'SmiTH'), Db2® will not return all the desired rows.
Normally, any operator that is entered in the Op column has no effect on the column name. The following two operators allow a column function to be specified:- LKL
- Results in this expression: LOWER(colname) LIKE value.
- LKU
- Results in this expression: UPPER(colname) LIKE value.
In the following example, any upper case character in the value will result in no matches, so FM/Db2 automatically converts the value entered into lower case. For more information, see The expression generated from the expression in example 2.Figure 2. Example 2: Specifying the LKL operator Cmd Con ( Column name Data type(length) Op Value <-> - <---+----1----+----2----+----> <---+----1----+----> <-> <---+----1----+----2 *** **** Top of data **** EMPNO CHARACTER(6) AND FIRSTNME VARCHAR(12) AND MIDINIT CHARACTER(1) AND LASTNAME VARCHAR(15) LKL '%SMITH%'In the following example, any lower case character in the value will result in no matches, so FM/Db2 automatically converts the value entered into upper case. For more information, see The expression generated from the expression in example 3.Figure 3. Example 3: Specifying the LKU operator Cmd Con ( Column name Data type(length) Op Value <-> - <---+----1----+----2----+----> <---+----1----+----> <-> <---+---- *** **** Top of data **** EMPNO CHARACTER(6) AND FIRSTNME VARCHAR(12) AND MIDINIT CHARACTER(1) AND LASTNAME VARCHAR(15) LKU '%smith%'You can also specify a column expression directly if required. To enter a column expression, you can either issue the EDITCOL editor primary command, or type the EC prefix command against the column name where an expression is required. After issuing these commands, the column name field changes to an input field.
Example 4: Entering a column expression and a data value with the escape character shows the use of a column expression and the '\' escape character. This example demonstrates how to display the rows where an employee was hired in March. The escape character is used to prevent FM/Db2 from attempting to validate the data in the value field according to the type. The column function MONTH returns an integer representing the month (1 - 12), whereas the data type for the column is DATE.Figure 4. Example 4: Entering a column expression and a data value with the escape character Cmd Con ( Column name Data type(length) Op Value <-> - <---+----1----+----2----+----> <---+----1----+----> <-> <---+---- *** **** Top of data **** EMPNO CHARACTER(6) AND FIRSTNME VARCHAR(12) AND MIDINIT CHARACTER(1) AND LASTNAME VARCHAR(15) AND WORKDEPT CHARACTER(3) AND PHONENO CHARACTER(4) AND MONTH(HIREDATE) = \3 - Press the Exit function key (F3).
FM/Db2 displays the Column Selection/Edit panel showing the generated row selection criteria (WHERE clause) in the Row Selection Criteria field at the top of the panel:
The results of the expressions entered in example 1 - example 4 are shown below:Figure 5. The expression generated from the expression in example 1. Process Options Utilities Help -------------------------------------------------------- FM/Db2 (DFG2) Co Command ===> TABLE DSN81010.EMP --------------------------------- Row Selection Criteria 1 Sel: WHERE "LASTNAME" = 'SMITH'Figure 6. The expression generated from the expression in example 2 Process Options Utilities Help -------------------------------------------------------- FM/Db2 (DFG2) Co Command ===> TABLE DSN81010.EMP --------------------------------- Row Selection Criteria 1 Sel: WHERE LOWER("LASTNAME") LIKE '%smith%'Figure 7. The expression generated from the expression in example 3 Process Options Utilities Help -------------------------------------------------------- FM/Db2 (DFG2) Co Command ===> TABLE DSN81010.EMP --------------------------------- Row Selection Criteria 1 Sel: WHERE UPPER("LASTNAME") LIKE '%SMITH%'Figure 8. The expression generated from the expression in example 4 Process Options Utilities Help -------------------------------------------------------- FM/Db2 (DFG2) Co Command ===> TABLE DSN81010.EMP --------------------------------- Row Selection Criteria 1 Sel: WHERE MONTH(HIREDATE) = 3 - Press Exit function key (F3)
FM/Db2 displays the selected data:
Figure 9. The data resulting from the expressions in example 1 - example 4 FM/Db2 (DFG2) Table Edit Command ===> 2 rows - End of object. 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 PUN-+> <-N-+----1-> - <-N-+----1----> <-N <--> <---+----> ****** **** Top of data **** 000001 000250 DANIEL< S SMITH< D21 0961 1969-10-30 000002 000300 PHILIP< X SMITH< E11 2095 1972-06-19Figure 10. The data resulting from the expression in example 4 FM/Db2 (DFG2) Table Edit Command ===> 3 rows - End of object. 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 PUN-+> <-N-+----1-> - <-N-+----1----> <-N <--> <---+----> ****** **** Top of data **** 000001 000200 DAVID< BROWN< D11 4501 1966-03-03 000002 000280 ETHEL< R SCHNEIDER< E11 8997 1967-03-24 000003 200280 EILEEN< R SCHWARTZ< E11 8997 1967-03-24
Continuing with the expression entered in Example 1: Specifying a simple predicate to match a single value, if you now want to select, for example, employees with a surname of SMITH or JONES, you need to modify the row selection criteria. To do this, you must return to the Row Selection Criteria panel:
- From the panel of displayed data, press the Exit function key (F3) to return to the Db2® Edit entry panel.
- Select the Edit template field and press the Exit function key (F3) to display
the Column Selection/Edit panel.
You will notice on this panel that the Row Selection Criteria field (showing your previous WHERE "LASTNAME" = 'SMITH' clause) is protected. This is because row selection criteria entered by column or as a free-form expression are not interchangeable. You can change row selection criteria that has been entered by column, by returning to the Row Selection Criteria panel. You can convert row selection criteria entered by column to a free-form expression by issuing the SQL command on the Column Selection/Edit panel, and saving the data. The conversion removes the specification by column. You cannot convert a free-form expression to a row selection criteria entered by column.
- Enter
1on the command line to display the Row Selection Criteria panel. - To select more than one value for the same column (as in this case, where you want to select
both SMITH and JONES), there are two methods you can use:
- Use multiple lines for the column:
- Type the R (Repeat) prefix command in the Cmd column to repeat the line for the column name concerned
- On the repeated line (or lines) for the column name, change the connector (in the Con
column) to
OR
by overtyping the AND withOorOR - Type the value in the Value column
Cmd Con ( Column name Op Value ) <-> - <---+----1----+----2----+----3----> <-> <---+----1----+----2----+-- - *** **** Top of data **** ⋮ AND LASTNAME = SMITH OR LASTNAME = JONES - Use the IN operator:
- On the line for LASTNAME, change the operator (from =) to IN. The IN operator lets you specify a list of values you want to select.
- In the value column, type the values you want to select. For alphanumeric columns, when you specify a list of values, each value must be enclosed in quotation marks and separated by a comma.
Cmd Con ( Column name Op Value ) <-> - <---+----1----+----2----+----3----> <-> <---+----1----+----2----+-- - *** **** Top of data **** ⋮ AND LASTNAME IN 'SMITH','JONES'
- Use multiple lines for the column:
- Press the Exit function key (F3)
twice to display the selected data. Whichever method you used to specify
row selection criteria for SMITH or JONES, the same results are achieved:
Process Options Utilities Help ──────────────────────────────────────────────────────────────────────────────── FM/Db2 (DFG2) Table Edit Template saved 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 000210 WILLIAM< T JONES< D11 0942 11.04.1979 000002 000250 DANIEL< S SMITH< D21 0961 30.10.1969 000003 000300 PHILIP< X SMITH< E11 2095 19.06.1972 000004 **** End of data **** ⋮
Continuing with this same example, if you now want to select employees with a surname of SMITH or JONES who are in department D21, you again need to modify the row selection criteria.
This time, in order to avoid any ambiguity, you may need to use parentheses to define the sequence of evaluation for the resultant WHERE clause. On the Row Selection Criteria panel, you can use the ( and ) columns to enclose groups of expressions in parentheses.
Cmd Con ( Column name Op Value )
<-> - <---+----1----+----2----+----3----> <-> <---+----1----+----2----+-- -
*** **** Top of data ****
⋮
AND ( LASTNAME = SMITH
OR LASTNAME = JONES )
AND WORKDEPT = D21
Process Options Utilities Help ──────────────────────────────────────────────────────────────────────────────── FM/Db2 (DFG2) Table Edit Template saved 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 **** 000002 000250 DANIEL< S SMITH< D21 0961 30.10.1969 000004 **** End of data **** ⋮