[SOLVED] Guided ad-hoc report with multiple tables in separate segments
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 -RUNThis message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 7.6 Windows, All Outputs
March 10, 2015, 07:36 PM
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:
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
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
Alan. WF 7.705/8.007
March 17, 2015, 10:03 AM
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.