Tyger Guzman 2 Posted February 27, 2020 Share Posted February 27, 2020 I'm trying to run row items through a SOAP request. I have the loop and process to convert the soap response to a table. But when I run this procedure only the last item in the loop is being returned. Is there away to append resutls to a cursor during a loop so that the final result will enclude all values PROCEDURE RunLoop(OUT result cursor(ID INTEGER, PARENT_ID INTEGER, DEPTCOL INTEGER, NAME varchar(255), XPATH varchar(255), PATHCOL varchar(255), POSTION integer, VAL_COL varchar(255))) BEGIN DECLARE LookUpXML varchar(255); DECLARE RowCount int; DECLARE lookup1 varchar(255); DECLARE lookup2 varchar(255); declare c CURSOR (ID INTEGER, PARENT_ID INTEGER, DEPTCOL INTEGER, NAME varchar(255), XPATH varchar(255), PATHCOL varchar(255), POSTION integer, VAL_COL varchar(255)) ; select count(*) into RowCount from /InputOnly; L_loop: Loop if RowCount = 0 then LEAVE L_loop; end if ; select value1 into lookup1 from /InputOnly where RowNum = RowCount; select value2 into lookup2 from /InputOnly where RowNum = RowCount; Select cast(checkVat as varchar(255)) into LookUpXML from /Test_2(lookup1, lookup2); open c for Select * from /ProcessSOAPToTable(LookUpXML); close c ; set RowCount = RowCount -1 ; end Loop ; SET result = c ; OPEN result ; END Link to comment Share on other sites More sharing options...
Motsu Aboshi Posted February 28, 2020 Share Posted February 28, 2020 Try using a PIPE, INSERT after each loop iteration. Link to comment Share on other sites More sharing options...
Tyger Guzman 2 Posted February 28, 2020 Author Share Posted February 28, 2020 Could you provide a little more detail. I'm newer to TDV and not familiar with all its functionality. Not sure if you are saying replace my Procedure output to a PIPE or to add a PIPE. I was able to get the function to work by append items to an external SQL table but would like to keep it all within TDV as much as possible and not utilize the SQL server to append to. Link to comment Share on other sites More sharing options...
Tyger Guzman 2 Posted February 28, 2020 Author Share Posted February 28, 2020 Changing the CURSOR to a PIPE in the procedure got the result I wanted : PROCEDURE RunLoop(OUT result PIPE(ID INTEGER, PARENT_ID INTEGER, DEPTCOL INTEGER, NAME varchar(255), XPATH varchar(255), PATHCOL varchar(255), POSTION integer, VAL_COL varchar(255))) BEGIN DECLARE LookUpXML varchar(255); DECLARE RowCount int; DECLARE lookup1 varchar(255); DECLARE lookup2 varchar(255); select count(*) into RowCount from /InputOnly; L_loop: Loop if RowCount = 0 then LEAVE L_loop; end if ; select value1 into lookup1 from /InputOnly where RowNum = RowCount; select value2 into lookup2 from /InputOnly where RowNum = RowCount; insert into result Select * from /ProcessSOAPToTable(LookUpXML); set RowCount = RowCount -1 ; end Loop ; END Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now