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] Distinct request to cluster MFD

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Distinct request to cluster MFD
 Login/Join
 
Gizmo
Gold member
posted
I'm trying to allow the users to select the columns that they would like to see on their report from a clustered master that retrieves data for MSSQL.

For the columns that they have selected, I only want to show the distinct values and have only the joins in effect that are necessary to retrieve the data (2 Million rows would be retrieved otherwise).

This works fine while the user only selects 32 or less columns since WebFOCUS translates DST. to both distinct and order by which also hits a WebFOCUS limit with the number of sort fields.

I'm looking at using DM generate passthrough SQL that would just do the distinct, but then I would also need to go back and query the MFD to get the column titles as well. This is likely to be a bit difficult (especially just determining the needed joins), so I'm sending a request here to see if anyone might have any ideas of how this could be done in an easy fashion.

This message has been edited. Last edited by: Gizmo,


Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
GamP
Virtuoso
posted Hide Post
These things are actually not easy. The approach I would take is by doing a CHECK FILE master HOLD and then use the held data to figure out the joins needed (by looking at the segment names of the selected field names). And then try to get where I need to be step by step. I think that all information needed to build your SQL request is available in the hold of the master file and the fields selected by the user.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gizmo
Gold member
posted Hide Post
Thanks GamP,

Unfortunatly, I could not find the join information in either the check file output nor any of the systables.

All of the join logic is being done through dialog manager by running through the list of field selections and setting a flag variable for each segment that is in use. I then go through the flags and set any flags that will end up being between any two segments so that the full join requirements can be determined. This is used to produce the file lists and the where statements to complete the join in the sql.

The sql then just does a distinct with all of the fields select and VIOLA!!! - I have my distinct request working for 300+ fields.

Took quite a while to put all of the logic togther though.


Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report 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] Distinct request to cluster MFD

Copyright © 1996-2020 Information Builders