SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-28
Specifying Rowset-Derived Tables
}
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 rs.
02 ROWSET[5] hvajobcode pic 9(4) comp.
02 ROWSET[100] hvarowcount pic 9 comp.
02 ROWSET[100] hvarowid pic s9(4) comp.
01 numrows pic 9(9) comp.
EXEC SQL END DECLARE SECTION END-EXEC.
01 i pic s9(4) comp.
...
***** Populate the rowset in some way *****
Move 100 TO hvajobcode(1)
Move 200 TO hvajobcode(2)
****3 Does not exist****
Move 350 TO hvajobcode(3)
Move 400 TO hvajobcode(4)
Move 500 TO hvajobcode(5)
EXEC SQL ROWSET FOR KEY BY row_id
SELECT row_id, COUNT(*)
INTO :hvarowid, :hvarowcount
FROM employee
WHERE jobcode = :hvajobcode
GROUP BY row_id END-EXEC.
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT end-exec.
PERFORM VARYING i FROM 1 BY 1 UNTIL i > numrows
display "Row Id: " hvarowid(i)
display "Row Count: " hvarowcount(i)
end-perform.
...
Specifying Rowset-Derived Tables
Use a rowset-derived table to manipulate rowsets like other tables in SQL statements.
A rowset-derived table is similar to an in-memory table and you can use it, followed by
its rowset table correlation, anywhere a table name is specified in a DML statement by
using the syntax. (This table correlation clause is required.)
rowset-size
restricts the size of the rowset-derived table to the specified size, which must be
less than or equal to the allocated size for the rowset. The size, if specified,
immediately follows the ROWSET keyword. The size is an unsigned integer or a
host variable whose value is an unsigned integer. By default, if the size is not
specified, NonStop SQL/MX uses the allocated rowset size specified in the SQL
Declare Section.
ROWSET [rowset-size] (:array-name [,:array-name]...)
[KEY BY row-id]
[AS] correlation (column [,column]...)
COBOL