1 2 Previous Next 18 Replies Latest reply on Nov 13, 2017 10:54 AM by shawkins

    Use of WHILE or LOOP ON for executing a procedure within VP

    rflesken

      Hi,

       

      I'm not very experienced in JDV, and looking for a way to iterate through a resultset and use the resultvalues in a WHILE loop for executing a procedure

       

      So, i get the resultset with a select using an input parameter (RequestNumber):

       

      select b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME
      from b_vbl_iprocess.CASE_INFORMATION
      join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_ID = b_vbl_iprocess.CASE_INFORMATION.PROC_ID
      where ci.casedesc = CaseService.DeleteRequestComplete_n.RequestNumber
      and ci.is_dead = 0
      

       

      Now i need to iterate through these results and use the value of CASENUM and PROC_NAME within an procedure execute, until the results of the query above are 0. When the procedure is ran, the value of ci.is_dead will become 1.

       

      EXEC b_vbl_iprocess.SW_CLOSE(VARIABLES.proc_name, -1, -1, VARIABLES.casenum, 'ipadmusr');
      

       

      How would i do this? I would think something like below, but there must be a more clever way.. Thanks in advance!

       

      BEGIN
      declare int variables.counter = 0;
      
      declare int variables.tablecount= 
      (select count(*)
      from b_vbl_iprocess.CASE_INFORMATION
      join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_ID = b_vbl_iprocess.CASE_INFORMATION.PROC_ID
      where ci.casedesc = CaseService.DeleteRequestComplete_n.RequestNumber
      and ci.is_dead = 0);
      
      declare table variables.activecases = (select b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME
      from b_vbl_iprocess.CASE_INFORMATION
      join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_NAME = b_vbl_iprocess.CASE_INFORMATION.PROC_ID
      where ci.casedesc = CaseService.DeleteRequestComplete_n.RequestNumber
      and ci.is_dead = 0);
      
      WHILE(VARIABLES.tablecount > VARIABLES.counter)
      BEGIN ATOMIC
      EXEC b_ vbl_iprocess.SW_CLOSE(VARIABLES.proc_name, -1, -1, VARIABLES.casenum, 'ipadmusr');
      END
      
        • 1. Re: Use of WHILE or LOOP ON for executing a procedure within VP
          shawkins

          You may directly join between a table and a procedure using a lateral join, or in this case if the procedure just has a return value you could just call it in the select:

           

          select b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME, (EXEC b_ vbl_iprocess.SW_CLOSE(proc_name, -1, -1, casenum, 'ipadmusr')) as close

          from b_vbl_iprocess.CASE_INFORMATION 

          join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_NAME = b_vbl_iprocess.CASE_INFORMATION.PROC_ID 

          where ci.casedesc = CaseService.DeleteRequestComplete_n.RequestNumber 

          and ci.is_dead = 0 

          • 2. Re: Use of WHILE or LOOP ON for executing a procedure within VP
            rflesken

            I Steven, thanks for your reply.

            I tried your suggestion, but keep getting parsing errors:

             

            BEGIN 
                select (b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME, (EXEC b_ vbl_iprocess.SW_CLOSE(b_vbl_iprocess.PROC_INDEX.PROC_NAME, -1, -1, b_vbl_iprocess.CASE_INFORMATION.CASENUM, 'ipadmusr'))) as close
            from b_vbl_iprocess.CASE_INFORMATION  
            join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_ID = b_vbl_iprocess.CASE_INFORMATION.PROC_ID  
            where b_vbl_iprocess.CASE_INFORMATION.CASEDESC = CaseService.DeleteRequestComplete_n.RequestNumber  
            and b_vbl_iprocess.CASE_INFORMATION.is_dead = 0  
            END
            

             

            ERROR: Error in parsing of sql - Encountered " "exec" "EXEC "" at line 2, column 95.

            • 3. Re: Use of WHILE or LOOP ON for executing a procedure within VP
              shawkins

              Are you trying to select the expressions as an array value?

               

              If not, you don't need to use the extra enclosing parens - select ( )

              • 4. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                rflesken

                It doesn't really matter, i tried all looks like i can't execute from the SELECT.

                I use DV6.2 with teiid 8.7.1

                • 5. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                  shawkins

                  Using a procedure as a correlated subquery is fully support with Teiid. 

                   

                  Are you trying to submit this as an anonymous procedure block?

                   

                  If not, you don't need the begin/end.

                   

                  If you are nesting in a procedure block, then command statements need a ';' delimiter at the end - is_dead = 0;

                  • 6. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                    rflesken

                    Hi Steven, perhaps i need to explain what i want to do.

                     

                    I need to run the SW_CLOSE for all records from the select below.

                    After that, i want to select the casenum, procname and is_dead status (from case_information) and display them as xmlResult in the resultset (this part is not in the code below yet). I can't get through the first part. It keeps returning parsing errors. I attached a screenshot.

                     

                    issue_dv_deleterequestcomplete.JPG

                    • 7. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                      shawkins

                      It's likely due to the older version, but you should be getting better error messages from the parser to indicate where the problem starts.  Using a later version I see immediately there is an space in the procedure name: EXEC b_[*]v...

                      • 8. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                        rflesken

                        That's it... thanks.

                        Can i use XMLELEMENT tags in the query to instantly create a resultset without using a result for the procedure SW_CLOSE?

                        So something like:

                         

                        BEGIN
                        SELECT 
                        XMLELEMENT(NAME TmpTableOut, XMLATTRIBUTES(CaseService.DeleteRequestComplete_n.RequestNumber AS RequestNumber), 
                        XMLELEMENT(NAME CaseNumber, CI.CASENUM), 
                        XMLELEMENT(NAME ProcName, PI.PROC_NAME)) AS xmlResult, 
                        (SELECT * FROM (EXEC b_vbl_iprocess.SW_CLOSE(PI.PROC_NAME, -1, -1, CI.CASENUM, 'ipadmusr')) AS x) 
                        AS CloseProc
                        FROM b_vbl_iprocess.CASE_INFORMATION AS CI 
                        INNER JOIN b_vbl_iprocess.PROC_INDEX AS PI ON PI.PROC_ID = CI.PROC_ID
                        WHERE (CI.CASEDESC = CaseService.DeleteRequestComplete_n.RequestNumber) AND (CI.IS_DEAD = 0);
                        END
                        

                         

                        With this, or without the XMLELEMENT tags i get: ERROR: java.util.NoSuchElementException

                        • 9. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                          shawkins

                          > Can i use XMLELEMENT tags in the query to instantly create a resultset without using a result for the procedure SW_CLOSE?

                           

                          You definitely may use XMLELEMENT.  A couple of considerations.  If the procedure is expected to just return xml, then you need only an xml result.  You can't also have closeproc at the top level of the select.  Another consideration is whether you are trying to return a single document/element containing everything or a result that has a row for each element.  The sql you currently have produces the latter.  You can use the aggregate function XMLAGG to produce a single document, for example with a root element - XMLELEMENT(NAME root, XMLAGG(XMLELEMENT(...)))

                           

                          > With this, or without the XMLELEMENT tags i get: ERROR: java.util.NoSuchElementException

                           

                          Can you provide the stacktrace for that?  Direct java exceptions are not expected.

                          • 10. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                            rflesken

                            Hi Steven,

                             

                            > If the procedure is expected to just return xml, then you need only an xml result.  You can't also have closeproc at the top level of the select

                             

                            How would i solve this, i need the procedure SW_CLOSE to run, and also need an xmlresult of the cases in the select. Can i produce a second select as xmlResult?

                             

                            > Can you provide the stacktrace for that?  Direct java exceptions are not expected.

                             

                            Can't find anything in the designer log, any other place i should seek?

                            • 11. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                              shawkins

                              > How would i solve this, i need the procedure SW_CLOSE to run, and also need an xmlresult of the cases in the select. Can i produce a second select as xmlResult?

                               

                              If you need any of the return values from the procedure are used, then you can do it all in a single select, but if you use something like an inline view to just select the xml:

                               

                              select xmlelenent....  from (select ..., (EXEC ... )) as vw

                               

                              The EXEC subquery will simply get optimized out of the plan.

                               

                              The workaround in that case would be to use a temporary table:

                               

                              -- first select everything you need into the temp table

                              insert into #temp select cols ...;

                              -- then execute a select that will call the procedure for each row.  you could do this in a loop if you want as well

                              select (EXEC ...) from #temp WITHOUT RETURN;

                              -- then create the xml result you want

                              select XMLELEMENT(...) from #temp;

                               

                              > Can't find anything in the designer log, any other place i should seek?

                               

                              Is it possible to provide a screenshot?

                              • 12. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                                rflesken

                                Hi Steven,


                                Thanks again.

                                 

                                I changed the procedure and the parser seems okay, but still receive the ERROR: java.util.NoSuchElementException. I attached a screenshot.

                                 

                                I use this now:

                                 

                                BEGIN
                                CREATE LOCAL TEMPORARY TABLE #temp (tmp_requestnumber integer, tmp_casenum bigdecimal, tmp_proc_name string, tmp_closedate timestamp);
                                SELECT CaseService.DeleteRequestComplete_n.RequestNumber AS tmp_requestnumber, CI.CASENUM AS tmp_casenum, PI.PROC_NAME AS tmp_proc_name, NOW() AS tmp_closedate INTO #temp FROM b_vbl_iprocess.CASE_INFORMATION AS CI INNER JOIN b_vbl_iprocess.PROC_INDEX AS PI ON CI.PROC_ID = PI.PROC_ID WHERE (CI.CASEDESC = CaseService.DeleteRequestComplete_n.RequestNumber) AND (CI.IS_DEAD = 0);
                                SELECT (SELECT * FROM (EXEC b_vbl_iprocess.SW_CLOSE(tmp_proc_name, -1, -1, tmp_casenum, 'ipadmusr')) AS x) FROM #temp WITHOUT RETURN;
                                SELECT XMLELEMENT(NAME ClosedCases, XMLAGG(XMLFOREST(CaseService.DeleteRequestComplete_n.RequestNumber AS RequestNumber, tmp_casenum AS IProcessCase, tmp_proc_name AS ProcedureName, tmp_closedate AS ClosedDate))) AS xmlResult;
                                END
                                
                                • 13. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                                  shawkins

                                  I'll have someone from the Designer team comment on that exception.  It's likely already been resolved given the age of Designer.  If you are on the latest fix release of Designer for 6.2 you may want to open a support ticket for that.

                                  • 14. Re: Use of WHILE or LOOP ON for executing a procedure within VP
                                    blafond

                                    The fact that there is no Eclipse log indicates that the error is being thrown, caught and displayed as part of the transformation/SQL validation framework which utilizes a fork of the Teiid runtime query source.  There is only one class, Evaluator, which catches NoSuchElementException and is implemented to iterate on elements within XML, JSON and text files, which appears to be the case in your screen cap and this thread.

                                     

                                    I agree with Steve... maybe upgrade to latest Designer and 8.12.x Teiid for starters.

                                     

                                    Having a project set to debug with would be helpful if you can share it.

                                    1 2 Previous Next