Here is the problem. I have two Oracle tables. One has various fields that contain code values. The other table is a Code Table Entry table, so it contains the code table id, code table value and code table description for all fields that are a code type of field. I want my MRE users to be able to drop and drag either the code or the description to their report so I want something like a code lookup in a DEFINE field in the master.

Problem is that the code table id is not available in a "real" field to use in a JOIN or Cross Ref in the master. For example, the field in the master for table 1 called CHANGE_ORDER_T is supported by entries in the Code Table Entry table where the TBL_ID = "MODTYPE".

In SQL I would be doing something like:

select CONT_ID, C_O_NBR, C_O_T, CD_DESC from dssdba.t_cont_co a, dssdba.t_cd_tbl_dtl b
where (a.cont_id = 'T2027') and (b.TBL_ID = 'MODTYPE') and (a.c_o_t = b.CD_ID);


CONT_ID C_O C_O_ CD_DESC
--------------- --- ---- ----------------------------------------
T2027 001 SA Supplemental Aggreement
T2027 002 SA Supplemental Aggreement
T2027 003 WE Weather Days Time Granted

TABLE 1

FILE=TABLE1, SUFFIX=SQLORA
SEGNAME=OT004, SEGTYPE=S0, $
FIELD=CONTRACT_ID, ALIAS=CONT_ID, USAGE=A15, ACTUAL=A15, $
FIELD=CHANGE_ORDER_ID, ALIAS=C_O_NBR, USAGE=A3, ACTUAL=A3, $
FIELD=CHANGE_ORDER_T, ALIAS=C_O_T, USAGE=A4, ACTUAL=A4, TITLE='Change,Order,Type', DESCRIPTION='Type of Change Order', $
FIELD=REASON_T, ALIAS=REAS_T, USAGE=A4, ACTUAL=A4, $
FIELD=CHANGE_ORDER_DESC, ALIAS=CHNG_DESC, USAGE=A60, ACTUAL=A60, $

TABLE 2

FILE=cd_tbl_dtl, SUFFIX=SQLORA,
SEGNAME=OT038, SEGTYPE=S0, $
FIELD=TBL_ID, ALIAS=TBL_ID, USAGE=A8, ACTUAL=A8, $
FIELD=CD_ID, ALIAS=CD_ID, USAGE=A4, ACTUAL=A4, $
FIELD=CD_DESC, ALIAS=CD_DESC, USAGE=A40, ACTUAL=A40, $

We are in a Windows environment (WF5.2.7) and I wanted to know if anyone has developed a User Written Sub-routine in say VB that has done anything like this? Or, if anyone has another technique.

We could do it with an Oracle view but that is not an option at this time.

Can you use views?

At this time, it would take longer to go thru all of the red-tape to get an Oracle view created. Here you have to go thru DA to document the view and then get DBA Tech guys to create it, etc. Then you have to do the change to the master. There are about 100 tables to do this for. In the long term we are going to be putting this information in a data warehouse structure where we will store the descriptions, de-normalize, summarize, etc. But that will take a while. In the short term, since I can update the masters with no red-tape..... I was looking for a quick fix.

karenv,

I think you might be able to do this with a combination of a WebFOCUS cluster Join, and WebFOCUS DBA Level Security. You could join the tables together and then add DBA level security to automaictly (under the covers) restrict the value of TBL_ID to MODTYPE without the users knowing.

I tried the Cluster Join and it appears that the problem is that the Join doesn't like the DEFINE for the TBL_ID.