Sometimes selection criteria for one or more columns in a table cannot adequately meet your requirements. For example, you may need a combination of OR and AND logical operators, rather than combining all criteria with one. For those situations, use the SQL WHERE Clause panel to specify the WHERE clause for an SQL SELECT statement.
From the Select Tables/Views for AD panel, use the SQL primary or line command to invoke the SQL WHERE Clause panel. To indicate the desired table for the primary command, use the name as an operand or position the cursor on the name in the Table List. From the Describe Columns for AD, Specify Selection Criteria for AD, or Specify Archive Criteria panel, use the SQL primary command with no operands.
Figure: Specify SQL WHERE Clause
--------------- Enter an SQL WHERE Clause for a Table or View -----------------
Command ===> Scroll ===> PAGE
SELECT ... FROM FOPDEMO.ORDERS
Cmd Correlation Name ===> A WHERE 1 of 8
--- ------------------------------------------------------------------------
*** ********************************* TOP **********************************
___ ORDER_SALESMAN LIKE ‘SW%' and CUST_ID <‘10000'
___ or ORDER_DATE <‘2005-01-31'
___
___
___
___
___
___
Line Commands: (I)nsert, (D)elete, (R)epeat, (M)ove, (C)opy
Use the LIST COLUMNS command to add column names, if needed
Use the SQLEdit command to invoke the ISPF editor with all of its facilities
An optional correlation name can be entered to refer to the base table
For reference, the name of the table is provided in the SELECT...FROM heading near the beginning of the panel. This heading includes an ellipsis (...) following SELECT to indicate the column list, which is generally too long for the available space.
To simplify typing the table name in the SQL WHERE clause, you can enter a 1- to 128-character Correlation Name or a substitute for the fully qualified table name in the FROM clause. A correlation name is especially useful as a prefix for names of columns when the SQL references columns of the same name from different tables.
Type the desired SQL in the WHERE area of the panel. The scrollable area displays the SQL in segments of 8 lines of 72 positions each. A maximum of 425 lines is available for the SQL and documenting comments. Notation before the WHERE area indicates the number of lines and the relative position of the first displayed line. Standard DB2® conventions apply to comments; each line must begin with two hyphens (--).
You can specify an SQL WHERE clause using a column name. Use the LIST COLUMNS command to display a selection list of columns in the table. You can scroll this list and use the S line command to select a column name. The selected column name is added to the end of any text in the SQL WHERE clause.
You can specify an SQL WHERE clause using a substitution variable that has been defined on the Substitution Variable Display panel. The name of the substitution variable must be preceded by a colon (:). (For more information, see Substitution Variables.) Use the LIST SUBS command to display a selection list of substitution variables for the table. You can scroll this list and use the S line command to select a substitution variable. The selected substitution variable is added to the end of any text in the SQL WHERE clause.
Unless the SQL is applied to a Legacy Table, use standard DB2 SQL format for the SQL WHERE clause. When you type, any leading or trailing spaces on each line are maintained: only the spaces at the end of the last line of the SQL WHERE clause are deleted. To facilitate editing, a Cmd area is provided for each line. The functions and corresponding line commands are:
Use A or B to indicate the destination for a Copy or Move line command.
Use the D line command to delete lines in an SQL WHERE clause. You can also replace the clause with blanks or press the Erase EOF key for each line of text.
The following primary commands are available:
You can use the SQLEDIT command to invoke an ISPF edit session for the displayed SQL WHERE clause. All standard ISPF facilities are available. Use the ISPF COPY command to insert data from a file and edit as desired.
Use END to terminate the ISPF session and redisplay the SQL WHERE Clause panel. The edited data is inserted into the panel.
After you complete the SQL WHERE clause, use END to save the current specifications and terminate editing.
Use the SAVE command to save the SQL WHERE clause and continue editing. You may want to use SAVE periodically to validate the SQL WHERE clause.
END or SAVE validates the SQL WHERE clause before redisplaying the previous panel. The clause must be 425 lines or fewer and must be acceptable to DB2. You must resolve any errors before you can save the SQL WHERE clause and exit this panel.
When the Select Tables/Views for AD panel is redisplayed, “SQL” appears under Status to indicate that an SQL WHERE clause is specified for the table.