I'm still new at WebFOCUS and am in the testing phase. I'm trying to join together 4 tables and 2 of them have defines to link format P9 to D20.2. USF_AA_ADDRESS.PIDM and USF_AA_DEGREE.PERSON_UID have a format of P9 and USF_AA_DN_DT.PIDM and USF_AA_GIFT_SUMS.PIDM have a format of D20.2. I feel like I should be using WITH instead of IN for the defined fields based on search results, however, it wasn't working for me.

----

Here is the error I'm getting:
Error Parsing MASTER File USF_AA_ADDRESS ( (FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(370)).

----

Here is my code:

DEFINE FILE USF_AA_ADDRESS
PERSON_UID/D20.2=USF_AA_ADDRESS.USF_AA_ADDRESS.PIDM;
END
DEFINE FILE USF_AA_DEGREE_USF
PIDM/D20.2=USF_AA_DEGREE_USF.USF_AA_DEGREE_USF.PERSON_UID;
END
JOIN
LEFT_OUTER USF_AA_ADDRESS.USF_AA_ADDRESS.PERSON_UID IN USF_AA_ADDRESS TO UNIQUE
USF_AA_DN_DT.USF_AA_DN_DT.PIDM IN USF_AA_DN_DT TAG J0 AS J0
END
JOIN
LEFT_OUTER J0.USF_AA_DN_DT.PIDM IN USF_AA_ADDRESS TO UNIQUE
USF_AA_GIFT_SUMS.USF_AA_GIFT_SUMS.PIDM IN USF_AA_GIFT_SUMS TAG J1 AS J1
END
JOIN
LEFT_OUTER J1.USF_AA_GIFT_SUMS.PIDM IN USF_AA_ADDRESS TO UNIQUE
USF_AA_DEGREE_USF.USF_AA_DEGREE_USF.PIDM IN USF_AA_DEGREE_USF TAG J2 AS J2
END

----

I tried a variety of different changes of IN's to WITH's, but am getting the FOC370 error. Any ideas on how to fix this?

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

You should have your JOIN command before the DEFINEs.

Also, if the fields with D20.2 only have integers, can you get the MASTER files fields USAGE to be changed to P9

Thanks I will move the JOINs.

I also contacted a DBA to see if she can change the tables with D20.2 to P9. Oddly enough when I check the MASTER files, they all say USAGE = P9 ACTUAL = ... so not sure why they're coming up as D20.2. Hopefully she'll get back to me soon!

Thanks Waz!

I moved the JOINs before the DEFINEs as follows:

JOIN
LEFT_OUTER USF_AA_ADDRESS.USF_AA_ADDRESS.PERSON_UID IN USF_AA_ADDRESS TO UNIQUE
USF_AA_DN_DT.USF_AA_DN_DT.PIDM IN USF_AA_DN_DT TAG J0 AS J0
END
JOIN
LEFT_OUTER J0.USF_AA_DN_DT.PIDM IN USF_AA_ADDRESS TO UNIQUE
USF_AA_GIFT_SUMS.USF_AA_GIFT_SUMS.PIDM IN USF_AA_GIFT_SUMS TAG J1 AS J1
END
JOIN
LEFT_OUTER J1.USF_AA_GIFT_SUMS.PIDM IN USF_AA_ADDRESS TO UNIQUE
USF_AA_DEGREE_USF.USF_AA_DEGREE_USF.PIDM IN USF_AA_DEGREE_USF TAG J2 AS J2
END

DEFINE FILE USF_AA_ADDRESS
PERSON_UID/D20.2=USF_AA_ADDRESS.USF_AA_ADDRESS.PIDM;
END
DEFINE FILE USF_AA_DEGREE_USF
PIDM/D20.2=USF_AA_DEGREE_USF.USF_AA_DEGREE_USF.PERSON_UID;
END


However, I'm getting this error when attempting to create a new procedure from table USF_AA_ADDRESS:

Error Parsing MASTER File USF_AA_ADDRESS ( (FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
(370)).

I'm still waiting on the word from our DBA about why the two are showing up as D20.2 when the Master files shows usage of P9.

Can you post the master files ?

Unfortunately, I don't think we can post that out because of our security policy.

I will try to do a work-around and will create a new post when I run into a snag during my testing

Editing to close.

Thanks again Waz. I definitely appreciate all your help today

Glad to be of service.

Another option is to repro with the sample tables, or create some data your self, there are examples in the forum.