Data is stored in Oracle 10g. We have a denormalized view of data with lots of columns. We'd like to group related data using names like Job, Department, Location, etc..., so that users developing reports can easily find what they are looking for rather than searching through a long alphabetical list.

We cannot change the column names to add a prefix like JOB_JOBCODE instaed of just JOBCODE.

My first thought was to modify the Master to have unique child segments defined for each group even though the data all comes from the same view. For example:

FILENAME=Movement_History, SUFFIX=SQLORA , $
SEGMENT=MOVEMENT, SEGTYPE=S0, $
FIELDNAME=EMPLID, ALIAS=EMPLID, USAGE=A11V, ACTUAL=A11V, $

SEGMENT=LOCATION, SEGTYPE=S0, PARENT=MOVEMENT, $
FIELDNAME=WORK_LOCATION, ALIAS=WORK_LOCATION, USAGE=A5V, ACTUAL=A5V, $

This approach works fine from a WF user perspective; the groups appear in the Object Explorer and can be expanded to show the columns associated with that group.

I changed the access file to include the Access File definition to include the new SEGMENT:

SEGNAME=MOVEMENT, TABLENAME=STEVE_GEN.MOVEMENT_GEN_HIST_VW,
CONNECTION=uredev, KEYS=0, $
SEGNAME=LOCATION, TABLENAME=STEVE_GEN.MOVEMENT_GEN_HIST_VW,
CONNECTION=uredev, KEYS=0, $

However, whenever I try to access the data I get the following message:

(FOC1402) KEYFLD AND IXFLD KEYWORDS MISSING FOR SEGMENT : LOCATION

Is anyone else doing something like this?
Is there an alternative method for grouping columns?

Alan, I'm not sure if creating multiple segments in the Master and then modifying the Access file will work.

Here are two other suggestions:

1) Change the Master to use names with the prefixes you mentioned. The Master has the WebFOCUS column name (FIELD) and the database table column name (ALIAS, usually right after the comma and not tagged with ALIAS). You can change the FIELD to anything you want.

2) Create an Oracle view with renamed columns. The advantage of this is you don't have to change a Master. This is my preferred method, since you're not fooling around with the generated Master.

Francis,
Unfortunately we have a requirement to use the same column names as the source system; this is so the user can relate data in the reports directly to the data in the source system without having to "translate" the names. Both of the changes you suggested require changing the column names.

I'm assuming this is a common problem, and I'm hoping there is another solution.

Thanks,
Alan

OK, how about adding the TITLE attribute to each column in the Master?

The syntax is something like this:

FIELD=MARITAL_STATUS_ID , MARITAL_STATUS_ID,
I11 , I4          , MISSING=ON
,TITLE='Marital Status'
,DESCRIPTION='Users marital status. Valid values configurable.'
,$

You can also have a DESCRIPTION attribute.

I think these two attributes will be displayed in the Object Explorer, though, perhaps, not by default.

Remember that if the Master is regenerated via the WebFOCUS Server Console Synonym generator, it will wipe out the existing Master.

Francis,
We plan to generate the synonym once, then it wil be heavily modifed and maintained like any other component. No regens allowed.

Both Title and Description are good ways to communicate information, but the goal is to group related columns and I don't think either will do that.

Thanks,
Alan

Alan, if you prefix the Title or Description with what you want to group the fields with, then it would group related columns.

e.g.
Address: Line 1
Address: Line 2
Name: First Name
Name: Last Name

etc.

These Titles or descriptions can be sorted and then the columns will be grouped.

Can you conceive of any other way of doing this?