I've recreated our issue using the training database. We need to display a field that has the same name from 2 different tables (primary and the joined to table). We are also using the MORE command. We get the error:
(FOC955) THE FIELD DOESN'T HAVE A UNIQUE FLDNAM, OR ALIAS: PIN

I'm qualifying the fieldname, so I'm not sure how to make it more unique. If we don't have the more command, we do not get the above error. Any suggestions on how to get past this error? When we look at the sql output from the run, it appears that the join is not in the 2nd sql. I'm trying to get the sql to display using developer studio (to put out here on the focal point) but it's not working. I'm using the following commands ... let me know what I'm doing wrong. Is it because I'm using a focus database?
SET TRACEOFF=ALL
-******* Turn on SQL Trace and have it sent back to the browser
SET TRACEON=STMTRACE//CLIENT
-******* Trun on SQL Aggration Trace and have it sent back to the browser
SET TRACEON=SQLAGGR//CLIENT
-******* Turn on User Tracing
SET TRACEUSER=ON
-******* Do Not Send SQL to DB2
SET XRETRIEVAL = OFF
-SET ECHO=ON

Here is the code using the training database.
SET ASNAMES=ON
SQL DB2 SET SQLJOIN OUTER ON

JOIN
TRAINING.TRAINING.PIN IN training TO ALL EMPDATA.EMPDATA.PIN IN empdata AS J0
END
TABLE FILE TRAINING
PRINT
TRAINING.PIN
COURSESTART
COURSECODE
LASTNAME
FIRSTNAME
EMPDATA.PIN
WHERE COURSECODE EQ 'EDP690' OR COURSECODE EQ 'EDP130';
ON TABLE HOLD AS HOLD100
MORE
FILE TRAINING
WHERE COURSECODE EQ 'PDR740' OR COURSECODE EQ 'PDR130';
END

Thanks.

EMPDATA.PIN
its this second PIN that you're pulling in, Pam.
Your extract how has 2 fields with the same name, PIN.
so, if you comment out that -*EMPDATA.PIN
it works fine.
so you have to figure out some way to get your second PIN in (if you really need it)
You can do it alot of ways, a DEFINE comes to mind, .

TRAINING and EMPDATA are the IBI supplied FOCUS databases? If so, then SQL will not be generated to access these files, SQL is generated only for DBMS databases. As well, SQL DB2 SET SQLJOIN OUTER ON will obviously not work since the files are not DB2 tables.

I can't explain why you're getting the FOC955 error, but here's a workaround, using DEFINE:

SET ASNAMES=ON
JOIN
TRAINING.TRAINING.PIN IN TRAINING TO ALL EMPDATA.EMPDATA.PIN IN EMPDATA TAG J0 AS J0
END

DEFINE FILE TRAINING
PIN2/A9 = EMPDATA.PIN;
END

TABLE FILE TRAINING
PRINT
TRAINING.PIN
COURSESTART
COURSECODE
LASTNAME 
FIRSTNAME 
PIN2 
WHERE COURSECODE EQ 'EDP690' OR COURSECODE EQ 'EDP130';
ON TABLE HOLD AS HOLD100
MORE
FILE TRAINING
WHERE COURSECODE EQ 'PDR740' OR COURSECODE EQ 'PDR130';
END

You do realize that the PINs will always be the same, since they're the JOIN field?

Pam, Francis and I are on the same track, It seems that the MORE command doesn't use the qualifiers, but only uses the resultant fieldnames generated by the hold file master... hence two identical fields named PIN. I don't think this is a full-fledged bug, but i certainly do think it is an 'icky'...and you might want to open a case and ask css to look in to improving the MORE command... maybe there's a good reason it works this way, and maybe not.

thanks for the responses. I realize the pin field will be the same but it was the only field that was the same on both databases that I could use for an example. In our case on our databases, we're using a field called address1 but it's completely different info in each of the fields.

I will try the define and I will open a case with IBI.

Francis: Our databases are SQL, so the sql code in there is just code I forgot to delete when I created the code using IBI tables. Sorry about that.

Thanks again!

Pam,

As stated by everyone else, using the DEFINE works:

JOIN
TRAINING.TRAINING.PIN IN training TO ALL EMPDATA.EMPDATA.PIN IN empdata AS J0
END
DEFINE FILE TRAINING
EMPPIN/A9=EMPDATA.PIN;
END
TABLE FILE TRAINING
PRINT
TRAINING.PIN
COURSESTART
COURSECODE
LASTNAME
FIRSTNAME
EMPPIN
WHERE COURSECODE EQ 'EDP690' OR COURSECODE EQ 'EDP130';
ON TABLE HOLD AS HOLD100
MORE
FILE TRAINING
WHERE COURSECODE EQ 'PDR740' OR COURSECODE EQ 'PDR130';
END

I haven't tried the DEFINE method!

We've been creating the join on the DB2 database itself for issues where the fieldnames are common between two tables and aliasing the fieldnames in a view on the system. The works ok and allows us to optimize the join with appropriate indexing.

using SQL
create view schema.viewname as (select a.Field1 as "Field1A", b.Field1 as "Field1B" from schema.a, schema.b where a.field = b.field);

your define method seems to be a faster way!

the define does work great.

Just an FYI. I did report this to IBI and they were able to recreate the issue and are working on fixing this. I don't expect a fix anytime soon, but it is documented as something that needs to be fixed.