|
Todd -- I'm confused as to exactly what the problem is. Are you not getting a value for SPENDABLE from ACTQ010 or what? What output variable doesn't "work" and what are you seeing? Gary > -----Original Message----- > From: java400-l-bounces@xxxxxxxxxxxx > [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Todd Bryant > Sent: Wednesday, April 23, 2003 1:24 PM > To: 'java400-l@xxxxxxxxxxxx' > Subject: Getting an output variable from a stored proc in a > stored proc > > > Hi, I have a stored procedure question and though it is not > technically Java, it is going to be used in a Websphere > environment so I thought someone might be able to help me. > > We have a stored procedure that we are trying to get an > output variable to work. I know how to register an output > variable when calling a stored proc that accepts one in Java > (though I have not actually done it. I just know how), but we > want to call a stored proc from within an SQL stored proc and > get the output variable. Maybe looking at our code will help > (See code below. we are trying to get a "spendable amount" > from another stored proc. The other stored proc is written in > RPG and appears to be working properly. The called stored > proc takes a char string input var and accepts a char output > var. It returns no resultset). > > Maybe you can only get output variables in any other language > than SQL procedure language? Or maybe we are just missing > something simple? I truly appreciate any help anyone can give. > > ----------------- > CREATE PROCEDURE TST.FUND_DETAILS_1 ( > IN FUND CHAR(8) , > IN LIBRARY CHAR(15) ) > DYNAMIC RESULT SETS 1 > LANGUAGE SQL > SPECIFIC TST.FUND_DETAILS_1 > NOT DETERMINISTIC > MODIFIES SQL DATA > CALLED ON NULL INPUT > BEGIN > DECLARE STMT CHAR(4000); > DECLARE SPENDABLE CHAR(20); > DECLARE C1 CURSOR WITH RETURN FOR S1; > > > /* set spendable amount here */ > CALL TST.ACTQ010('30013550', SPENDABLE); > > SET STMT = 'SELECT FUND.ACTNO AS ACCOUNT_NUMBER, > FUND.ACTNM AS SHORT_NAME, > NAME.RCFDCP AS LONG_NAME, > END.CODDSC AS ENDOWMENT, > /* concatenate amount in here */ > ''' || SPENDABLE || ''' AS SPENDABLE_AMT, > PUR.CODE30 AS PURPOSE, > DEV.TCNAM AS DEV_OFFICER, > ADDR.PHONE AS PHONE_NUMBER, > SUM(INVEST.INBKVL) AS BOOK_VALUE, > SUM(INVEST.INMKVL) AS MARKET_VALUE, > FUND31.CASH AS PRINCIPAL_CASH, > FUND.PLEDGE AS PLEDGE, > FUND.INVEST AS INVESTED, > FUND.CASH AS INCOME_CASH, > FUND.TOTBAL AS TOTAL_BALANCE, > HIER1.TBODSC AS CAMPUS_DESC, > HIER2.TBODSC AS INST_DESC, > HIER3.TBODSC AS COLL_DESC, > HIER4.TBODSC AS DEPT_DESC, > HIER5.TBODSC AS SUB_DESC > FROM > ' || LIBRARY ||'.LDSP010 AS FUND > inner join ' || LIBRARY ||'.LDSL014 AS FUND31 > on (SUBSTR(FUND.ACTNO, 3, 5) = > SUBSTR(FUND31.ACTNO, 3, 5)) > > inner join ' || LIBRARY || '.RCTP080 AS NAME > on (FUND.ACTNO = NAME.RCFNDC) > inner join ' || LIBRARY || '.TABP120 AS END > on (FUND.ENDWL = TRIM(END.CODE)) > inner join ' || LIBRARY || '.TABP300 AS PUR > on (FUND.LDLD03 = PUR.CODE3) > inner join ' || LIBRARY || '.TABP290 AS DEV > on (FUND.CONT1 = DEV.TCONT) > left join ' || LIBRARY || '.ADDP010 AS ADDR > ON (DEV.TCMID# = ADDR.MSTID) > left join ' || LIBRARY || '.INVP020 AS INVEST > on (FUND.ACTNO = INVEST.INACT#) > left join ' || LIBRARY || '.TABL476 AS HIER1 > on (FUND.LDOLV1 = HIER1.TBOLV1) > left join ' || LIBRARY || '.TABL472 AS HIER2 > on (FUND.LDOLV1 = HIER2.TBOLV1 > AND FUND.LDOLV2 = HIER2.TBOLV2) > left join ' || LIBRARY || '.TABL473 AS HIER3 > on (FUND.LDOLV1 = HIER3.TBOLV1 > AND FUND.LDOLV2 = HIER3.TBOLV2 > AND FUND.LDOLV3 = HIER3.TBOLV3) > left join ' || LIBRARY || '.TABL474 AS HIER4 > on (FUND.LDOLV1 = HIER4.TBOLV1 > AND FUND.LDOLV2 = HIER4.TBOLV2 > AND FUND.LDOLV3 = HIER4.TBOLV3 > AND FUND.LDOLV4 = HIER4.TBOLV4) > left join ' || LIBRARY || '.TABL475 AS HIER5 > on (FUND.LDOLV1 = HIER5.TBOLV1 > AND FUND.LDOLV2 = HIER5.TBOLV2 > AND FUND.LDOLV3 = HIER5.TBOLV3 > AND FUND.LDOLV4 = HIER5.TBOLV4 > AND FUND.LDOLV5 = HIER5.TBOLV5) > > WHERE FUND.ACTNO = ? > AND END.FIELD = ''ENDWL'' > AND PUR.FIELD = ''UDLD03'' > AND ADDR.ADDTYP = ''B'' > > GROUP BY > FUND.ACTNO, FUND.ACTNM, NAME.RCFDCP, END.CODDSC, > PUR.CODE30, DEV.TCNAM, > ADDR.PHONE, FUND31.CASH, FUND.PLEDGE, FUND.INVEST, > FUND.CASH, FUND.TOTBAL,HIER1.TBODSC, > HIER2.TBODSC, HIER3.TBODSC, HIER4.TBODSC, HIER5.TBODSC'; > > PREPARE S1 FROM STMT; > OPEN C1 USING FUND; > END > ------------------------------------ > > > Todd Bryant, SCJP > Programmer/Analyst > University of Nebraska Foundation > 402-472-0107
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.