You use the Basic SELECT Prototyping (primary)
panel to specify up to 15 tables to be used to construct a FROM clause
as the starting point for prototyping a SELECT statement.
Panel and field definitions
Process Options Utilities Help
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFG2) Basic SELECT Prototyping
Enter the name(s) of the table(s) from which to retrieve data:
Owner Name
#1 DSN8810 + EMP + Location
#2 DSN8810 + EEMP + Database
#3 + + Tbl spc.
#4 + +
#5 + +
#6 + +
#7 + +
#8 + +
#9 + +
#10 + +
#11 + +
#12 + +
#13 + +
#14 + +
#15 + +
Command ===>
F1=Help F2=Split F3=Exit F4=Expand F7=Backward F8=Forward
F9=Swap F10=Left F11=Right F12=Cancel
Owner
This field is optional. If specified, it is used to qualify the
name field on the same line. If omitted, the current SQL ID is used.
Name
This field is required. It specifies the name of the table to
be processed.
Location
This field is optional. If specified, it is used to qualify all
the tables entered in the Owner and Name fields. If
omitted, the current location is be used.
Database
This field is optional. If specified, all the tables entered in
the Owner and Name fields must exist in the given database.
If omitted, the tables can exist in any database at the specified
or defaulted location.
Table space
This field is optional. If specified, all the tables entered in
the Owner and Name fields must exist in the given table
space. If omitted, the tables can exist in any table space at the
specified or defaulted location, subject to any restriction imposed
by the database specification.
After you have specified
the table names on the primary Basic SELECT Prototyping panel, press
Enter to display the secondary Basic SELECT Prototyping panel.
Process Options Utilities Help
────────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFG2) Basic SELECT Prototyping Row 1 of 19
SELECT ?
FROM ?
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 )
_ ___ _ #1 EMPNO CHAR(6) __ _________________________ _
_ ___ _ #1 FIRSTNME VARCHAR(12) __ _________________________ _
_ ___ _ #1 MIDINIT CHAR(1) __ _________________________ _
_ ___ _ #1 LASTNAME VARCHAR(15) __ _________________________ _
_ ___ _ #1 WORKDEPT CHAR(3) __ _________________________ _
_ ___ _ #1 PHONENO CHAR(4) __ _________________________ _
_ ___ _ #1 HIREDATE DATE(4) __ _________________________ _
_ ___ _ #1 JOB CHAR(8) __ _________________________ _
_ ___ _ #1 EDLEVEL SMALLINT(2) __ _________________________ _
_ ___ _ #1 SEX CHAR(1) __ _________________________ _
_ ___ _ #1 BIRTHDATE DATE(4) __ _________________________ _
_ ___ _ #1 SALARY DECIMAL(9,2) __ _________________________ _
_ ___ _ #1 BONUS DECIMAL(9,2) __ _________________________ _
_ ___ _ #1 COMM DECIMAL(9,2) __ _________________________ _
_ ___ _ #2 DEPTNO CHAR(3) __ _________________________ _
_ ___ _ #2 DEPTNAME VARCHAR(36) __ _________________________ _
_ ___ _ #2 MGRNO CHAR(6) __ _________________________ _
_ ___ _ #2 ADMRDEPT CHAR(3) __ _________________________ _
_ ___ _ #2 LOCATION CHAR(16) __ _________________________ _
**** End of data ****
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
Row count
Enter a number in this field to control:
The number of rows returned in the result set when the SQL statement
is executed. Specifying 0 results in all qualifying rows being returned.
Specifying n (when n>0)
limits the number of rows returned to the lesser of: n rows,
or the number of rows in the result table.
Whether or not an "OPTIMIZE FOR n ROWS" clause is added to the SQL
statement prior to execution. Specifying n (when
n>0) results in the addition of "OPTIMIZE FOR
n ROWS" to the SQL statement prior to execution. Specifying a 0 value does
not add "OPTIMIZE FOR n ROWS" to the SQL statement. Specifying a non-zero
value may improve Db2's use of resources when executing the SQL statement.
S
A selection column in which you can specify one of the following
values for the column on that line:
S
Select. Adds the column name to the column list in the SELECT
clause.
A
Select ascending. Adds the column name to the column list in the
SELECT clause, and adds the column name with the keyword ASC to
the column list in the ORDER BY clause.
D
Select descending. Adds the column name to the column list in
the SELECT clause, and adds the column name with the keyword DESC to
the column list in the ORDER BY clause.
LOp
Logical operator to join a new predicate to previous predicates.
You can specify one of the following values for the column on that
line:
AND (the default operator)
OR
NOT (equivalent to and not)
NOR (equivalent to or not)
(
Inserts an opening parenthesis at the start of the predicate added
to the WHERE clause. Use an opening parenthesis in conjunction with
a closing parenthesis to ensure the predicate for this column is evaluated
before other (non-parenthesized) predicates.
Tab
The correlation name of the table containing the column on this
line. This is an information-only field.
Column Name
The name of the column as held in the Db2® catalog. This is an information-only field.
Data Type(length)
The Db2® data type of the
column and its length, or numeric precision and scale. This is an
information-only field.
Op
Expression operator. You can specify one of the following values
for the column on that line:
=
Equal. This is the default operator.
<>
Not equal.
>
Greater than.
<
Less than.
>=
Greater than or equal to.
<=
Less than or equal to.
IN
In set. If the value in the column is
in the specified set of values (specified in Value field),
the result of the expression is true. The IN expression operator generates
the predicate IN set in the WHERE clause.
NI
Not in set. If the value in the column
is not in the specified set of values (specified in Value field),
the result of the expression is true. The NI expression operator generates
the predicate NOT IN set in the WHERE clause.
LI
Like pattern. If the value in the column
matches the pattern (specified in Value field), the result
of the expression is true. The LI expression operator generates the
predicate LIKE pattern in the WHERE clause.
NL
Not like pattern. If the value in the
column does not match the pattern (specified in Value field),
the result of the expression is true. The NL expression operator generates
the predicate NOT LIKE pattern in the WHERE
clause.
Value
The value to be used with the expression operator (specified in
the Op field).
The format of the data you enter depends
on the expression operator:
For expression operators =, <>, >, <, >=, and <=:
For columns with an alphanumeric data type, specify a character
string, optionally enclosed in quotation marks. If you do not enclose
the string in quotation marks, FM/Db2 automatically adds the quotation marks
around the string when it adds the generated predicate to the WHEN
clause.
Examples:
SMITH
'SMITH'
SALES REP
'SALES REP'
For columns with a numeric data type, specify a numeric value.
Examples:
500
33.75
0
You can also specify a column name as a value. The column name
must be qualified with its correlation name.
Examples:
#1.WORKDEPT
#2.BONUS
Note: To enter a string that begins with
a “#”, you must enclose the string in quotation marks.
For expression operators IN and NI:
For columns with an alphanumeric data type, specify a list of
character strings. Each character string must be enclosed in
quotation marks and separated by a comma.
For columns with a numeric data type, specify a list of numeric
values. Each value must be separated by a comma and, optionally, one
or more spaces.
Examples:
101,102,103
45.5, 50.0, 65.5
You can also specify a list of column names. Each column name
must be qualified with its correlation name and be separated by a
comma. Examples:
#1.ACCT,#1PROD,#2MAINT
#1.BONUS, #2.BONUS
For the expression operator LI:
Specify a pattern (as a character string), optionally enclosed
in quotation marks.
Note: You can only specify a pattern for columns
with an alphanumeric data type.
If you do not enclose the pattern
in quotation marks, FM/Db2 automatically adds the quotation marks
around the pattern when it adds the generated predicate to the WHEN
clause.
The pattern can contain an underscore (_) to represent
any single character, or a percent sign (%) to represent a string
of zero or more characters.
All of the following examples
find the string “SMITH”:
'SMITH'
SMI
'SM%'
SMI
Specify a column name as a value. The column name must be qualified
with its correlation name.
Examples:
#1.SEX
#1.WORKDEPT
)
Inserts a closing parenthesis at the end of the predicate added
to the WHERE clause. Use in conjunction with an opening parenthesis
to ensure the predicate for this column is evaluated before other
(non-parenthesized) predicates.