My data source is sql server database and I have 3 tables that I want to access simultaneously:
Table 1 contains an entity_id that I will use in my where clause. Table 2 links to Table 1 by the field adr_int_id that is common to both tables, and contains the address fields that I need to access. Table 3 is a reference table that links to Table 2 where T2.state_cd = T3.cod_int_id, so that I can display the state description rather than a code value.

The first attempt was to create a masterfile "view" where I linked the 3 tables together using the synonym editor and this worked fine if I did a table file from WebFocus, but failed with a multiple path error FOC03633 when I tried to reference it with a stack in Maintain. The second attempt was to build the view in sql server and create the masterfile that directly referenced this view. Again, it worked fine with a table file from WebFocus but failed with the same error from Maintain.

I'm fairly new to maintain so I'm somewhat at a loss here as to how to fix this. Any suggestions would be much appreciated!
Thanks,
Jen

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

Jen,
I think it should be possible to go either way. It may be that referencing the fields in the maintain is not quite correct, but without the master(s) and code I can't be sure.
Please post your master(s) and code as far as you have it, and then I could try and solve your problem for you.

The time I might get this error is if the WHERE clause when loading the data into the stack is wrong.

However, with Maintain it may be better to populate the stack from a TABLE request using the EXEC syntax.

Then, using that data, update/include/delete/revise the individual tables and use commit and rollback to provide integrity.

I have never tried to MAINTAIN a VIEW or CLUSTER, and would not be inclined to try that.

Hi Jen
The rule with Maintain is that each stack can only contain one logical database path. We cannot easily put data from multiple masters into one stack. There are a couple of ways around this.

When using SQL you can either create a view, or load the stack with Table from a joined struction. Views are nice since you can select all of the fields from any of the Tables. However, if you are going to write back to the database, you have to write back to the original database as the view is for READ only. Your Maintain line would have MAINTAIN FILE one AND two AND... Where one is the view and two, etc... are the actual tables.

Table works well. In your Maintain you would:
-* 1) Build the stack that you want to retrieve:
INFER T2.ADR_IND_ID INTO STK
COMPUTE STK.STATE_NAME/A15; -* as found in T3
-* 2) Perform a Table to load the data
EXEC tab1 INTO STK

Your Table would have:
JOIN fld in T2 to fld in T3

TABLE FILE T2
PRINT T2.fields T3.fields
ON TABLE PCHOLD
END

If you were using FOCUS files, KU segments can be used to load data from multiple files into one stack.

The last way of doing this is just uses Maintain code. In this example, we have loaded up all of the rentals from the VIDEOTRK database into a stack, and then want to compare the MOVIECODE in that file to the TITLE in the MOVIES file.

MAINTAIN FILE VIDEOTRK AND MOVIES
FOR ALL NEXT CUSTID VIDEOTRK.MOVIECODE
INTO MOVSTK
INFER MOVIES.MOVIECODE INTO TSTK
COMPUTE MOVSTK.FILM/A39;
COMPUTE CNT3/I2=1;
REPEAT MOVSTK.FOCCOUNT
REPOSITION MOVIES.MOVIECODE
COMPUTE TSTK.MOVIECODE = MOVSTK(CNT3).MOVIECODE;
MATCH MOVIES.MOVIECODE FROM TSTK INTO TSTK
ON MATCH
COMPUTE MOVSTK(CNT3).FILM = TSTK.TITLE;
ON NOMATCH
COMPUTE MOVSTK(CNT3).FILM = 'FILM NOT FOUND';
TYPE "CODE = < COMPUTE CNT3=CNT3+1;
ENDREPEAT
END

I hope this helps.

Mark

Thanks for the help!

Alan- looks like you were right about the WHERE clause. When I fully qualified the field, the error went away.

This particular data is read only- I'm reading from one data source off multiple tables, allowing the user to edit if needed and then writing to a second datasource/single table a record at a time.

I didn't quite realize I could Exec a Table into a stack... thanks Mark for the code examples!