CA-L-Serv Commands › SQL Statements › SELECT Statement

SELECT Statement

The SELECT statement extracts data from a relational table that meets the criteria you specify. You can use the SELECT statement by itself or within another statement (then called a subquery).

Syntax

LSQL SELECT columnlist|*asterisk [INTO (hostvarlist)] FROM tablename
 [WHERE criteria] [GROUP BY criteria [HAVING criteria]] [ORDER BY criteria]

*

Selects all columns in a table.

columnlist

Selects values only from the columns you name. Each column name must be separated by a comma.

criteria

The search criteria that you are using to match values in the specified columns.

FROM

Clause used to specify the tablename from which data is being selected.

GROUP BY

Clause used to summarize multiple rows of data into single rows based on the specified criteria.

HAVING

Clause used, only with a GROUP BY clause, to further limit the output of the statement based on the specified criteria.

hostvarlist

The host variables in which to store the selected column values. In a cursor declaration, do not use this parameter since this function is accomplished with the FETCH statement.

ORDER BY

Clause used to specify the order of the statement output.

tablename

The name of the relational table from which you are selecting.

Notes:

• You can use host variables for columnlist or tablename.
• You can issue the SELECT statement from two different environments:
  • From a TSO terminal; selected rows are displayed on the terminal.
  • From a REXX procedure or a stand‑alone CLIST (that is, not part of a cursor declaration). When you use SELECT without using a cursor and you insert the returned values into variables, the variables would receive the values in only the first row that matched the search criteria.
• When using a cursor issued from a REXX procedure or a CLIST, the cursor points to each row that matches the search criteria, one row at a time. With this method, you would normally use a WHILE loop, with the SQLCODE variable controlling the duration of the loop, to receive values from all rows of the table that match the search criteria.

Examples

• To select all values in all columns of the APPLICATIONS table, issue this command:

  LSQL SELECT * FROM APPLICATIONS
  

• To select all of the application IDs from the APPLICATIONS table, issue this command:

  LSQL SELECT APPL_ID FROM APPLICATIONS
  

• To select all of the application IDs whose status is DOWN, issue this command:

  LSQL SELECT APPL_ID FROM APPLICATIONS WHERE STATUS='DOWN'
  

• To include a SELECT statement within a DECLARE CURSOR statement that includes the APPL_ID and STATUS columns and points to rows where the status is DOWN, issue this command:

  LSQL DECLARE CURSOR STATDOWN FOR SELECT APPL_ID, STATUS FROM APPLICATIONS WHERE STATUS = 'DOWN'