RE: Use SQL to load an Array

Are you needing to use grouping to summarize your data? Say for example, if your on-hand table has a row for each slot, you might return many rows of on-hand for the same SKU.

Maybe something like this....
H DFTACTGRP(*NO)

DarrDs ds qualified dim(50)
D SKU 15a
D Qty 7s 0

/free
exec sql
Select
SKU, decimal(sum(QTY),7,0)
Into
:arrDs
From
ONHAND
Group by SKU
Where
CLASS='Z';

*inlr = *on;
Return;

/end-free


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe D
Sent: Saturday, March 01, 2008 8:38 PM
To: RPG programming on the AS400 / iSeries
Subject: Use SQL to load an Array


Hi All,

I have tried every variation I can think of, and every example I have found on the List and elsewhere via Google. I must be missing something obvious.

I have a simple array containing a part number and quantity. I want to use SQL to populate the array, and I am almost sure I can do it in a single statement.

Using just a 'Select Into' the array winds up with all 50 elements having the same values. I also tried using a Cursor/Fetch Into with the same results. At this point I have spent hours on something I though would take five minutes.

Here is the simplist variation I have tried. If anyone can tell me what I am missing I would greatly appreciate it.

FYI - There are only 7 items with CLASS=Z, so it would not overflow the array. Once I get it working the way I want I will limit the number of rows returned in the production app.

H DFTACTGRP(*NO)

DarrDs ds qualified dim(50)
D SKU 15a
D Qty 7s 0

/free
exec sql
Select
SKU, QTY
Into
:arrDs
From
ONHAND
Where
CLASS='Z';

*inlr = *on;
Return;

/end-free

Thanks!
JD