Question

Report doesnt show a record which its relation is NULL

0

I use OQL below to create a report FROM ItemMgt.Item AS Item JOIN Item/ItemMgt.Item_LearningArea/BaseData.LearningArea AS LearningGroup WHERE Item/ItemMgt.Item_KeyStage = $KeyStage AND Item/ItemMgt.Item_LearningArea = $LearningArea AND Item/ItemMgt.Item_Indicator = $Indicator GROUP BY LearningGroup.GroupName SELECT LearningGroup.GroupName AS LearningArea, COUNT(DISTINCT(Item/ID)) AS Total And the association between item and indicator is many-to-many. When I doesn't choose the indicator and generate the report. It shows only records that have association with the indicators and it doesn't show any records that don't have association with the indicators. I need to see all the record. Did I do anything wrong?

asked 2013-10-08

Siwapong Kongsilp

4 answers

Chris de Gelder · Answer 1 · 2013-10-08

You have to test separate for the empty association

see this question

David Sanders · Answer 2 · 2013-10-08

I think you need to use LEFT OUTER JOIN to also return records without the association set

Siwapong Kongsilp · Answer 3 · 2013-10-08

Both directions don't work. I've already tried left join, right join and full outer join, but it cannot solve the problem.

Bart Tolen · Answer 4 · 2013-10-09

Maybe you can try

AND (Item/ItemMgt.Item_Indicator = $Indicator OR $Indicator IS NULL)