Loading Data in a For loop

[FMDF]

I have order records that need to map on a chart.  There is a start and end date for each order line.  I need to create a row in a DateLink table for each month that the order line is open.

In my master calendar, I have a flag that tells me what the first day of the fiscal month is.

I only want to include the actual start date and then all "first day of fiscal month" values for each month.  This will allow me to map how I want later.

The first time through the loop IT WORKS.  The second time it fails with an error message that makes zero sense based on the code.

NoConcatenate OrdTemp:
Load
*
Resident Order_Temp4
WHERE IsNull(startDateEffective) = 0;

//Get the count of rows
LET numOrderRows = NoOfRows('OrdTemp');

//Loop through all the order rows
FOR i = 0 to $(numOrderRows)-1
Let vUniqueId = Peek('uniqueId',$(i),'OrdTemp');
Let vStartDate = Num(Date(Floor(Peek('startDateEffective',$(i),'OrdTemp'))));
Let vEndDate = Num(Date(Floor(Peek('endDateEffective',$(i),'OrdTemp'))));

NoConcatenate tCal:  /////THIS IS THE LINE THAT FAILS THE 2ND TIME THRU THE LOOP.  ERROR BELOW.
Load
DISTINCT
masterCalDateKey
Resident DIM_MasterCalendar
Where
masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
(masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
;


//Get the row count for the tCal table
LET numCalRows = NoOfRows('tCal');

//Loop thru the calendar records
FOR j = 0 to $(numCalRows)-1

//Get the next date key to add to the date link table.
vMasterCalDate = Peek('masterCalDateKey',$(j),'tCal');

Concatenate(MasterDateLink)
LOAD
$(vUniqueId) AS uniqueId,
$(vMasterCalDate) AS masterCalDateKey,
'Start Date Effective' AS dateType
;
Next;

Drop Table tCal;
Next;

This is the error:

Field 'USOrderDetail' not found: tCal1: Load DISTINCT masterCalDateKey Resident DIM_MasterCalendar Where masterCalDateKey = 38966 OR (masterCalDateKey > 38966 AND masterCalDateKey <= 39247 AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates

I don't even reference a field called "USOrderDetail" in this loop.  Ever.  I am completely stuck.

[FMDF]

I modified my original loop after noticing a flaw where the inner loop was not needed.  This runs but is still so slow as to be unusable.

//No need to link records if there is no startDateEffective.
OrdTemp:
NoConcatenate
Load
*
Resident Order_Temp4
WHERE IsNull(startDateEffective) = 0;

//Get the count of rows
LET numOrderRows = NoOfRows('OrdTemp');

//Loop through all the order rows
FOR i = 0 to $(numOrderRows)-1

     Let vUniqueId = Peek('uniqueId',$(i),'OrdTemp');
     Let vStartDate = Num(Date(Floor(Peek('startDateEffective',$(i),'OrdTemp'))));
     Let vEndDate = Num(Date(Floor(Peek('endDateEffective',$(i),'OrdTemp'))));

     IF $(i)=0 THEN
          NoConcatenate tCal:
          Load
               DISTINCT
               masterCalDateKey,
               '$(vUniqueId)' AS uniqueId,
               'Start Date Effective' AS dateType
               Resident DIM_MasterCalendar
          Where
               masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
               (masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND  masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
          ;
     ELSE
          Concatenate(tCal)
          Load
               DISTINCT
               masterCalDateKey,
               '$(vUniqueId)' AS uniqueId,
               'Start Date Effective' AS dateType
          Resident DIM_MasterCalendar
          Where
               masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
               (masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
          ;
     End If

     //Get the row count for the tCal table
     //LET numCalRows = NoOfRows('tCal');

Next;

Concatenate(MasterDateLink)
LOAD
     uniqueId,
     masterCalDateKey,
     dateType
Resident tCal;

Drop Table tCal;