5 Replies Latest reply: Mar 19, 2012 10:57 PM by cclcjr14 RSS

    drop tables in a FOR loop

      I  meet a  wried  issue, actually it is based on a wried requirement. Below is the steps to reproduce the issue I met.

       

      1. create 2 tables in SQL Server, you can also use oracle, etc.

      CREATE TABLE [ETL_Log (

                [logid] [int] NULL

      )

      GO

      CREATE TABLE ETL_WorkFlow (

                [WorkFlowID] [int] NULL ,

                [JobID] [int] NULL

      )

      GO

      Both these 2 tables are empty.

       

      2. create a new qvw file, edit script, create OLE DB connection, and then add below code:

      for i=1 to 3

                SQL insert into etl_log values (1);

                SQL Select * from ETL_WorkFlow;

                DROP Table ETL_WorkFlow;

      NEXT

       

      3. Try to reload data, you will get an exception that "table not found" . Try debug this script, this issue will happen when i >= 2

      After that, you can easily find that ETL_Log table has 3 records, means "SQL insert ..." works fine.

      But, if comment out the "SQL insert ..." line, everything works fine.

       

      So I researched this issue again, and then I found that "SQL insert ..." is not recognized as an Insert SQL. In "script execution progress" window, it shows that :

      ETL_WorkFlow 0 lines fetched

      insert into etl_log values (1) 0 lines fetched

      insert into etl_log values (1) << ETL_WorkFlow 0 lines fetched

      insert into etl_log values (1) << insert into etl_log values (1) 0 lines fetched

      insert into etl_log values (1) << ETL_WorkFlow 0 lines fetched


      Means, when i>=2, QlikView recognize SQL insert as a query for table "ETL_WorkFlow", and then it tried to fetch value into "ETL_WorkFlow".

       

      Also, try CTRL+T, you will see the tables like the screen-shot I attached here.

      1.PNG

      Very wried, can someone explain why this happen?

       

      BTW, if I add "NoConcatenate" before "SQL select ...", this exception will not happen. But the wried table like above screen-shot will still be there.

        • drop tables in a FOR loop

          no one response?

          • drop tables in a FOR loop
            Rob Wunderlich

            You are relying on default table names and that may be the problem. Try adding some explicit table labels like this:

             

            for i=1 to 3

                        LogValues:

                      SQL insert into etl_log values (1);

                DROP Table LogValues:

               ETL_Workflow:

                      SQL Select * from ETL_WorkFlow;

                DROP Table ETL_WorkFlow;

            NEXT

             

             

            -Rob

            • Re: drop tables in a FOR loop

              Thx for your reply, I have tried both your solution, not workable.

              Rob>> "DROP Table LogValues" will trhow exception because when i=1, this table doesn't exist. This table will only be created when i>=2. But this give me some idea, I will try IF not isnull(nooftables('LogVales')) then drop table later, maybe that can drop the log table.

               

              I tried another way to avoid exception:

              for i=1 to 3

                        SQL insert into etl_log values (1);

                        ETL_WorkFlow$(i):

                        Noconcatenate SQL Select * from ETL_WorkFlow;

                        DROP Table ETL_WorkFlow$(i);

              NEXT

               

              By this way, there has no exception.

              But, the table named as "SQL insert into etl_log values (1)" is still there when input ctrl+T.

               

              So I researched this issue again, seems this is caused by "QlikView recognize SQL insert as a SQL Select command", so after execution, it tried to fetch values into some table.

              But, since this is an insert command, so no ResultSet return, of course no column can be find. And then, it will auto concatenate with previously table/fields. That's the reason why there exist a wried table, whose column names belong to ETL_WorkFlow , and table name belong to SQL insert.

               

              I am not sure if anyone tried to use INSERT/UPDATE/DELETE in load script at before, I think this is an general issue, and QV should have some workaround.

              BTW, does any one have good idea to catch exception? I know ScriptErrorCount can be used to identify if exception happened, but what I want is syntax like JAVA/Oracle. E.g:

               

              Java:

              try{

                   int a;

                   a=1/0;

              }

              catch (exception e) {

                   System.out.println(e.getmessage());

              }

               

              Oracle:

              Begin

              a:=1/0;

              exception

              when others then

                   dbms_output.putline('div 0 exception');

              end;

                • Re: drop tables in a FOR loop

                  Resolved.

                  Below is the code.

                   

                  for i=1 to 3

                            sql_insert:

                            SQL insert into etl_log values (1);

                            if NOT ISNULL(TableNumber('sql_insert')) then

                                 drop table sql_insert;

                            endif

                            ETL_WorkFlow:

                            SQL Select * from ETL_WorkFlow;

                            DROP Table ETL_WorkFlow;

                  NEXT

                   

                   

                  The root cause of this issue is : QlikView recognize "SQL insert into etl_log values (1);" as a query command, so it tried to fetch resultset into a table.

                  But, since this sql will not return any resultset, so "concatenate" happen, it auto concatenate with etl_workflow in the 2nd round. That's the reason why this issue will only happen when i>=2

                   

                  I am not sure if this is the best solution, for me, at least it is a useful workaround, hope it will be helpful for you too