Create and Execute a Data Query
A data query is tied to a particular table as opposed to a function (like selection queries). A data query is not based on any pre-existing internal SELECT statements. You can specify the columns, column order, WHERE conditions, and row order. A data query can also be used as a model to create other queries by saving it under another name.
A data query is tied to a particular tableas opposed to a function (like selection queries). A data query is not based on any pre-existing internal SELECT statements. You can specify the columns, column order, WHERE conditions, and row order. A data query can also be used as a model to create other queries by saving it under another name.
Create and Execute a Data Query
Follow these steps:
- EnterYin the Where field of a product panel whose functions support data queries and the name of an extended query (for example, editing a table in RC/Edit).
- Press Enter. The Data Query Edit panel appears:The For Table field is unique to this panel. This field displays the name of the table that is associated with the data query. For example, B0625JS.EMPTABLE.
- Complete the following fields to identify the data query, control the SQL statement information, generate test counts, and specify column information:
The panel information is updated. Any outstanding changes (including column expansion due to abbreviations) are performed and the panel reappears.
- Query NameSpecifies the name of the query, up to eight characters or leave it blank to assign a temporary name (TEMP). Temporary queries are not saved after you leave the function or table you are accessing. To save the query, enter a name.When you are editing a query and the name is changed, the creator ID used to store the query is your ID.ForCA RC/Migrator™ for DB2 for z/OS, enter a name for a data query. TEMP is not accepted.
- Description(Optional) Specifies a short description that is associated with the query.
- Confirm ReplaceIndicates whether you request a confirmation to appear before an existing query is replaced with the query being edited.
- Auto CastIndicates the default cast option for a column that is specified as a user-defined type. This option is applicable only to columns that contain an entry in the WHERE condition.
- ShareIndicates how other users can access your query.
- DefaultIndicates if the query is the default for the table or function.
- Test CountProvides a count of the number of rows that are retrieved based on the current SELECT statement when you specify Y or the COUNT primary command. Otherwise, enter N.
- View SQLShows the SQL SELECT statement in standard SQL format when you specify Y or the SQL primary command. All conditions, including the extended where, are displayed. Otherwise, enter N.
- ShrinkShows a subset of the columns that are listed in the SELECT statement section. This field is useful for reducing table columns to show only pertinent columns. The following values are valid:
- YShows any columns that have been selected, have ordering specifications, or have a Where condition.
- NShows all columns in the table.
- SShows columns that are selected.
- WShows columns with a where condition.
- OShows columns that are used in an ORDER BY clause.
- Row LimitSpecifies the maximum number of rows you want returned from the query. (This field has the same function as Row Limit on the Data Options panel.)
- SEL and Extended WhereSelects the columns that you want to include in the SELECT statement. The following values are valid:
Enter S or a number or a combination of both in either field. You can also specify the ALL, ALL NUMBER, SORT SEL, or SORT COL primary commands.For RC/Edit, you can select the column by entering F to freeze the field upon entry to the editor. Frozen columns are displayed before sequence-numbered columns and before selected columns.
- SSelects a column. By default, all columns are selected. To unselect a column, blank it out or enter the RESET primary command.
- #Selects the column order in the generated SELECT statement. For example, if you want the DEPARTMENT column to appear first (instead of NAME), type 1 in the SEL field. The numbers do not have to be in strict sequential order (for example, 1, 3, 5 is valid). Any column with a sequence number is placed in front of the other S columns. Therefore, if you number a column 99 and it is the only column with a sequence number, then it is placed first in the list
- C (Column level casting control)Casts a column to its internal source data type at the column level when a column is defined as a user-defined type and you enter Y. Column level casting overrides the current specified Auto Cast field setting. If you leave this field blank, the auto-case setting controls casting.This field is underscored for columns that are defined as a user-defined type when appropriate.
- Order By (ORD)Controls the sort order of the retrieved rows in each column. Enter a sequence number. The numbers control the sort order, with the lowest number representing the outer-most sort field. You can enter a number 1-9 followed by an A (for ascending) or D (descending). Valid examples include 1, 1A, and 6D.
- WHERE ConditionSpecifies column-specific WHERE information. The condition is applied to the column name on its left. All standard SQL syntax is supported.For more complex WHERE information that cannot be represented in the SELECT statement section, enter the WHERE condition in the EXTENDED WHERE fields.Specifies how all conditions that are listed in the WHERE Condition column are joined to each other in the AND/OR column, and specifies that all conditions must be true for the row selected.
- Press F3.The query is saved and executed. To save the query without executing it, enter the SAVE command.
Adding Extended Where Conditions
If you cannot enter the WHERE conditions using the columns in the middle of the Data Query Edit panel to create your SQL SELECT statement, you can specify EXTENDED WHERE conditions. When you mix AND/OR conditions or you use parentheses to separate conditions you must specify these conditions.
Note the following:
- You can enter up to 45 lines of free-form text for your EXTENDED WHERE condition.
- You can use column abbreviations instead of typing in column names. To enter the column abbreviation, enter :nwherenis the column number. For example, if BIRTHDAY is column 2 and you want to include this column in your query, enter :2 wherever you want the column to appear. Column Name identifies the DB2 column number and name. The column abbreviation number appears to the left of each column name. If the column number is highlighted, then the column is defined as NOT NULL WITHOUT DEFAULT. The NOT NULL indicator can be useful when using RC/Edit because you cannot insert or replicate rows if you did not select all columns that are defined as NOT NULL. Therefore, you must select the NOT NULL columns if you are choosing columns for a target table, such as within RC/Edit. You can enter the AUTOSEL primary command to select all columns with this type of definition.
- Type shows the column type. There is also a TYPE command that is used to toggle to the full DB2 type definition.
- When you press Enter, the abbreviation is automatically expanded to the full column name. The abbreviation is expanded with a column cast when the column is a user-defined type and casting is specified for the column.
- You can mix an EXTENDED WHERE with conditions in the WHERE Condition column. These conditions have assumed parentheses around them to separate them from the EXTENDED WHERE. If you are mixing conditions, you must start the EXTENDED WHERE with AND/OR.