topic Re: Repeat the same SQL queries for multiple ODBC connections in QlikView

Repeat the same SQL queries for multiple ODBC connections

Tue, 22 Nov 2016 10:34:16 GMT

Hi everyone.

I want to find an efficient way to repeat the same LOAD statements and SQL queries for multiple ODBC connections. My current script looks like this:

LIB CONNECT TO 'DB1';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB2';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB3';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB4';

LOAD ... ;

SQL ...;

Is there a better way to do this? Because the SQL queries are quite long, can we avoid repeating the same

LOAD ... ;

SQL ...;

Re: Repeat the same SQL queries for multiple ODBC connections

adamdavi3s — Tue, 22 Nov 2016 10:40:31 GMT

Can you just pop them in an include script and call that each time?

That is what I would do but there might be a better way.

Re: Repeat the same SQL queries for multiple ODBC connections

maxgro — Tue, 22 Nov 2016 10:43:54 GMT

you can set 2 variables for load and sql

SET vLoad = Load FIELD1, FIELD2;

SET vSQL = SQL SELECT

FIELD1, FIELD2

FROM TABLE1

where ..........;

LIB CONNECT TO 'DB1';

$(vLoad);

$(vSQL);

LIB CONNECT TO 'DB2';

$(vLoad);

$(vSQL);

or maybe also in a for loop on your DB1...2

Re: Repeat the same SQL queries for multiple ODBC connections

Anonymous — Tue, 22 Nov 2016 10:45:34 GMT

Maybe a loop to keep on changing the connection like below?

>> List your connections here or load from excel file
DBConnList:
LOAD * INLINE [
    DBName
    DB1
    DB2
    DB3
    DB4
];

LET vRowCount = NoOfRows('DBConnList');

for a = 1 to $(vRowCount)

let vValueToRead = FieldValue( 'datafiles', $(a));

LIB CONNECT TO $(vValueToRead)

LOAD ... ;

SQL ...;

Re: Repeat the same SQL queries for multiple ODBC connections

Peter_Cammaert — Tue, 22 Nov 2016 12:39:21 GMT

Or maybe even simpler with a FOR EACH

FOR EACH vConnectionString in 'String1', 'String2', 'String3'

CONNECT TO [$(vConnectionString)];

Table:

LOAD * FROM Table; // Will perform an AUTOCONCATENATE if tables are identical

DISCONNECT;

Best,

Peter

Re: Repeat the same SQL queries for multiple ODBC connections

Wed, 23 Nov 2016 01:51:27 GMT

Thank you all for your ideas.

All answers are awesome!