I am using AppStudio to build a guided ad-hoc report.

I have view in db which contains only entities ids.
I have joined this views with other views, containing entities details.

Since I need to use columns from joined views in a sort I created hold file to keep only the columns needed for sort.
I am not sure if that is right solution, but without that there is an error when trying to use fields which are not on the same level:
(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH

Then I am joining the hold file with details views.

Based on that guided add-hoc is build, it contains one section with sort fields and 2 with detail fields, each section represents 1 variable.
The problem is that all the join statements with this hold file create multiple select statements being executed in the database.
There is a select called for each row on the hold file, there are tens of thousands of selects called and the report runs for a unmanageable amount of time.

Can anybody comment on the solution and give us better guidance on how to approach this problem?
What a correct way to create guided ad-hoc report with multiple tables in separate segments?


JOIN
INNER VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.APPLICATION_ID IN vf_app_seq_event
TO MULTIPLE VD_APPLICATION.VD_APPLICATION.APPLICATION_ID IN vd_application
TAG J3 AS J3
END
JOIN
INNER VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.EVENT_ID IN vf_app_seq_event
TO MULTIPLE VD_EVENT.VD_EVENT.EVENT_ID IN vd_event TAG J4 AS J4
END
TABLE FILE VF_APP_SEQ_EVENT
PRINT
VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.APPLICATION_ID
VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.EVENT_ID
J3.VD_APPLICATION.APPLICATION_NAME
J3.VD_APPLICATION.APPLICATION_CODE
J3.VD_APPLICATION.APPLICATION_STATUS_NAME
J4.VD_EVENT.EVENT_CODE
J4.VD_EVENT.EVENT_NAME
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN

JOIN
INNER HOLD1.HOLD1.APPLICATION_ID IN HOLD1 TO MULTIPLE
VD_APPLICATION.VD_APPLICATION.APPLICATION_ID IN vd_application TAG J9 AS J9
END
JOIN
INNER HOLD1.HOLD1.EVENT_ID IN HOLD1 TO MULTIPLE VD_EVENT.VD_EVENT.EVENT_ID
IN vd_event TAG J0 AS J0
END
TABLE FILE HOLD1
PRINT
&RP_FN06P_Detail6.(AND(,,)).Please select detail field(s).
&RP_FN09P_Detail9.(AND(,,)).Please select detail field(s).
BY &RP_FN01B_Sort1.(BY(,,,,)).Please select sort field(s).
ON TABLE SET ONFIELD IGNORE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN

This message has been edited. Last edited by: <Kathryn Henning>,

The data structure you have constructed basically links cartesian keys of application_id and event_id in a one-to-many relationship to both application details and event details. This creates a multi-path structure which is where the issue you are having arises, printing from one path and sorting on another path. Creating a hold file and then joining back to the data tables will not help performance either.

Is there really a one-to-many relationship between the application_id in vf_app_seq_event and the application_id in vd_application and also a one-to-many relationship between the event_id in vf_app_seq_event and event_id in vd_event?

If these are actually one-to-one relationships then changing the joins from 'multiple' to 'unique' will provide a single path structure that can have print and sort at different levels directly on the structure.

However if these are one-to-many relationships, then you may be able to alter the structure such that a single path is created. This could be constructed by using:

JOIN
   INNER     VD_APPLICATION.VD_APPLICATION.APPLICATION_ID       IN vd_application      TAG APP
TO UNIQUE    VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.APPLICATION_ID   IN vf_app_seq_event    TAG SEQ AS J1
END
JOIN
   INNER     VF_APP_SEQ_EVENT.VF_APP_SEQ_EVENT.EVENT_ID         IN vd_application
TO MULTIPLE  VD_EVENT.VD_EVENT.EVENT_ID                         IN vd_event            TAG EVT AS J2
END

The example I posted contains only subset of views I need to join.
VF_APP_SEQ_EVENT is a fact table and the other views join to this table through many to 1 relationships, where many is on the site of VF_APP_SEQ_EVENT.

Can a single path way be achieved using cluster joins?
I have tried to work through it in the following way, but it does not seem to give me single path way.

FILENAME=VF_APP_SEQ_EVENT_ADHOC, SUFFIX=SQLORA , $
SEGMENT=VF_APP_SEQ_EVENT, SEGTYPE=S0, $
FIELDNAME=APP_COUNTRY_ID, ALIAS=APP_COUNTRY_ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=PRODUCT_FAMILY_ID, ALIAS=PRODUCT_FAMILY_ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=APPLICATION_ID, ALIAS=APPLICATION_ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=COUNTRY_ID, ALIAS=COUNTRY_ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=EVENT_ID, ALIAS=EVENT_ID, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=EVENT_COUNTRY_ID, ALIAS=EVENT_COUNTRY_ID, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=SEQUENCE_ID, ALIAS=SEQUENCE_ID, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=SEQ_COUNTRY_ID, ALIAS=SEQ_COUNTRY_ID, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=SEQ_EVENT_ID, ALIAS=SEQ_EVENT_ID, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
SEGMENT=VB_APP_SECURITY, SEGTYPE=KU, PARENT=VF_APP_SEQ_EVENT, CRFILE=VB_APP_SECURITY, CRINCLUDE=ALL,
JOIN_WHERE=VF_APP_SEQ_EVENT.APPLICATION_ID EQ VB_APP_SECURITY.APPLICATION_ID;, $
SEGMENT=VD_APP_COUNTRY, SEGTYPE=KU, PARENT=VF_APP_SEQ_EVENT, CRFILE=VD_APP_COUNTRY, CRINCLUDE=ALL,
JOIN_WHERE=VF_APP_SEQ_EVENT.APP_COUNTRY_ID EQ VD_APP_COUNTRY.APP_COUNTRY_ID;, $
SEGMENT=VD_PRODUCT_FAMILY, SEGTYPE=KU, PARENT=VF_APP_SEQ_EVENT, CRFILE=VD_PRODUCT_FAMILY, CRINCLUDE=ALL,
JOIN_WHERE=VF_APP_SEQ_EVENT.PRODUCT_FAMILY_ID EQ VD_PRODUCT_FAMILY.PRODUCT_FAMILY_ID;, $
SEGMENT=VD_APPLICATION, SEGTYPE=KU, PARENT=VF_APP_SEQ_EVENT, CRFILE=VD_APPLICATION, CRINCLUDE=ALL,
JOIN_WHERE=VF_APP_SEQ_EVENT.APPLICATION_ID EQ VD_APPLICATION.APPLICATION_ID;, $
SEGMENT=VD_EVENT, SEGTYPE=KU, PARENT=VF_APP_SEQ_EVENT, CRFILE=VD_EVENT, CRINCLUDE=ALL,
JOIN_WHERE=VF_APP_SEQ_EVENT.EVENT_ID EQ VD_EVENT.EVENT_ID;, $

Thanks,
Elizabeth

including CRJOINTYPE=LEFT_OUTER in the SEGMENT definition seemed to give me what I needed.