Since the second parameter is an out parameter. Why not make it a function? You can tell your function to return a table.
Here's a sample of what I previously created.
CREATE FUNCTION MYLIB/GETIMSADJUSTMENTS (
@GLCPY CHAR(3) ,
@IMSCPY CHAR(3),
@UNIT DECIMAL(6, 0) ,
@WEDATE DECIMAL(8, 0),
@ITEM DECIMAL(9,0)
)
RETURNS TABLE (
ITEM DECIMAL(9, 0) ,
ADJUSTMENTQTY DECIMAL(9, 2) ,
ADJUSTMENTS DECIMAL(15, 2)
)
LANGUAGE SQL
SPECIFIC MYLIB/GETIMSADJUSTMENTS
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
NOT FENCED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN
SELECT ITEM, SUM(QTY), SUM(AMT) FROM (
SELECT DJDITM ITEM,
-- If the headers code is negative, then switch the quantity.
SUM(CASE COALESCE(CODUC1,'')
WHEN 'NEGATIVE'
THEN DJDQTY*-1
ELSE DJDEAM
END) QTY,
-- If the headers code is negative, then switch the quantity.
SUM(CASE COALESCE(CODUC1,'')
WHEN 'NEGATIVE'
THEN DJDEAM*-1
ELSE DJDEAM END) AMT
FROM IMSPADJD
JOIN IMSPADJH
ON DJHCONO = DJDCONO
AND DJHCPY = DJDCPY
AND DJHSTR = DJDSTR
AND DJHDTE = DJDDTE
AND DJHTIM = DJDTIM
JOIN IMSPCOD
ON DJHCONO = CODCONO
AND DJHCPY = CODCPY
AND DJHTYP = CODCOD
AND CODCONO = @GLCPY
AND CODCPY = @IMSCPY
AND CODTYP = 'ADJ'
WHERE DJDCONO = @GLCPY
AND DJDCPY = @IMSCPY
AND DJDSTR = @UNIT
AND DJDFCWE = @WEDATE
AND DJDITM = @ITEM
AND DJDSTS = 'P'
AND DJDFCS in ('X', 'B', 'L')
GROUP BY DJDCONO,DJDSTR,DJDFCWE,DJDITM
UNION ALL
-- Subtract the adjustment from quantity and amount
SELECT ADITM1 ITEM, SUM(ADQTY*-1) QTY, SUM(ADAMT*-1) AMT
FROM IMSPI2IIAH
JOIN IMSPI2IIAD USING (AHADJID)
WHERE AHCONO = @GLCPY
AND AHSTR = @UNIT
AND AHSTS = 'P'
AND ADCPY1 = @IMSCPY
AND ADITM1 = @ITEM
AND ADWEDT = @WEDATE
AND ADFCS in ('X', 'B', 'L')
GROUP BY ADWEDT,ADCPY1,ADITM1
UNION ALL
-- Add the adjustment from quantity and amount
SELECT ADITM2 ITEM, SUM(ADQTY) QTY, SUM(ADAMT) AMT
FROM IMSPI2IIAH
JOIN IMSPI2IIAD USING (AHADJID)
WHERE AHCONO = @GLCPY
AND AHSTR = @UNIT
AND AHSTS = 'P'
AND ADCPY2 = @IMSCPY
AND ADITM2 = @ITEM
AND ADWEDT = @WEDATE
AND ADFCS in ('X', 'B', 'L')
GROUP BY ADWEDT,ADCPY2,ADITM2) z GROUP BY ITEM;
Then in any program that I want to access this...
Exec SQL
Select * from TABLE( GETIMSADJUSTMENTS(:Cpy,:IMSCpy,:Unit, :Date, :Item )) z ;
I went this route because as you can see the SQL statement was quite large and it was being repeated in several programs. So I created a function knowing that the access path would be saved with it. I thought about a view, but I wasn't sure if the access path was saved with the view (didn't bother to look it up). But I also overloaded the function by allowing the caller to return all items. So if you left the :Item parm off the select then every item would be returned. Another reason for using a function was that in the future our .Net applications might need to call it.
Michael Schutte
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Wednesday, November 03, 2010 10:01 AM
To: RPG programming on the IBM i / System i
Subject: Re: Data Structure with Array in SQL Call Not Allowed?
No, it's a stored procedure that I created like this:
CREATE PROCEDURE NEWSYS/GETWCS522R(IN INPOSTAL CHAR ( 6), OUT
OTDELDS CHAR ( 350)) LANGUAGE RPGLE SPECIFIC NEWSYS/GETWCS522R NOT
DETERMINISTIC READS SQL DATA EXTERNAL NAME 'NEWSYS/WCS522R'
PARAMETER STYLE GENERAL
On Wed, Nov 3, 2010 at 9:34 AM, Bryce Martin <BMartin@xxxxxxxxxxxx> wrote:
This is a UDF and not a Stored Procedure correct?
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
Michael Ryan <michaelrtr@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/03/2010 08:59 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
Data Structure with Array in SQL Call Not Allowed?
I'm getting this compile error:
SQL0312: Position 44 Variable WKOTDELDS not defined or not usable.
on this code:
D WkInPostal S 6
D WkOtDelDS DS
D WkOtDelArr 35 Dim(10)
D WkOtDelDay 30 OverLay(WkOtDelArr:*Next)
D WkOtDelAmt 5 OverLay(WkOtDelArr:*Next)
Exec Sql
Call GetWCS522R( :WkInPostal,
:WkOtDelDS);
IIRC, there's something about arrays in data structures with SQL, but I
couldn't find it in the archives. I'm at V5R4.
Do I need to *not* use this type of parameter in embedded SQL?
Thanks!
--
This is the RPG programming on the IBM i / System i (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.
--- This message (including any attachments) is intended only for the use
of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential, and
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us and destroy
this message immediately. ---
--
This is the RPG programming on the IBM i / System i (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
