How to understand object type relationship in OpenPages

Check list of all posts

There are two ways to generate the same report with parent child relationship. You can see case 1 and 2 below. Loss impact is child of loss event.

Case 1

SQL generated by Case 1

select distinct "LOSSEVENT"."NAME00" "Loss_Event_Name", "LOSSIMPACT"."NAME00" "Loss_Impact_Name"

from ((

select *

from RV_LOSSEVENT

                      ) "LOSSEVENT" LEFT OUTER JOIN (

select *

from RT__LOSSEVENT_LOSSIMPACT

                      ) "LOSSEVENT_LOSSIMPACT" on "LOSSEVENT"."LOSSEVENT_ID"="LOSSEVENT_LOSSIMPACT"."LOSSEVENT_ID" and "LOSSEVENT"."REPORTING_PERIOD_ID"="LOSSEVENT_LOSSIMPACT"."REPORTING_PERIOD_ID") LEFT OUTER JOIN (

select *

from RV_LOSSIMPACT

                      ) "LOSSIMPACT" on "LOSSEVENT_LOSSIMPACT"."LOSSIMPACT_ID"="LOSSIMPACT"."LOSSIMPACT_ID" and "LOSSEVENT_LOSSIMPACT"."REPORTING_PERIOD_ID"="LOSSIMPACT"."REPORTING_PERIOD_ID"

where "LOSSEVENT"."NAME00"='31974' and ("LOSSEVENT"."OP_FLAG_READ_ACCESS"='Y' or "LOSSEVENT"."OP_FLAG_READ_ACCESS" is null) and ("LOSSIMPACT"."OP_FLAG_READ_ACCESS"='Y' or "LOSSIMPACT"."OP_FLAG_READ_ACCESS" is null)

Case 2

[Event].[Loss Event Reporting Period ID] = [Impact].[Loss Impact Reporting Period ID]and "OP_RPS_AUX.IS_REL_PARENT_CHILD"([Event].[Loss Event Resource ID], [Impact].[Loss Impact Resource ID], [Event].[Loss Event Reporting Period ID]) = 'Y'

SQL generated by Case 2

select distinct "Event"."Loss_Event_Name" "Loss_Event_Name", "Impact"."Loss_Impact_Name" "Loss_Impact_Name"

from (

select distinct "LOSSEVENT"."NAME00" "Loss_Event_Name", "LOSSEVENT"."REPORTING_PERIOD_ID" "Loss_Event_Reporting_Period_ID", "LOSSEVENT"."LOSSEVENT_ID" "Loss_Event_Resource_ID"

from (

select *

from RV_LOSSEVENT

                             ) "LOSSEVENT"

where "LOSSEVENT"."NAME00"='31974' and ("LOSSEVENT"."OP_FLAG_READ_ACCESS"='Y' or "LOSSEVENT"."OP_FLAG_READ_ACCESS" is null)) "Event" LEFT OUTER JOIN (

select distinct "LOSSIMPACT"."REPORTING_PERIOD_ID" "c1", "LOSSIMPACT"."LOSSIMPACT_ID" "Loss_Impact_Resource_ID", "LOSSIMPACT"."NAME00" "Loss_Impact_Name"

from (

select *

from RV_LOSSIMPACT

                             ) "LOSSIMPACT"

where "LOSSIMPACT"."OP_FLAG_READ_ACCESS"='Y' or "LOSSIMPACT"."OP_FLAG_READ_ACCESS" is null) "Impact" on "Event"."Loss_Event_Reporting_Period_ID"="Impact"."c1" and "OPENPAGES".OP_RPS_AUX.IS_REL_PARENT_CHILD("Event"."Loss_Event_Resource_ID", "Impact"."Loss_Impact_Resource_ID", "Event"."Loss_Event_Reporting_Period_ID")='Y'