Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loop for SQL statement

Hi everyone,

I've got a script of 15 sql statements. All the statements are for one customer only (see below).

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table1

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table2

.....

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table15

I need to create a loop for all Customers listed in another sql table (over 300 records) and add a dimension for Customer to the script (db name). Could anyone help with the code, please?

BR,

Przemek

7 Replies
Anonymous
Not applicable
Author

Przemec,
I assume the Customer names are the table names. If this is correct, here is what you can do.
First, you need the number of Customers. It can be defined this way:
LET NumberOfCustomers = NoOfRows('table'); // table is the list of your Customers
Now you can use the loop:
FOR i = 0 to $(NumberOfCustomers)-1
LET C= peek('Customer', $(i), table)
SQL SELECT ... FROM $(C);
NEXT
I didn't test exactly this script, so it may require some adjustments.

Not applicable
Author

Thank you for the reply, Micheal.

I've tested the script and feel we're almost there. I get Incorrect syntax near 'FROM' error so there must be just a small mistake. Do you have any idea what it could be?

BR,

Przemek

Anonymous
Not applicable
Author

Take a look at the log file, it can show what is going on.

Not applicable
Author

Michael,

first Customer goes ok but next ones don't get db.table after FROM and end up with the log (script reduced to 3 loops):

0015 Table1:

0016 SQL SELECT

0017 FROM CDN_Configuration.CDN.Bases

1 fields found: Customers, 183 lines fetched

0021 FOR i=0 TO 2

0022 LET C = peek('Customers',0,Table1)

0023 SQL SELECT Document FROM CDN_Customer1.CDN.VatNag

1 fields found: Document, 1 lines fetched

0024 NEXT

0022 LET C = peek('Customers',1,Table1)

0023 SQL SELECT Document FROM

Error: SQL Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'FROM'.

SQL Scriptline:

SQL State:37000

0024 NEXT

0022 LET C = peek('Customers',2,Table1)

0023 SQL SELECT Document FROM

Error: SQL Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'FROM'.

SQL Scriptline:

SQL State:37000

0024 NEXT

Execution finished.

The wholAny idea what might be the reason?

BR,
Przemek

Anonymous
Not applicable
Author

It helps, thanks.
Looks like the variable C wasn't defined in the 2nd and 3rd loops. My syntax for peek() function wasn't quite correct. I'm sure that the table name should be in single quotes as well: peek('Customers',$(i),'Table1')
(Without quotes, the table is not properly defined, and peek() takes Customer field from the last loaded table by default. When it was Table1 - it had this field, and you have one correct C. Next time it's a new table, no Customer field - no value for C.)

Not applicable
Author

Marvelous! It works perfectly 🙂 Just one more thing - I need to load 15 sql tables for all customers. I would like to load Customers table once and only add tablename at each sql statements i.e.

LET NumberOfCustomers = NoOfRows('table');

FOR i = 0 to $(NumberOfCustomers)-1

LET C= peek('Customer', $(i), 'Table1')

SQL SELECT ... FROM $(C)+tablename;

NEXT

When I added it as above I got an error Incorrect syntax near '+'. Do you know how to fix it?

BR,

Przemek

Anonymous
Not applicable
Author

Getting there... Cool
You have to change the variable C itself:
...
LET C= peek('Customer', $(i), 'Table1') & tablename
SQL SELECT ... FROM $(C);
...
If it doesn't work - see in the log file if the script creates the correct table name, and adjust if needed.