Loop issue in Procedure

[virtualdatabase]

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

[virtualdatabase]

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

[shawkins]

> 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.

[virtualdatabase]

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

[shawkins]

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.