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,

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.

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.