Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Guided ad-hoc report with multiple tables in separate segments

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Guided ad-hoc report with multiple tables in separate segments
 Login/Join
 
Member
posted
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>,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
Virtuoso
posted Hide Post
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


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
Member
posted Hide Post
including CRJOINTYPE=LEFT_OUTER in the SEGMENT definition seemed to give me what I needed.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Guided ad-hoc report with multiple tables in separate segments

Copyright © 1996-2020 Information Builders