Basic QV Arrays and Loops

[#1]

Hi,

I am attempting to load a number of tables from an SQL database too large to fit entirely into memory. Therefore, I need to be selective in which tables and columns are SELECTed.I need to use the output from selecting data in Table1 to determine which rows are selected from Table2. Basically I want to:

SQL SELECT Var1 FROM Table1 WHERE ConditionVar>1;

then use these values for Var1 to select each row in Table2 with a matching Var1 (and exclude all those which do not match).

Apparently, MSSQL does not support arrays, so it is impossible for me to store the output from the first SELECT statement into an SQL array and then use that to pull Table2.

I tried doing this in Qlikview as such, but I think VB.NET syntax may have rubbed off onto me a bit too much:

SET IDVar = SQL SELECT Var1 FROM Table1 WHERE ConditionColumn>1

FOR EACH y in $(IDVar)

SQL SELECT * FROM Table2 WHERE Var1 = '$(y)';

NEXT

This doesn't work at all! Can anyone please tell me a working way of doing what I'm trying to do?

[#2]

I think you can get it with a subquery

SQL SELECT * FROM Table2 WHERE Table2.Var1 IN (SELECT Table1.Var1 FROM Table1 WHERE ConditionVar>1);

[Miguel_Angel_Baeyens]

Hi Ben,

I think your idea is good enough. Giving a try to your code, something like the following should work, although loops are slower:

DataToLoad:LOAD Concat(Var1, chr(39) & ',' & chr(39)) AS Var1;SQL SELECT Var1 FROM Table1 WHERE ConditionColumn>1; LET vConditionToLoad = chr(39) & FieldValue('Var1', 1) & chr(39); FOR EACH y in $(vConditionToLoad) Data: LOAD *; SQL SELECT * FROM Table2 WHERE Var1 = '$(y)'; NEXT

Some things have to be taken into consideration:

1.- The chr(39) stuff is needed since text literals must be single quoted. It may be some syntax mispelling in my example.

2.- Table "Data" will be concatentated implicitly, meaning all records pulled in each iteration will be appended to the already loaded.

3.- There may be some limitations to the number of characters allowed in a variable, so "vConditionToLoad" may have some limitations, being needed to do some extra loops to get all possible values.

Hope that helps.