For the most part, procedures for using the DB/Assist dialog boxes to build a single-row SELECT statement are identical to those described in building a multi-row SELECT statement. However, some differences do exist when you build a single-row SELECT statement. This section describes the differences.

A single-row SELECT statement uses the SELECT INTO syntax to retrieve exactly one row of data from a table, and assign the values in that row to ESL host variables. You will supply the ESL variables when you link variables to the statement, as described in Linking Variables to SQL Statements.

For example, the following single-row SELECT statement retrieves a singe row of data in the Staff table for the employee whose last name is James.

 

SELECT STAFF.ID, STAFF.NAME, STAFF.DEPT,STAFF.JOB, STAFF.YEARS, STAFF.SALARY, STAFF.COMM

 INTO ?, ??, ??, ??, ??, ??, ??

 FROM STAFF

 WHERE STAFF.NAME = 'James'

Until you link a single-row SELECT statement to host variables, DB/Assist displays question marks (?) in the SQL Editor window as placeholders. DB2 refers to these question marks as parameter markers.

A single question mark (?) in a single-row SELECT statement represents a column that prohibits null values, and a pair of question marks ??) represents a column that permits null values. In a question mark pair, the second question mark is a null indicator variable, which is described in more detail in Linking Variables to SQL Statements.

As shown in the preceding example, the first column in the list, STAFF.ID, prohibits null values, as indicated by the single question mark. All of the other columns permit null values, as indicated by the double question marks.

You cannot build a GROUP BY clause or an ORDER BY clause as part of a single-row SELECT statement.

Therefore, the buttons and menu choices for the GROUP BY clause and ORDER BY clause are disabled while you build a single-row SELECT statement.

By definition, a single-row SELECT statement must retrieve only one row of data from a table. Therefore, if you attempt to run a single-row SELECT statement that retrieves more than one row, DB/Assist displays an error message instructing you to specify additional search conditions in order to limit the result set to a single row.

For complete information about running SQL statements in DB/Assist, see Running a SQL Statement.