I'm not sure if I have encountered a bug or maybe that group by can only be performed on x number of records ? I have an applications where I join two tables and a group by on one of them.
Simplified example:
TableA:
Load
Key, RegistrationDate, Department
From xx;
TableB:
BKey as Key, Action_Code, ActionDate
From yy
Where ActionDate >=to_date('2009-01-01','yyyy-mm-dd');
TableC:
Key, Action_Code as MaxAction_Code,
Max(date(date("ActionDate",'DD-MM-YYYY'))) as "MaxActionDate"
Resident TableB
Group by Key, Action_Code;
I only need to see records where ActionDate from TableB is greather than or equal to 2009, but I need to see it for all keys from TableA. I need to make a group by from TableB as Action_Code per ActionDate and BKey can be more than one record but should only count/be displayed as one.
TableA and TableB load without problems but TableC fails with the following error:
05-02-2010 16:33:41: 0118 group by "Key","Action_Code"
05-02-2010 16:41:51: General Script Error
05-02-2010 16:41:51: Execution Failed
05-02-2010 16:41:51: Execution finished.
The strange thing is that if I limit TableA to load only records with RegistrationDate <=2009-06-30, TableC loads without any errors (no synthetic keys - everything ok).
If I load TableA with RegistrationDate <=2009-10-01 TableC fails.
If I load RegistrationDate >=2007-01-01 no problems either (600.641 lines fetched from TableA, 6.916.410 lines fetched from TableB, 3.824.216 lines fetched from TableC). But if I load RegistrationDate >=2006-01-01 TableC fails (748.225 lines fetched from TableA, 7.369.643 lines fetched from TableB, TableC fails).
I first thought that there was some faulty data that caused the error, but as I can load 1992 till June 2009 without problems and 2007 till today without problems that doesn't seem to be the case.
Could it be that group by can only be performed on a limited amount of data? I'm using version 8.5
