For Each Loop not working

[wfortuny]

For Each Loop not working:

I am trying to load in data from a single field into a value list to use in a For Each Loop.  I can get the data loaded, but then when trying to use in the for each loop I keep getting the “Field ‘Dataset’ not found”.

Here’s the section of code I’m working with:

[Databases]:

NoConcatenate

SELECT DATABASE

  FROM SYSTEM.ADMIN.DATABASE

where (DATABASE <> 'FIELD_INT' and DATABASE LIKE 'FIELD%');

TempDatabases:

load concat(Chr(39) & DATABASE & Chr(39), ',') as concatenatedDatabases resident [Databases];

let Dataset = peek('concatenatedDatabases');

trace $(Dataset);

For each fy in FieldValueList('Dataset')

[EMPS]:

SELECT DISTINCT

    AA.EMP_ID_NO,

    AA.EMP_LNAME,

    AA.EMP_FNAME,

    (AA.EMP_LNAME || ', ' || AA.EMP_FNAME) AS EMP_NAME

    FROM $(fy).TABLE AA

    WHERE AA.LOCATION_ID = '42';

NEXT

After I run the code, I get the error.  If I run only this piece:

[Databases]:

NoConcatenate

SELECT DATABASE

  FROM SYSTEM.ADMIN.DATABASE

where (DATABASE <> 'FIELD_INT' and DATABASE LIKE 'FIELD%');

TempDatabases:

load concat(Chr(39) & DATABASE & Chr(39), ',') as concatenatedDatabases resident [Databases];

let Dataset = peek('concatenatedDatabases');

trace $(Dataset);

I get the desired output for Dataset, which is the list of values I want to loop with:

Lines fetched: 1

'FIELD06','FIELD07','FIELD08','FIELD09','FIELD10','FIELD11'

Please help.

[Saravanan_Desingh]

So Dataset is a variable right? Try this,

For each fy in FieldValueList($(Dataset))
[EMPS]:
SELECT DISTINCT
    AA.EMP_ID_NO,
    AA.EMP_LNAME,
    AA.EMP_FNAME,
    (AA.EMP_LNAME || ', ' || AA.EMP_FNAME) AS EMP_NAME
    FROM $(fy).TABLE AA
    WHERE AA.LOCATION_ID = '42';
NEXT

[wfortuny]

I tried that, but of course right after I posted this question I found the solution.  In the call to the for each loop I changed to this:

For each fy in $(Dataset)

and that worked, not sure why the "FieldValueList()" didn't work?

[Saravanan_Desingh]

Oh yes. You are right. My bad.

Dataset is not a Field. Its a variable. So, FieldValueList won't work here. Nice catch.

[wfortuny]

Only took me about 6hrs to find this, darn it. 🤣

Thank you for the quick support!

[Saravanan_Desingh]

you welcome

[Saravanan_Desingh · Accepted solution]

One more thought. If you still want to use the function, you can try using the Field directly.

FieldValueList('DATABASE')

[wfortuny]

oh man, of course, that worked too! Thank you.👌