Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ...;
"?
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;
NEXT
Best,
Peter
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.
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
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 ...;
NEXT
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;
NEXT
Best,
Peter
Thank you all for your ideas.
All answers are awesome!