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]

Parameters

*

Selects all columns in a table.

columnlist

Selects the values only from the columns you name. Separate each column name by a comma.

criteria

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

FROM

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

GROUP BY

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

HAVING

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

hostvarlist

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

ORDER BY

Clause that is used to specify the order of the statement output.

tablename

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 typically 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'