4 Replies Latest reply on Sep 11, 2015 3:57 PM by Steven Hawkins

    Loop issue in Procedure

    virtualdatabase Apprentice

      I'm having an issue I can't figure out if I'm doing something incorrect or there's a bug..

       

       

      BEGIN

        DECLARE string VARIABLES.ExecSTring;

        DECLARE string VARIABLES.WhereClause;

        DECLARE string VARIABLES.Tempcounter;

        IF(MCDS_UTIL.proc_X_ByClientLayout.IN_Col2 IS NULL)

        BEGIN

        VARIABLES.WhereClause = '';

        END

        ELSE

        BEGIN

        VARIABLES.WhereClause = (((' WHERE  Col2 = ' || chr(39)) || MCDS_UTIL.proc_X_ByClientLayout.IN_Col2) || chr(39));

        END

        CREATE LOCAL TEMPORARY TABLE temp_2 (name string);

        CREATE LOCAL TEMPORARY TABLE TEMP_TJ (Col1 string, Col2 string, Col3 string, Col4 string, Col5 string, Col6 string, Col7string, Col8 string, Col9 string, Col10 string, Col11 date, Col12 date, Col13 string, Col14 string);

        LOOP ON (SELECT SYS.Tables.Name FROM SYS.Tables WHERE (SYS.Tables.Name LIKE (('%_' || MCDS_UTIL.proc_X_ByClientLayout.IN_ClientNum) || '_%')) AND (SYS.Tables.SchemaName = 'MCDS_src') AND (SYS.Tables.SchemaName <> 'MCDS') AND (lower(SYS.Tables.Name) LIKE (('%_' || MCDS_UTIL.proc_X_ByClientLayout.IN_LayoutNum) || '.X%'))) AS txncursor

        BEGIN

        VARIABLES.ExecSTring = (('select  Col1, Col2,Col3,Col4,Col5,Col6, Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14 FROM  ' || txncursor.Name) || VARIABLES.WhereClause);

        EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => VARIABLES.ExecSTring); <----I CAN SEE THE LOOP works HERE

        EXECUTE IMMEDIATE VARIABLES.ExecSTring AS Col1 string, Col2 string, Col3 string, Col4 string, Col5 string, Col6 string, Col7string, Col8 string, Col9 string, Col10 string, Col11 date, Col12 date, Col13 string, Col14 string INTO TEMP_TJ;

        VARIABLES.Tempcounter = (SELECT COUNT(*) FROM TEMP_TJ);

        EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => ('INSIDE LOOP:  ' || VARIABLES.Tempcounter)); <--- I SEE THIS BUT THE COUNTER is only showing the current Loop iteration... it SHOULD INCREMENT.. shouldn't it ?  IS a "commit" needed  ?

        END

        EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => ('Before RETURN Select:  ' || VARIABLES.Tempcounter));<---I SEE THIS BUT THE COUNTER is only showing the current Loop iteration...

        SELECT * FROM TEMP_TJ;<--- NO DATA is ever returned when I call the procedure

        BEGIN

        EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => ('AFTER RETURN Select:  ' || VARIABLES.Tempcounter)); <--- I NEVER SEE THIS

        END

      END

       

       

      Any thoughts/input/guidance Most appreciated!!

       

       

      -Tom

        • 1. Re: Loop issue in Procedure
          virtualdatabase Apprentice

          I've made *some* progress!

           

          This is the relevant part that I've changed...

          I not get rows returned, however, it's only for the LAST iteration of the loop... WHY?

           

          BEGIN

            VARIABLES.ExecSTring = (('select  COL1, COL2,COL3,COL4,COL5,COL6, COL7 ,COL8,COL9,COL10,COL11,COL12,COL13,COL14 FROM  ' || txncursor.Name) || VARIABLES.WhereClause);

            EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => VARIABLES.ExecSTring);

            EXECUTE IMMEDIATE VARIABLES.ExecSTring AS COL1 string, COL2 string, COL3 string, COL4 string, COL5 string, COL6 string, COL7 string, COL8 string, COL9 string, COL10 string, COL11 date, COL12 date, COL13 string, COL14 string INTO #TEMP_TJ11;

            VARIABLES.Tempcounter = (SELECT COUNT(*) FROM TEMP_TJ);

            INSERT INTO TEMP_TJ (TEMP_TJ.COL1, TEMP_TJ.COL2, TEMP_TJ.COL3, TEMP_TJ.COL4, TEMP_TJ.COL5, TEMP_TJ.COL6, TEMP_TJ.COL7, TEMP_TJ.COL8, TEMP_TJ.COL9, TEMP_TJ.COL10, TEMP_TJ.COL11, TEMP_TJ.COL12, TEMP_TJ.COL13, TEMP_TJ.COL14) SELECT * FROM #TEMP_TJ11;

            VARIABLES.Tempcounter = (SELECT COUNT(*) FROM TEMP_TJ);

            EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => ('INSIDE LOOP:  ' || VARIABLES.Tempcounter));

            END

            EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => ('Before RETURN Select:  ' || VARIABLES.Tempcounter));

            SELECT * FROM TEMP_TJ;

          END

          • 2. Re: Loop issue in Procedure
            Steven Hawkins Master

            > I not get rows returned, however, it's only for the LAST iteration of the loop... WHY?

             

            In what you have in the second post you should see all of the rows.  It would be better though to put the final select outside of the loop.

             

            However it should be unnecessary to use an intermediate table.  I do see an issue with the execute immediate in that it is looking only in the most immediate scope to see if the target temp table exists.  So in your first post, it's effectively creating a shadow TEMP_TJ for each iteration.  This will need an issue to fix.

            • 3. Re: Loop issue in Procedure
              virtualdatabase Apprentice

              Thanks Steve!

              Yes, the only reason I am using the intermediate table is the exec immediate didn't seem to work at all.

              I'm unsure as to where you suggest I move the "SELECT * FROM TEMP_TJ" statement.

              Please clarify..

               

              -Tom

              • 4. Re: Loop issue in Procedure
                Steven Hawkins Master

                I just mean that the select from the accumulation temp table can be done after the loop.  Shown in a simplified form:

                 

                begin

                  create local temporary table t1 (e1 string);

                  loop on (select 1 as a union all select 2) as c

                  begin

                    execute immediate 'select c.a' as e1 string into #t2;

                    insert into t1 select * from t2;

                  end

                select * from t1;

                end

                 

                As for being able to target the outer temp table [TEIID-3699] execute immediate only checks immediate parent scope - JBoss Issue Tracker was logged for that.