RE: SQL select into [array]?

Thanks Dennis. Not as "elegant" as I had hoped for, but pretty elegant,
nonetheless. I'll use it. (I had hoped to not have to define my ds
with all 99 subfields...) Oh well.
-- Michael

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Thursday, August 27, 2009 12:28 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: SQL select into [array]?

Out of luck. SQL won't accept array elements and into cannot go into
an array.

I put a request for new features that you could do a fetch into an
array but of course who knows if they are ever going to do or at all.

On Thu, Aug 27, 2009 at 9:54 AM, Koester, Michael <mkoester@data-
east.com>wrote:

I am looking for an elegant way to code an embedded SQL statement

that

selects into, where the host target is an array.

As in:

Select Field1, Field2, Field3
into :MyArray(1), :MyArray(2), :MyArray(3) From *libl/MyTable

Where Key1 = :MyKey;

The way I have done this in the past is something like this:

D Amounts_DS DS
D Amt01 7P 0
D Amt02 7P 0
D Amt03 7P 0
D Amt04 7P 0
D Amt05 7P 0
D Amt06 7P 0
D Amt07 7P 0

D pAmts S * Inz(%Addr(Amounts_DS))
D AmtArray S 7P 0 Based(pAmts) Dim(7)

Exec SQL
SELECT Amt1, Amt2, ... Amt7
INTO Amounts_DS
FROM ...
;


HTH

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
The best way to save face is to keep the lower part full