My goal is to have a master file on two tables (Test_Job and Test_Job_Cat) and then have a third master that joins them through a remote or CRFILE access. What I don't want is a copy of the Test_Job_Cat master pasted on the bottom of the Test_Job master. My goal is to have one master that is created and maintained with others accessing that without needed to be maintained.

Here are the two master/access files by themselves:
Job (Access):
SEGNAME=test_job,
TABLENAME=TSPMADMIN.USPD_JOBS,
KEYS=1,
CONNECTION=TSPMP,$

Job (Master):
FILE=test_job, SUFFIX=SQLORA
SEGNAME=test_job, SEGTYPE=S0, $
FIELD=JOB_ID, ALIAS=JOB_ID, USAGE=P11, ACTUAL=P6, $
FIELD=JOB_CODE, ALIAS=JOB_CODE, USAGE=A7V, ACTUAL=A7V, MISSING=ON, $
FIELD=JOB_TITLE, ALIAS=JOB_TITLE, USAGE=A25V, ACTUAL=A25V, MISSING=ON, $
FIELD=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, $
FIELD=SVC_SECURITY_USER_LEVEL, ALIAS=SVC_SECURITY_USER_LEVEL, USAGE=A4V, ACTUAL=A4V, MISSING=ON, $

Job Category (Access):
SEGNAME=TEST_JOB_CAT, TABLENAME=TSPMADMIN.USPD_JOB_CATEGORY,
CONNECTION=TSPMP, KEYS=1, $

Job Category (Master):
FILENAME=TEST_JOB_CAT, SUFFIX=SQLORA , $
SEGMENT=TEST_JOB_CAT, SEGTYPE=S0, $
FIELDNAME=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=JOB_CATEGORY, ALIAS=JOB_CATEGORY, USAGE=A15V, ACTUAL=A15V, $
FIELDNAME=MANPOWER_IND, ALIAS=MANPOWER_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON, $
FIELDNAME=REPORT_CATEGORY, ALIAS=REPORT_CATEGORY, USAGE=A15V, ACTUAL=A15V,
MISSING=ON, $

Both tables are from the same Oracle source. Both tables join on job_category_id where Job to Job_Category is a oneto one relationship (each job will have ONE category)

This is what I'm trying to figure out and my attempt:
FILE=test_job, SUFFIX=SQLORA
SEGNAME=test_job, SEGTYPE=S0, $
FIELD=JOB_ID, ALIAS=JOB_ID, USAGE=P11, ACTUAL=P6, $
FIELD=JOB_CODE, ALIAS=JOB_CODE, USAGE=A7V, ACTUAL=A7V, MISSING=ON, $
FIELD=JOB_TITLE, ALIAS=JOB_TITLE, USAGE=A25V, ACTUAL=A25V, MISSING=ON, $
FIELD=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, $
FIELD=SVC_SECURITY_USER_LEVEL, ALIAS=SVC_SECURITY_USER_LEVEL, USAGE=A4V, ACTUAL=A4V, MISSING=ON, $
SEGNAME=TEST_JOB_CAT ,SEGTYPE=KLU ,PARENT=test_job, CRFILE=TEST_JOB_CAT,$

Here's what I wanted for the .acx file (all of this is done under the test_job.mas and test_job.acx)
SEGNAME=test_job, TABLENAME=TSPMADMIN.USPD_JOBS, KEYS=1, CONNECTION=TSPMP,$
SEGNAME = TEST_JOB_CAT, TABLENAME = TSPMADMIN.USPD_JOB_CATEGORY, KEYS=1, KEYFLD = JOB_CATEGORY_ID, IXFLD = JOB_CATEGORY_ID,$

When I run the check command I get this:
(FOC211) THE PARENT SEGMENT NAME IS NOT FOUND IN THE MASTER: test_job

When I open the master in the report painter I see the new segment for test_job_category but it is grayed out with no fields listed. It is like it can't read the remote master using CRFILE.

Can anyone help me out on this one? I would greatly appreciate it.

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

quote:

SEGNAME=TEST_JOB_CAT ,SEGTYPE=KLU ,PARENT=test_job, CRFILE=TEST_JOB_CAT,$

Here's what I wanted for the .acx file (all of this is done under the test_job.mas and test_job.acx)
SEGNAME=test_job, TABLENAME=TSPMADMIN.USPD_JOBS, KEYS=1, CONNECTION=TSPMP,$
SEGNAME = COURSE, TABLENAME = TSPMADMIN.USPD_JOB_CATEGORY, KEYS=1, KEYFLD = JOB_CATEGORY_ID, IXFLD = JOB_CATEGORY_ID,$

Try a SEGTYPE of DKU in the cross reference segment (that's a dynamic join).
The SEGNAME in the acx file has to match the segname in the master file. Where did COURSE come from?


Is this WebFOCUS, or FOCUS? What Release?

Good catch. I wat trying to cut and pasted from the help files and missed the segment.

I am on WebFocus 5.3.2 & 7.1.

The segtype should then be either KU or KM right?

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

When I type these commands into the text tool in report painter's text editor CRFILE does NOT show up as RED like all the other terms (SEGNAME, CRKEY, CRSEGNAME, etc.)

Is this feature not available for relational (Oracle) databases?

Here's what you need for the static Unique Join. And don't worry about the key words not coming up in red... they probably just aren't in the list. It doesn't mean you can't use them with ORACLE.

Syntax: How to Specify a Static Unique Join
SEGNAME = segname, SEGTYPE = KU, PARENT = parent,
CRFILE = db_name,
CRKEY = field, [CRSEGNAME = crsegname,] $
where:

segname
Is the name by which the cross-referenced segment
will be known in the host data source. You can
assign any valid segment name, including the segment's
original name in the cross-referenced data source.

parent
Is the name of the host segment.

db_name
Is the name of the cross-referenced data source. You can
change the name without rebuilding the data source.

field
Is the common name (field name and/or alias) of the host
field and the cross-referenced field. The field name or
alias of the host field must be identical to the field
name of the cross-referenced field. You can change the field
name without rebuilding the data source as long as the
SEGTYPE remains the same.
Both fields must have the same format type and length.
The cross-referenced field must be indexed
(FIELDTYPE=I or INDEX=I).

crsegname
Is the name of the cross-referenced segment. If you do not
specify this it defaults to the value assigned to SEGNAME.
After data has been entered into the cross-referenced data
source, you cannot change the crsegname without rebuilding
the data source.
The SEGTYPE value KU stands for keyed unique.
Example: Creating a Static Unique Join
SEGNAME = JOBSEG, SEGTYPE = KU, PARENT = PAYINFO,
CRFILE = JOBFILE, CRKEY = JOBCODE, $
The relevant sections of the EMPLOYEE Master File follow
(for simplicity, fields and segments not essential to the
example are not shown):

FILENAME = EMPLOYEE, SUFFIX = FOC, $
SEGNAME = EMPINFO, SEGTYPE = S1, $ . . .
SEGNAME = PAYINFO, SEGTYPE = SH1, PARENT = EMPINFO, $
FIELDNAME = JOBCODE, ALIAS = JBC, FORMAT = A3, $ . . .
SEGNAME = JOBSEG, SEGTYPE = KU, PARENT = PAYINFO,
CRFILE = JOBFILE, CRKEY = JOBCODE, $
Note that you only have to give the name of the cross-referenced
segment; the fields in that segment are already known from
the cross-referenced data source's Master File
(JOBFILE in this example). Note that the CRSEGNAME attribute is
omitted, since in this example it is identical to the name
assigned to the SEGNAME attribute.

Piipster... Thanks for helping out.

I started over and made a few tweaks. It seems to have needed both the common fields indexed (INDEX=I) and then when I checked it it recognized the new virtual segment and fields. Then when I opened it in painter it showed me the fields in the master file as well.

HOWEVER, when I wrote a report off of the new fields I got this:
(FOC1354) ACCESS FILE RECORD ABSENT, WRONG OR INCOMPLETE FOR SEGMENT :

I didn't put anything in the access file so I don't know if that is what this is telling me. SO CLOSE..

Here are my new mas/acx files for reference:
FILE=test_job, SUFFIX=SQLORA
SEGNAME=TEST_JOB, SEGTYPE=S0, $
FIELD=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, FIELDTYPE=I, $
FIELD=JOB_ID, ALIAS=JOB_ID, USAGE=P11, ACTUAL=P6, $
FIELD=JOB_CODE, ALIAS=JOB_CODE, USAGE=A7V, ACTUAL=A7V, MISSING=ON, $
FIELD=JOB_TITLE, ALIAS=JOB_TITLE, USAGE=A25V, ACTUAL=A25V, MISSING=ON, $
FIELD=SVC_SECURITY_USER_LEVEL, ALIAS=SVC_SECURITY_USER_LEVEL, USAGE=A4V, ACTUAL=A4V, MISSING=ON, $
SEGNAME=test_job_cat, SEGTYPE=KU, PARENT=TEST_JOB, CRKEY=JOB_CATEGORY_ID, CRFILE=TEST_JOB_CAT, $

Access File:
SEGNAME=TEST_JOB,
TABLENAME=TSPMADMIN.USPD_JOBS,
KEYS=1,
CONNECTION=TSPMD,$

Remote Maste File:
FILENAME=test_job_cat, SUFFIX=SQLORA , $
SEGMENT=test_job_cat, SEGTYPE=S1, $
FIELDNAME=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, INDEX=I, $
FIELDNAME=JOB_CATEGORY, ALIAS=JOB_CATEGORY, USAGE=A15V, ACTUAL=A15V, $
FIELDNAME=MANPOWER_IND, ALIAS=MANPOWER_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON, $
FIELDNAME=REPORT_CATEGORY, ALIAS=REPORT_CATEGORY, USAGE=A15V, ACTUAL=A15V,
MISSING=ON, $

Remote Access File:

SEGNAME=test_job_cat,
TABLENAME=TSPMADMIN.USPD_JOB_CATEGORY,
CONNECTION=TSPMD,
KEYS=1, $

Hi John,

Has this issue been resolved on your side?

I ran a quick search on Information Builders' online documents and came across the following topics which may be of interest:

FOC1351 & FOC1354 returned from ORACLE TABLE request
http://techsupport.informationbuilders.com/sps/62711015.html

FOC1354 Access File Not Found after conversion to 711
http://techsupport.informationbuilders.com/sps/21522051.html

Hope this helps.

Cheers,

Kerry

Kerry,

My issue is resolved. With the assistance of Pipster in this forum and a ticket I opened up with IBI a complete solution was generated. My problem was that the documenation mentioned what to add to the master file but was done with an adaptor that did not require an access file. When I went back to try this with Oracle I just made the leap that because the cluster-joined object would read the remote segment from the remote master that it would also use its access file. That turned out to be an incorrect assumption. The connection data also had to be added into the access file (which is basically duplicated from the root master).

I am going to post up my final solution in an attempt to have it here in case someone else has the same question in the future.

The second link is very intersting to be aware of. Thanks.

- john

Here is the final coded solution.

Root Master + Remote Segment Master File
FILE=TEST_JOB, SUFFIX=SQLORA
SEGNAME=TEST_JOB, SEGTYPE=S0, $
FIELD=JOB_CATEGORY_ID, ALIAS=JOB_CATEGORY_ID, USAGE=P11, ACTUAL=P6, FIELDTYPE=I, $
FIELD=JOB_ID, ALIAS=JOB_ID, USAGE=P11, ACTUAL=P6, $
FIELD=JOB_CODE, ALIAS=JOB_CODE, USAGE=A7V, ACTUAL=A7V, MISSING=ON, $
FIELD=JOB_TITLE, ALIAS=JOB_TITLE, USAGE=A25V, ACTUAL=A25V, MISSING=ON, $
FIELD=SVC_SECURITY_USER_LEVEL, ALIAS=SVC_SECURITY_USER_LEVEL, USAGE=A4V, ACTUAL=A4V, MISSING=ON, $
SEGNAME=test_job_cat, SEGTYPE=KU, PARENT=TEST_JOB, CRKEY=JOB_CATEGORY_ID, CRFILE=TEST_JOB_CAT, $

Corresponding Access File (Oracle)
SEGNAME=TEST_JOB,
TABLENAME=TSPMADMIN.USPD_JOBS,
KEYS=1,
CONNECTION=TSPMD,$
SEGNAME=test_job_cat,
TABLENAME=TSPMADMIN.USPD_JOB_CATEGORY,
KEYS=1,
CONNECTION=TSPMD,$

I know that the documentation says to use various segment types for remote use (when using CRFILE) but the only two that worked for me were KU -> Keyed Unique & KM -> Keyed Multiple