Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Distinct request to cluster MFD
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Distinct request to cluster MFD
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Distinct request to cluster MFD

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.