Example of using basic SELECT prototyping

We'll use an example to show how to use basic SELECT prototyping to build a SELECT statement and then run it to see the results.

Suppose you want to list details of all employees in the table DSN8810.EMP whose:
  • Department is not E01, and
  • JOB is Manager or SALARY is at least $40,000
For each employee, you want to show the following columns from the table DSN8810.EMP (correlation name #1):
  • LASTNAME
  • FIRSTNME
  • WORKDEPT
  • JOB
  • SALARY (in ascending sequence)
and the following column from the table DSN8810.EEMP (correlation name #2):
  • BONUS

You can specify details on the secondary Basic SELECT Prototyping panel for more than one column at a time. However, for the purpose of clarity, the following steps show the details being entered one at a time for each column, and the resultant SELECT statement that FM/Db2 progressively builds (and displays at the top of the panel) after you press Enter.

Step 1

Select the column, LASTNAME, by typing S in the S entry field: 
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
s ___ _  #1 LASTNAME           VARCHAR(15)             _____________________ _
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (LASTNAME) to the SELECT clause.
SELECT #1.LASTNAME
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE ?
  ORDER BY ?

Step 2

Select the column, FIRSTNME, by typing S in the S entry field: 
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
s ___ _  #1 FIRSTNME           VARCHAR(12)             _____________________ _
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (FIRSTNME) to the SELECT clause.
SELECT #1.LASTNAME, #1.FIRSTNME
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE ?                             STNAME <> 'SMITH'
  ORDER BY ?

Step 3

To exclude employees in WORKDEPT “E01”:
  • Select the column, WORKDEPT, by typing S in the S entry field,
  • Type the expression operator <> in the Op entry field, and
  • Type E01 in the Value entry field:
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
s ___ _  #1 WORKDEPT           CHAR(3)              <> e01__________________ _
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (WORKDEPT) to the SELECT clause.
  • Adding a predicate, #1.WORKDEPT <> 'E01', to the WHERE clause.
SELECT #1.LASTNAME, #1.FIRSTNME, #1.WORKDEPT
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE #1.WORKDEPT <> 'E01'
  ORDER BY ?

Step 4

To select employees with a JOB of “Manager”:
  • Select the column, JOB, by typing S in the S entry field,
  • Type an opening parenthesis, (, in the ( entry field, and
  • Type MANAGER in the Value entry field:
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
s ___ (  #1 JOB                CHAR(8)                 manager______________ _
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (JOB) to the SELECT clause.
  • Inserting the default logical operator AND before adding a predicate, (#1.JOB = 'MANAGER', to the WHERE clause.
SELECT #1.LASTNAME, #1.FIRSTNME, #1.WORKDEPT, #1.JOB
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE #1.WORKDEPT <> 'E01' AND (#1.JOB = 'MANAGER'
  ORDER BY ?

Step 5

To also select employees with a SALARY of at least $40,000 (regardless of their JOB), and to specify that the results are to be shown in ascending sequence of SALARY:
  • Select the column, SALARY, for ascending sequence by typing A in the S entry field,
  • Type the logical operator, OR, in the LOp entry field,
  • Type the expression operator >= in the Op entry field, and
  • Type 40000 in the Value entry field.
  • Type a closing parenthesis in the ) entry field:
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
a or_ _  #1 SALARY             DECIMAL(9,2)         >= 40000________________ )
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (SALARY) to the SELECT clause.
  • Adding a predicate, OR #1.SALARY >= 40000), to the WHERE clause.
  • Adding the name of the selected column (SALARY), and the keyword ASC, to the ORDER BY clause.
SELECT #1.LASTNAME, #1.FIRSTNME, #1.WORKDEPT, #1.JOB, #1.SALARY
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE #1.WORKDEPT <> 'E01' AND (#1.JOB = 'MANAGER' OR #1.SALARY >= 40000)
  ORDER BY #1.SALARY ASC

Step 6

Select the column, BONUS (from the table DSN8810.EEMP), by typing S in the S entry field: 
S LOp ( Tab Column Name        Data Type(length)    Op Value                 )
⋮
s ___ _  #2 BONUS              DECIMAL(9,2)            _____________________ _
⋮
FM/Db2 updates the generated SELECT statement by:
  • Adding the name of the selected column (BONUS) to the SELECT clause.
SELECT #1.LASTNAME, #1.FIRSTNME, #1.WORKDEPT, #1.JOB, #1.SALARY, #2.BONUS
  FROM DSN8810.EMP #1, DSN8810.EEMP #2
  WHERE #1.WORKDEPT <> 'E01' AND (#1.JOB = 'MANAGER' OR #1.SALARY >= 40000)
  ORDER BY #1.SALARY ASC

Details entered together on the secondary Basic SELECT Prototyping panel shows how the Basic SELECT Prototyping panel is displayed if you had entered the details for all of the columns at the same time.

Note: The effect of entering details for one column at a time (as in the previous steps) can be different to when you enter details for more than one column at a time.

For example, if you enter the details as shown in Details entered together on the secondary Basic SELECT Prototyping panel, FIRSTNME is shown before LASTNAME in the generated SELECT statement.

Figure 1. Details entered together on the secondary Basic SELECT Prototyping panel
  Process   Options   Utilities   Help
────────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)              Basic SELECT Prototyping           Row 1 to 8 of 14

 SELECT ?
   FROM DSN8810.EMP #1, DSN8810.EEMP #2
   WHERE ?
   ORDER BY ?

 Row count  ALL            Number of rows to display

 Select columns (S/A/D) or enter predicates to build the SELECT statement:

 S LOp ( Tab Column Name      + Data Type(length)  + Op Value                 )
⋮
 s ___ _  #1 FIRSTNME           VARCHAR(12)             _____________________ _
⋮
 s ___ (  #1 JOB                CHAR(8)                 manager______________ _
 s ___ _  #1 LASTNAME           VARCHAR(15)             _____________________ _
⋮
 a or_ _  #1 SALARY             DECIMAL(9,2)         >= 40000________________ )
⋮
 s ___ _  #1 WORKDEPT           CHAR(3)              <> e01__________________ _
⋮
 s ___ _  #2 BONUS              DECIMAL(9,2)            _____________________ _
⋮

 Command ===> _____________________________________________________ Scroll PAGE
  F1=Help      F2=Split     F3=Exit      F4=Expand    F6=Execute   F7=Backward
  F8=Forward   F9=Swap     F10=Left     F11=Right    F12=Cancel

Related tasks

Related references