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(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.
It's pretty clear to me that it's a data issue. I have found that it fails at a particular spot (can't identify the exact record, but it's somewhere around the 262,000th record). Does anyone have a good way of debugging a data issue? I'd like to be able to output the record that the load script chokes on so I can inspect it, but I am not sure how to do that.
Apparently memory was the issue, or something like it. I was able to run the same code on a variety of data sets and it seemed as though it would only crash if I was loading >260,000 records. Loading different portions of the record sets (e.g., loading records 200,000 - 300,000) did not cause an error, so it's not a data issue. When I got to certain row counts, it would fail consistently.
I switched from a Lookup function to using an Exists and did not encounter errors when running the full load. I'm still fighting with the logic I need to get to my desired result, but I think I'm past the errors that were plaguing me for now.