drop tables in a FOR loop

[#1]

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.

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.

[#2]

no one response?

[rwunderlich]

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

[CELAMBARASAN]

Hi,

     Check with this

for i=1 to 3

          SQL insert into etl_log values (1);

          ETL_WorkFlow:

          SQL Select * from ETL_WorkFlow;

          DROP Table ETL_WorkFlow;

NEXT i;

Celambarasan

[#5]

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;

[#6 · Accepted solution]

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