Loop through a data set

[Sasja]

I hope someone can help me with my chalenge:

I am getting a dataset through a SQL query. That is not the challenging part.  But for every record in that dataset, I want to do more SQL queries.

The first dataset contains variables where the new data need to be reed from.

Al the new datasets I want to land in new datasets. For every record in the first dataset I want to put records in the same tabel as the other records.

for example:

Variables:
SQL
  Select Naam
  , Var1
  , Var2
  , Var3
  From Person
;

For each Naam in Variables
Table1:
SQL
Select Naam
,    Field1
    , Field2
    From Var1
;
Table2:
SQL
Select Naam
,    Field1
    , Field2
    From Var2
;
Table3:
SQL
Select Naam
,    Field1
    , Field2
    From Var3
;
Next Naam

I tried 'Peek' and 'subfield' but both did not gave me the answer.

Probably I am doing it wrong. Is there anybody who already has experience with a simulair challange/problem?

Thank for your help/reply.

Sasja

[marcus_sommer · Accepted solution]

The loop for multiple sql isn't quite clear for me but you may use something like this:

for i = 0 to noofrows('Variables') - 1
   vNaam = peek('Naam', $(i), 'Variables');
   vVar1 = peek('Var1', $(i), 'Variables');
   vVar2 = peek('Var2', $(i), 'Variables');
   vVar3 = peek('Var3', $(i), 'Variables');
next

which would assign the field-values in each iteration to the variables - which you could apply within the sql-statements like: $(vVar1) or '$(vVar1)' depending if the content is a number or a string.