SELECT DISTINCT (using DECLARE CURSOR)

Syntax

>>---EXEC SQL--.------------.------DECLARE cursor_name---->
               +-AT db_name-+

 >---CURSOR FOR------SELECT DISTINCT------select_list------>

 >---FROM----table_list--.-----------------2--END-EXEC----><
                         +-select_options--+

Parameters

db_name The name of a database that has been declared using DECLARE DATABASE.
cursor_name Cursor name used to identify the cursor in subsequent statements. Cursor names can contain any legal filename character and be up to 30 characters in length. The first character must be a letter.
select_list The name of the columns to retrieve.
table_list The name of the tables that contain the columns to be retrieved, as specified in select_list.
select_options The options specified to limit the number of rows retrieved and/or order the rows retrieved.

Purpose

The DECLARE CURSOR statement associates the cursor name with the SELECT DISTINCT statement and enables you to retrieve rows of data using the FETCH statement.

Two separately compiled programs cannot share the same cursor. All statements that reference a particular cursor must be compiled together.

The SELECT DISTINCT statement runs when the cursor is opened. The following rules apply to the SELECT DISTINCT statement:

  • The statement cannot contain an INTO clause or parameter markers.
  • The statement can contain input host variables previously identified in a declaration section.
  • With some ODBC drivers, the SELECT DISTINCT statement must include a FOR UPDATE clause if positioned updates or deletions are to be performed.
Note:

Use SELECT DISTINCT instead of SELECT INTO to remove duplicate rows in the row set.

Example:

 01 age-array        pic s9(09) comp-5 occurs 10 times.
 01 lname-array      pic x(32)   occurs 10 times.

    MOVE 5 TO staff-id
    EXEC SQL
       SELECT DISTINCT last_name
          INTO :lname-array
          FROM staff
          WHERE staff_id > :staff-id
    END-EXEC

    EXEC SQL
       SELECT DISTINCT age
          INTO :age-array
          FROM staff
          WHERE first_name > 'George'
    END-EXEC