topic Nested Loop in App Development

Nested Loop

SilviyaK — Tue, 07 Feb 2023 09:11:56 GMT

Hi all,

Can anyone tell me why the below is not working:

For vRow = 1 to NoOfRows('Events_Data')

Let vID = Peek('ID',vRow-1,'Events_Data');

For vRow_Att = 1 to NoOfRows('Attendees_Data')

Let vID_Att = Peek('ID_Att',vRow-1,'Attendees_Data');

LIB CONNECT TO 'Connection_Name';

RestConnectorMasterTable:
SQL SELECT
.

FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(
URL "https://***-***.***.com/**/***/$(vID)/***/***",
QUERY "filter" "attendee.id eq '$(vID_Att)'",
HTTPHEADER "Authorization" "Bearer $(vToken)"
);

Next;

I am trying to do a nested loop, because I have different event IDs and different attendee IDs and my idea is to go through each event ID from my Events_ID table and get data for each attendee ID from my Attendee_ID table. If I run the above script it just never ends... While if instead of the vID_Att variable I type a specific attendee ID it works. So it seems I just need to correctly go through all attendee IDs.

Please help as it is kind of urgent to be completed. Any ideas will be appreciated.

Re: Nested Loop

marcus_sommer — Tue, 07 Feb 2023 12:13:31 GMT

The second loop iterates with the same variable as the first loop which is probably not intended. Beside this it's quite unclear if both loops with the variable-assigning per peek-values would work like expected - because for such a use-case they would need to be appropriate synchronized.

Simpler as this seems for me to merge both information into a single table which is then read within a single loop.

As first step I suggest you reduced the tables to maybe a dozens records, commenting the load-part and viewing the loop-results within TRACE statements or just writing them within another table, like:

load *, rowno() as RowNo;
load $(vRow) as vRow, $(vRow_Att) as vRow_Att, '$(vID)' as vID, '$(vID_Att)' as vID_Att
autogenerate 1;

If the results are really suitable you could increase the data-set and checking it again and after that you may try your real load.

Re: Nested Loop

vinieme12 — Wed, 08 Feb 2023 03:42:32 GMT

why not consolidate Events and Attendees in a single table using a Join ?

and then do a single loop instead of nested loops