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-31
Selecting From Rowset-Derived Tables
Move 2403 TO hvapartnum(3)
Move 5103 TO hvapartnum(4)
Move 6301 TO hvapartnum(5)
EXEC SQL
SELECT od.partnum, COUNT(*)
INTO :hvaodpartnum, :hvapartnumcount
FROM odetail od,
ROWSET(:hvapartnum) AS rs(partnum)
WHERE od.partnum = rs.partnum
GROUP BY od.partnum END-EXEC.
*****Process the counts in some way*****
PERFORM VARYING i FROM 1 BY 1 UNTIL i < 5
display "Part Nbr: " hvaodpartnum(i)
display "COUNT: " hvapartnumcount(i)
END-PERFORM.
...
Example
This example selects the element of the rowset-derived table that is indexed by the
number 4:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET [5] unsigned NUMERIC (4) hva_partnum;
unsigned NUMERIC (4) row_id_partnum;
...
EXEC SQL END DECLARE SECTION;
...
/* Populate the rowset in some way. */
hva_partnum[0] = 244;
hva_partnum[1] = 2001;
hva_partnum[2] = 2403;
hva_partnum[3] = 5103;
hva_partnum[4] = 6301;
...
EXEC SQL
SELECT partnum INTO :row_id_partnum
FROM ROWSET(:hva_partnum)
KEY BY row_id AS rs(partnum, row_id)
WHERE row_id = 4;
...
/* Process the selected element of the table in some way. */
printf("\nPart Nbr: %hu", row_id_partnum);
...
In this example, the selected element, whose row identifier is equal to the number 4, is
the part number 6301:
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 rowidpartnum pic 9(4) comp.
01 rs.
02 ROWSET[5] hvapartnum pic 9(4) comp.
EXEC SQL END DECLARE SECTION END-EXEC.
C
COBOL