Re: SQL multi-row FETCH

Here is where I am at:

d HDG3X120 ds
d AR1 120 din(3)
...
exec sql declare C1 cursor for
select FIELD3 from FILEP where FILEKEY = 'HEADING12'
order by FILEKEYSEQ;
exec sql open C1;
exec sql fetch C1 for 3 rows into :HDG3X120;

My preferred outcome is one field that is 360 columns wide, made up by concatenating one 120 column field from each of 3 records.

I can not tell you what is right, but I can tell you at least a dozen different ways that are wrong.


On 9/26/2015 6:54 PM, Buck Calabro wrote:

On 26 September 2015 at 18:01, Booth Martin <booth@xxxxxxxxxxxx> wrote:

Embedded SQL. This should be easier than I am making it.

Here is a statement that does not compile:

exec sql fetch C1 for 3 rows into :HDG3X120;

The error is:
SQL0221: Position 46 Number of rows 3 not valid.
SQL5011: Position 63 Host structure array HDG3X120 not defined or not
usable.

HDG3X120 is a data structure made up of a subfield AR1 120 dim(3)

Embedded SQL is a little bit like subfiles in that there are multiple
related parts. It's easier to give advice when one can see all of the
related code. In this case, the missing but important parts are the
definition of HDG3X120, and the definition of the cursor C1.

Alan's advice is good, but if I can take a guess at what's happening,
I think you want to put the DIM(3) on the DS, not on the subfield.
The subfields of the DS should exactly match the columns in the
SELECT. If your SELECT has 3 identical 120 character columns, then
dim(3) will work at the subfield level. But if the SELECT only has
one 120 character column, you'll need to tell RPG to reserve 3 /rows/
worth of that 120 byte structure.

In the end, the compiler will be setting aside the same number of
bytes of storage, but will it be 120 bytes wide x 3 rows deep, or will
it be 360 bytes wide x 1 row deep?
--buck