Micro Focus Visual COBOL 2.3 Update 2 for Eclipse (Windows) > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > SELECT DISTINCT (using DECLARE CURSOR)

SELECT DISTINCT (using DECLARE CURSOR)

Associates the cursor name with the SELECT DISTINCT statement and enables you to retrieve rows of data using the FETCH statement.

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.

Comments:

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