SYMPTOM:
Two temp tables are created as follows:
Temptable1
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Temptable2
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data | Datetime, Date or Time depending on data |
The final pass of the SQL appears as follows:
Select pa1.Project_id Project_id,
pa1.Start_id Start_id,
pa1.End_id End_id,
pa1.WJSBFS1 WJSBFS1,
pa2.WJSBFS1 WJSBFS2
From Temptable1 pa1
Left outer join Temptable2 pa2
On pa1.Project_id = pa2.Project_id and
pa1.Start_id = pa2.Start_id and
pa1.End_id = pa2.End_id
The report returns the following:
Notice in the screen shot above that the Data for Project B is missing.
CAUSE:
In the example above, the final pass compares End_id of Temptable1 and End_id of Temptable2. If two columns both have null values, those columns are not recognized as "Equal".
The Strategy SQL Generation Engine does not support null values in attribute ID columns. Using End_Date as in attribute ID form is incorrect modeling for the Strategy SQL Generation Engine.
WORKAROUND:
For the 'End date' attribute, create two forms, ID and DESC. The ID form is that which the Strategy Engine uses to perform the joins, and the DESC form will be what is actually displayed on the report.
The following steps demonstrate how two null values can be compared:
Now, the joins will occur as desired, but the report display will show empty cells for the Null Close Dates, as illustrated below:
