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-30
Selecting From Rowset-Derived Tables
A SELECT statement that contains a rowset-derived table within the FROM clause
handles its input data as a join of the other table references with the rowset-derived
table.
Example
In this example, the ODETAIL table is joined with a rowset using a rowset-derived
table. The program counts the number of elements in common between the part
number values in ODETAIL and the values in the rowset, which is composed of an
array of part numbers:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET [5] unsigned NUMERIC (4) hva_partnum;
ROWSET [5] unsigned NUMERIC (4) hva_od_partnum;
ROWSET [5] short hva_partnum_count;
...
EXEC SQL END DECLARE SECTION;
long i;
...
/* 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 od.partnum, COUNT (*)
INTO :hva_od_partnum, :hva_partnum_count
FROM sales.odetail od,
ROWSET(:hva_partnum) AS rs(partnum)
WHERE od.partnum = rs.partnum
GROUP BY od.partnum;
...
/* Process the counts in some way. */
for (i = 0; i < 5; i++) {
printf("\nPart Nbr: %hu", hva_od_partnum[i]);
printf("\nCount: %hu", hva_partnum_count[i]);
}
...
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 rs.
02 ROWSET[5] hvapartnum pic 9(4) comp.
02 ROWSET[5] hvaodpartnum pic 9(4) comp.
02 ROWSET[5] hvapartnumcount pic s9(4) comp.
EXEC SQL END DECLARE SECTION END-EXEC.
01 i pic s9(4) comp.
...
***** Populate the rowset in some way *****
Move 244 TO hvapartnum(1)
Move 2001 TO hvapartnum(2)
C
COBOL