return the results from Query union the external proc in DB2
-
Hello experts, I am pretty new to DB2. I have a query that returns some result. For each row of the results, I should pass a value from results to external Procedure. Now I want an union of both the results. Here is what I have tried. And I am banging my head to wall since last three days. Can someone please help me with this? It is really really important now.
BEGIN
DECLARE EOF_COND INTEGER DEFAULT 0 ;
DECLARE @ITEM CHAR ( 30 ) ;
DECLARE @BIN CHAR ( 10 ) ;
DECLARE @SIZE CHAR ( 30 ) ;
DECLARE @TIRERACK CHAR ( 10 ) ;
DECLARE @RACKSBIN CHAR ( 10 ) ;
DECLARE @DESC CHAR ( 100 ) ;
DECLARE @DESC1 CHAR ( 100 ) ;
DECLARE @ONHAND CHAR ( 10 ) ;
DECLARE @COMTD CHAR ( 10 ) ;
DECLARE @USEDRACKS CHAR ( 10 ) ;
DECLARE @OPENRACKS CHAR ( 10 ) ;
DECLARE @TEMP CHAR ( 10 ) ;
DECLARE RESULT INTEGER DEFAULT 0 ;DECLARE CR1 CURSOR WITH RETURN FOR
SELECT T2 . ICITEM , T2 . BABLOC , C . ISSIZE , D . UNITSPER
, E . NBRRCKBINB , F . ICDSC1 , F . ICDSC2 , T2 . TQOH , T2 . TQCM
, CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER )
NBR_USED_RACKS
, E . NBRRCKBINB - ( CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) )
NBR_OPEN_RACKS , ( E . NBRRCKBINB * D . UNITSPER ) - T2 . TQOH
FROM (
SELECT A . BACMP , A . BALOC , MIN ( A . BAITEM ) ICITEM
, A . BABLOC , INTEGER ( SUM ( A . BAQOH ) ) TQOH ,
INTEGER ( SUM ( A . BAQCM ) ) TQCM
FROM TESTDATA . VINBINI A WHERE A . BALOC = '13' AND 1 = A . BACMP AND
A . BAQOH - A . BAQCM > 0 GROUP BY A . BACMP , A . BALOC , A . BABLOC HAVING
MIN ( A . BAITEM ) = MAX ( A . BAITEM )
AND SUM ( A . BAQOH - A . BAQCM ) > 0
) AS T2
, TESTDATA . PALITMLOC B , TESTDATA . VINITEMSIZ C , TESTDATA . PALSIZQTY D , TESTDATA . PALBINPF E
, TESTDATA . VINITEM F
WHERE T2 . BACMP = B . TACOMP AND T2 . ICITEM = B . ICITEM
AND T2 . BALOC = B . IALOC AND T2 . ICITEM = F . ICITEM
AND T2 . ICITEM = C . ISITEM
AND B . PALLETID = D . PALLETID
AND C . ISSIZE = D . ISSIZE
AND E . TACOMP = T2 . BACMP
AND E . IALOC = T2 . BALOC
AND E . IMBLOC = T2 . BABLOC
AND E . PALLETID = B . PALLETID
ORDER BY 1 , 2 ;OPEN CR1 ;
SET EOF_COND = 0 ;
FETCH CR1 INTO @ITEM , @BIN , @SIZE , @TIRERACK , @RACKSBIN , @DESC , @DESC1 , @ONHAND , @COMTD , @USEDRACKS , @OPENRACKS , @TEMP ;CALL TESTDATA . PARECR24SP ( '01' , '13' , @BIN , RESULT ) ;
RETURN ;
END
Your help is much appreciated. Thanks Happy Coding!