Jose,
Here is an RPGLE example in /Free format:
First, traditional D specs define a DS array:
* Start: data to store Pallet Detail -------------------------------------
D PDmaxC C const(2000) max Pallet Detail
D PDmax S 5I 0 Inz(PDmaxC) " " "
D PD@ S 5I 0 Inz(0) index to PD#
D PD# DS DIM(PDmaxC) Qualified
D DelLoc 4A Delivery Location
D DelDat 7S 0 Inz(*Zero) " Dt CYYMMDD
D DelRte 5A " Route
D PalSeqNum 5P 0 Inz(*Zero) Pallet Sequence Num
D ArtNum 9S 0 Inz(*Zero) Article number
D UniLod 7P 0 Inz(*Zero) Units to be loaded
D UniPkd 9P 0 Inz(*Zero) " loaded on palt
D ArtNam 30A Article Name
D ArtWgt 5P 2 Inz(*Zero) " Case Wgt Lbs
D Upc12 12A AM01.USRARE001,01,12
D PackTy 2A " .ARTGRP07
D PackTyCi 5A " .ARTGRP07 CI
D SubU 2A " .ARTGRP08
* End : data to store Pallet Detail -------------------------------------
The size of the array is much larger than any expected Pallet
Here, in /Free format is SQL statement for the cursor:
// Cursor to get Pallet Detail for specified Delivery
Exec SQL
DECLARE PDcsr CURSOR FOR
SELECT l95.delloc, l95.deldat, l95.delrte, l96.palseqnum,
l97.artnum, l97.unilod, l97.unipkd, am1.artnam,
am1.artwgt, SUBSTR(am1.usrare002,01,12) AS upc12,
am1.artgrp07 AS packty,
SUBSTR(xi1.datafield,41,05) AS packtyci,
am1.artgrp08 AS subu
FROM lc95 l95 JOIN lc96 l96
ON l96.delloc = l95.delloc
AND l96.deldat = l95.deldat
AND l96.delrte = l95.delrte
JOIN lc97 l97
ON l97.delloc = l95.delloc
AND l97.deldat = l95.deldat
AND l97.delrte = l95.delrte
AND l97.palseqnum = l96.palseqnum
JOIN am01 am1
ON am1.artnum = L97.artnum
AND am1.eftdat = 1999999
JOIN xi01 xi1
ON xi1.recid = 'XIC'
AND xi1.mainkey = 'PACKTY'
AND xi1.subkey = (' ' || AM1.artgrp07)
WHERE l95.delloc = :wk_DelLoc
AND l95.deldat = :wk_DelDat
AND l95.delrte = :wk_DelRte
AND L96.outnum = :wk_OutNum
AND L97.UNIPKD > 0
ORDER BY 1,2,3,4,5;
Next is an SQL statement to populate the array:
Exec SQL FETCH PDcsr FOR :PDmax ROWS INTO :PD#;
And, finally, an SQL statement to load a table in QTEMP, or a library of your choice:
(Note the "WITH DATA" clause at the end of the SQL statement)
Exec SQL
CREATE TABLE qtemp/apartsav AS (
SELECT artnum, SUBSTR(artnam,1,18) AS artnam18,
CAST(0 AS NUMERIC(7,0)) AS artnmovipk,
artgrp7, artwgt,
SUBSTR(usrare030C,01,03) AS apshlflif,
SUBSTR(usrare030C,04,02) AS apstkseq,
SUBSTR(usrare030C,06,02) AS aplayrunt,
SUBSTR(usrare030C,08,05) AS apformat,
SUBSTR(usrare030C,13,02) AS appaltyp,
SUBSTR(usrare030C,15,03) AS apcsppal,
SUBSTR(usrare030C,18,01) AS aplayropt,
SUBSTR(usrare030C,19,01) AS aplayrreq,
SUBSTR(usrare030C,20,01) AS apdisc,
bascamind, basartref, artgrp05,
artgrp06, artgrp07, sprcod, sprdat
FROM basdbc1/am01
WHERE eftdat = 1999999
AND sprcod = ' '
AND SUBSTR(usrare030C,08,05) NOT IN (' ','00000')
AND am01.artnum IN (SELECT artnum FROM basdbc1/am02
WHERE delloc IN ('09 ','11 ','92 '))
ORDER BY 1
) WITH DATA;
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jose Luis Hernandez Riesgo
Sent: Monday, December 15, 2014 4:05 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Cursors vs Arrays in Stored Procedure
Hi everybody.
If it's possible some help. I have one stored procedure which returns three result sets. One array and two cursors (from two tables in qtemp). The result sets (array and cursors) are built on a rpg program.
Somebody know if is possible, convert the return of cursors in arrays in a easy way.
In resume, i try that one rpg program return three arrays to a stored procedure instead of one array and two cursors.I need leave to work with qtemp tables.
Finally, we are in V7R1.
Thanks in advance,
greetings and merry christmas to everybody.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
