I changed a bit of code in a load script yesterday to handle a bug. I ran through a quick limited load test on 10 rows to verify my syntax (it worked) then pushed the code to my QC environment to do a full load. The script failed with a "General Script Error". I went back to my dev environment and ran more limited loads, increasing the order of magnitude from 10 to 100 to 1000 to 10,000 to 100,000. Each time the script ran successfully. So I let it run overnight to load the million plus records and came in to find that it failed. I'm assuming that it's a data issue of some sort, but I'm curious if anyone can tell me if there is something in the new code I introduced that is likely to trigger the error. Here's my code, with the new field bolded:
if(PassAnnualFlag = ID, 'Y',
if(NoPassAnnualFlag = ID AND ISNULL(lookup('ID', 'PassAnnualFlag', ID)),
if(PassAnnualAttempt = ID, 'Y', if(NoPassAnnualAttempt = ID, 'Y', 'N')) as BestAnnualAttempt,
if(PassOverallAttempt = ID, 'Y', if(NoPassOverallAttempt = ID, 'Y', 'N')) as BestAttempt,
if(PassLatestAttempt = ID, 'Y', if(AttemptCount > 4 and NoPassLatestAttempt = ID, 'Y', 'N')) as LastAttempt,
ORDER BY ID, PYSort, MyDate desc;
I haven't used the ISNULL operator much so I'm wondering if I need to handle that differently, i.e., test it's value for -1 or 0?
For a bit of context, what I'm trying to do is look through a set of records (where ID is the "primary key" and there are N records per ID) to see if I have ever set the PassAnnualFlag. If I have not done so, I want to set the BestAnnualAttemptNew flag to 'Y' when the NoPassAnnualFlag contains the same value as the ID for that record. If I have set the PassAnnualFlag, I do not want to set the BestAnnualAttemptNew flag to 'Y' even if the NoPassAnnualFlag matches the ID for that record.
Thanks in advance for any assistance.