Store QVD loop failing in multiple ODBC connections

[Anonymous]

Hi

I have a scenario , where my qvw has 3 ODBC connections in it, here I implemented for loop for  storing to qvd's and dropping tables.

But the problem is  the for loop is not storing any qvds as it is entering to the next ODBC connection,

pls suggest..

SUB STORETABLES

FOR i =0 to NoOfTables()-1

LET vTabName =TableName(0);

STORE $(vTabName) into $(vTabName).qvd (qvd);

DROP Table $(vTabName);

NEXT i

END SUB

ODBC1 Conn;

load * from ODBC1;

ODBC2 Conn;

Load * from ODBC2;

John

[Peter_Cammaert]

This is not QlikView script code. Pseudo code can't be executed. Can you post your script, or at least the part that doesn't function?

Peter

[marcus_sommer]

I have noted the following:

SUB STORETABLES

    FOR i =0 to NoOfTables()-1

    LET vTabName =TableName($(i));

    STORE $(vTabName) into $(vTabName).qvd (qvd);

    DROP Table $(vTabName);

NEXT i

- Marcus

[Peter_Cammaert]

Marcus, that's a trick that occurs in all those dump&delete subroutines. The DROP Table statement pushes the next table to the top of the stack, becoming TableName(0) again. Without DROP Table statements, you would be right.

P.

[Anonymous]

Hi Marcus, there is no problem in the loop if  i have one ODBC connection, the problem is arising when we have multiple ODBC connections

[marcus_sommer]

Yes, of course I haven't looked carefully enough because I use a slight different approach:

// drop tables from generators

for i = NoOfTables() - 1 to 0 step - 1

    let vTable = tablename($(i));

    drop tables [$(vTable)];

next

let i        = null();

let vTable   = null();

And brackets by [$(vTable)] avoid potential problems.

- Marcus

[Peter_Cammaert]

Hmmm. There is sometihng I don't get. In a QlikView script, you cannot have multiple ODBC connections. They will be serialized, and the one from the last CONNECT statement will remain active if you do not use a CLOSE DISCONNECT statement.

Care to post your script to this thread?

Peter

Edit: sorry, it's DISCONNECT as Marcus correctly pointed out.

[marcus_sommer]

Normally a new connection will replace an older one but maybe as DISCONNECT; could be helpful.

- Marcus

[Anonymous]

sure Peter, this is what I written exactly in the script,

SUB STORETABLES

FOR i =0 to NoOfTables()-1

LET vTabName =TableName(0);

STORE $(vTabName) into $(vTabName).qvd (qvd);

DROP Table $(vTabName);

NEXT i

END SUB

ODBC Connection string ORACLE;

COST:

SQL SELECT *

FROM "AM_DDR".dbo."COST";

OLEDB Connection string SQL;

Inventory:

SQL SELECT *

FROM "AM_DDR".dbo."Inventory";

CALL STORETABLES

John

[Peter_Cammaert]

Thanks John.

When you execute this script, do none of the tables get exported to QVD, or only some?

If you put the CALL in comments and execute again, does your Table Viewer show all required tables?

Do they all contain rows? (check the pop-ups when hoovering over the tables in Table Viewer)

Peter

Edit: changed the ugly condensed block into something more readable...