Relational Data Framework Reference › SQL Statements › SELECT Statement—Extract Relational Table Data › Output from the SELECT Statement

Output from the SELECT Statement

Most SQL statements modify relational tables, and their only output is the SQLCODE variable. However, the SELECT statement returns data stored in selected rows. How this data is treated depends on the source from which you invoked SQL.

• Output From a TSO Terminal

  If you invoke a SELECT statement from a TSO terminal, SQL displays the returned column values on the terminal, one row at a time. A display line can contain up to 250 characters. To fit as much information as possible on one line, SQL either truncates some lengthy character fields to 20 bytes or, if necessary, omits several column values.

  If you need to see the complete text of a display line, you can do so through the CA OPS/MVS relational table editor.

  To look at all of the rows in a table, you could invoke a SELECT statement from foreground TSO as follows:

  OPSQL SELECT * FROM tablename
  

• Output From a TSO/E REXX Program or a TSO CLIST

  When you issue the OPSQL command processor from in a TSO/E REXX program or a TSO CLIST, the terminal receives no output. Instead, the selected values are returned as REXX or CLIST variables.

• Output From an AOF Rule or an OPS/REXX Program

  If an AOF rule or any other type of OPS/REXX program invokes a SELECT statement, SQL fetches the values from each selected row and places them into stem variables:

  • If the SELECT statement does not contain an INTO clause, the column name becomes the REXX variable stem.
  • If the SELECT or FETCH statement includes the INTO clause, the REXX variable stems are those specified on it.

  Unless you specify otherwise, SQL returns the column values in host variables. When you want to return a value into a variable or a column that has a different name, include the INTO clause in your SELECT statement. The INTO clause supplies the names of the variables where you want to store the returned values.

  For example, suppose that you want to extract from the SYSTEMS table values from the NAME and CURRENT_STATE columns, and you want to store those values in variables called SYSNAME and STATE. You would invoke a SELECT statement like the following:

  ADDRESS SQL
    "SELECT NAME, CURRENT_STATE INTO :SYSNAME :STATE",
      "FROM SYSTEMS WHERE NAME='CICS1'"
  

  This statement generates these OPS/REXX output variables:

  • SYSNAME.0, set to 1 (the number of rows matching the search criteria)
  • SYSNAME.1, set to CICS (the value in the NAME column for the selected row)
  • STATE.0, set to 1 (the number of rows matching the search criteria)
  • STATE.1, set to DOWN (the value in the CURRENT_STATE column for the selected row)
  • SQLCODE, set to 0 (indicating that the SELECT statement executed successfully)

  Use the FETCH cursor operation statement to select data one row at a time. For more information, refer to the description of the FETCH statement earlier in this chapter.

Examples: SELECT Statement

The following examples illustrate the use of the SELECT statement.

• Example 1-Selecting Data From All the Columns in a Row

  Suppose that you want to fetch the contents of only one row. If you wanted only the data for system CICS1 from the SYSTEMS table, you could use this SELECT statement in an OPS/REXX program:

  ADDRESS SQL
    "SELECT * FROM SYSTEMS WHERE NAME = 'CICS1'"
  

  The asterisk indicates that you want SQL to return data from all columns in the row where the NAME column has the value CICS1.

• Example 2-Comparing Two Columns

  When using the WHERE clause in an SQL statement, you typically compare a column against a string or host variable. However, you also can compare one column against another. For example, the SYSTEMS table contains system status information, so you can get a list of all the systems where the current state does not equal the desired state. To get this list, invoke the following SELECT statement from OPS/REXX:

  ADDRESS SQL
    "SELECT * FROM SYSTEMS WHERE CURRENT_STATE <> DESIRED_STATE"
  

• Example 3-Specifying Multiple Search Criteria

  When you need to specify multiple search criteria linked by a mixture of AND and OR operators, you may need to enclose some of the criteria in parentheses to ensure the right result. For example, to select row CICS1 or CICS2, but only when the current state of the CICS1 or CICS2 system is DOWN, you could invoke the following statement from OPS/REXX:

  ADDRESS SQL
    "SELECT * FROM SYSTEMS WHERE CURRENT_STATE = 'DOWN'",
      "AND (NAME = 'CICS1' OR NAME = 'CICS2')"
  

  If you leave out the parentheses, row CICS1 would be selected when its current state is down, but row CICS2 would be selected regardless of its state.

• Example 4-Retrieving Data From Selected Columns

  In the preceding examples, the SELECT statements fetched data from all columns of the selected rows because an asterisk was specified as the columnlist operand. However, you can instruct CA OPS/MVS to fetch data only from certain columns. The following SELECT statement, which is invoked from an OPS/REXX program, asks CA OPS/MVS to return only the data stored in the CURRENT_STATE column.

  ADDRESS SQL
    "SELECT CURRENT_STATE FROM SYSTEMS WHERE NAME = 'CICS'"
  

• Example 5-Inserting Retrieved Data Into a REXX Variable

  When instructing CA OPS/MVS to fetch data from a certain column in a certain row, you can insert the retrieved data into a REXX variable. To retrieve the value FIXIMS and place it in the REXX variable RECOV, you could use this REXX statement:

  ADDRESS SQL
     "SELECT RECOV_PROC INTO RECOV FROM SYSTEMS",
     "WHERE NAME = 'IMS'"
  

  The INTO clause identifies the variable that will store the retrieved value, FIXIMS.

• Example 6-Selecting Multiple Columns

  When you want to select multiple columns from a table, you can invoke a SELECT statement like this from a rule:

  ADDRESS SQL
    "SELECT CURRENT_STATE, DESIRED_STATE FROM SYSTEMS"
  

• Example 7-Using SELECT in a TSO/E REXX Program

  You can use the SELECT statement in a TSO/E REXX program, as shown here:

  OPSQL "SELECT NAME INTO :XYZ FROM COLUMN SUB(OPSF)"
  SAY XYZ
  DO I = 1 TO XYZ.0
    SAY XYZ.I
  END
  

• Example 8-Using SELECT for Cross-System SQL Operations

  You can use the SYSTEM keyword to select all items from STCTABLE on the ZOS1 system. The SYSWAIT(7) keyword indicates that you want the SQL processor to wait for 7 seconds for output from ZOS1:

  ADDRESS SQL
    "SELECT * FROM STCTABLE SYSTEM(ZOS1) SYSWAIT(7)"