On any SQL statement that specifies search criteria, you can use subqueries to reduce the amount of table data returned. A subquery is a SELECT statement that is part of the WHERE clause of another statement.
A subquery allows CA OPS/MVS to make decisions based on data values from tables other than the table currently being accessed. For example, you might want to extract an application name from the current table only if that name matches a value from a column in another table.
The following SELECT statement uses a subquery to compare data from the CURRENT_STATE and DESIRED_STATE columns of the SYSTEMS table to the contents of the CURRENT_STATE column of another table called CICS_TABLE:
ADDRESS SQL
"SELECT CURRENT_STATE, DESIRED_STATE FROM SYSTEMS",
"WHERE CURRENT_STATE = (SELECT CURRENT_STATE FROM",
"CICS_TABLE WHERE NAME = 'CICSA')"
In the above statement, the subquery defines the current state value for resource CICSA as the value which data fetched from the SYSTEMS table must match. Assuming that CICSA has a current state of UP, this statement fetches from SYSTEMS the current and desired state values from row 2 (both also UP).
A subquery must fetch only one piece of table data, except when the WHERE clause using that subquery also contains an IN keyword. Subqueries can operate on more than one column or row. For instance, the following INSERT statement copies every row from CICS_TABLE into the SYSTEMS table, so long as the column definitions for both tables are compatible and defined in the same order:
ADDRESS SQL "INSERT INTO SYSTEMS SELECT * FROM CICS_TABLE"
|
Copyright © 2014 CA.
All rights reserved.
|
|