I am getting the following error on my JOIN:
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED

Here is a simplified version of my code:

TABLE FILE V_PROJABC
SUM
AUT_ASST
BY PPG_LE
WHERE ABC_CNTRCT EQ '498707' OR '498695'
ON TABLE HOLD AS HPRJCNT
END

TABLE FILE V_PROJABC
SUM
CNTRCT_COST
BY PPG_LE
WHERE ABC_CNTRCT EQ '498707' OR '498695'
ON TABLE HOLD AS HPRJSUM
END

JOIN PPG_LE IN HPRJCNT TO PPG_LE IN HPRJSUM AS J1;

TABLE FILE HPRJCNT
SUM
AUT_ASST AS 'Auth,Assist'
CNTRCT_COST AS 'Total,Cost'
BY PPG_LE AS 'PPG'
END

-------------------------------------------------
Here is the output from ? HOLD:

DEFINITION OF HOLD FILE: HPRJCNT
FIELDNAME ALIAS FORMAT

PPG_LE E01 A60 MISSING = ON
AUT_ASST E02 P15CB MISSING = ON

0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2

DEFINITION OF HOLD FILE: HPRJSUM
FIELDNAME ALIAS FORMAT

PPG_LE E01 A60 MISSING = ON
CNTRCT_COST E02 P15CB MISSING = ON

DEFINITION OF HOLD FILE: HPRJCNT
FIELDNAME ALIAS FORMAT

PPG_LE E01 A60 MISSING = ON
AUT_ASST E02 P15CB MISSING = ON
PPG_LE E01 A60 MISSING = ON
CNTRCT_COST E02 P15CB MISSING = ON
-------------------------------------------------

My data source is Oracle.
Note that this error only occurs for certain values of ABC_CNTRCT, and not
for other values of ABC_CNTRCT. From various tests, I know the error is
happening because the data for field PPG_LE is similar but different for
each of the 2 values selected for field ABC_CNTRCT, but I don't know why
WebFocus is choking on it.

Here's the Oracle data:
ABC_CNTRCT: 498695
PPG_LE : 6.2c Marketing - no related capital project exists

ABC_CNTRCT: 498707
PPG_LE : 6.2c Marketing - Related Capital Project Exists
-------------------------------------------------

I am using WebFocus 5.2.3 on AIX Unix running Servlet.

Does anyone know why this problem is occurring, and how to get around it?

Thanks for your help!
Sean

This message has been edited. Last edited by: <Kathryn Henning>,

I just got access to our new Test 5.2.8 version, and the problem appears to be fixed.

But if anyone knows of a simple workaround for 5.2.3, that would be appreciated.

Thanks,
Sean

In your second table file that you use in the JOIN TO file

You need to say;

ON TALBE HOLD FORMAT FOCUS INDEX PPG_LE

then do the join and the error message should disappear!

Good Luck!

Thanks Carole, that did work!

However, when I implement it with my larger slightly more complex live version, it fails on field AUT_ASST.

Not sure why yet, but I'll plug away at it.

Thanks,
Sean

OK, I got it.

It was because of some AS names that I was using in the extract for the hold file. When I removed them, it worked as you suggested.

Thanks,
Sean

Hi Sean,

You're welcome! I usually leave the format focus off because it slows down response time. If I get the error you got, I add it to the "TO" file I'm joining to. Even though you know the files are sorted, focus bombs!

Carol

Hi Carol.

I've been reading this thread as I just got the same error message. I'm still fairly new to WF so could you tell me where exactly to put the string?

The FORMAT FOCUS string you mentioned above?

Thanks

Hi Phoenix (Eclipse),
Welcome to the wonderful world of WF!
As for your question, could you post the code that gives you the error?

Here is the part of it where the error returns. It's too massive to post all of it.
-****BEGIN**********************
TABLE FILE NEWFILE2
PRINT
PERSON_UID NOPRINT
ID
NAME
STUDENT_LEVEL
STANDING
HONORS
CGPA
TGPA
COURSE_REFERENCE_NUMBER
COURSE_LEVEL
SUBJECT
COURSE_NUMBER
ACADEMIC_PERIOD
GRADE_CHANGE_DATE
FINAL_GRADE
I?
SEQ
GRADE_CHANGE_REASON_DESC
GRADE_COMMENT
GRADE_COMMENT_DESC
COLLEGE
DEPARTMENT_DESC
INSTRUCTOR
SPRT
APPLIED
BY PERSON_UID
WHERE ID IS-NOT MISSING;
ON TABLE HOLD AS MORE
END
-RUN
SET ALL=PASS
-************PULL APPROVAL AND COMMENTS FROM BANNER TABLES***SYRGRCH DATE IS CONVERTED**********
JOIN CLEAR *
DEFINE FILE SYRGRCH
WEB/A2 = IF SYRGRCH_USER IS 'WWW2_USER' THEN 'Y' ELSE 'N';
END
TABLE FILE SYRGRCH
PRINT
WEB
COMPUTE NEWDATE/YYMD = DATECVT(SYRGRCH.SYRGRCH.SYRGRCH_ACTIVITY_DATE, 'YYMD', 'YYMD');
SYRGRCH_APPR_ONE_PIDM
SYRGRCH_APPR_TWO_PIDM
SYRGRCH_APPR_ONE_DECN
SYRGRCH_APPR_ONE_DECN
SYRGRCH_ADDITIONAL_1_COMMENT
SYRGRCH_ADDITIONAL_2_COMMENT
SYRGRCH_TERM_CODE
SYRGRCH_CRN
BY SYRGRCH_PIDM
ON TABLE HOLD AS DATEADD
-*FORMAT FOCUS INDEX SYRGRCH_PIDM
END
JOIN CLEAR *
JOIN MORE.MORE.PERSON_UID AND MORE.MORE.COURSE_REFERENCE_NUMBER AND MORE.MORE.ACADEMIC_PERIOD AND MORE.MORE.GRADE_CHANGE_DATE IN MORE
TO MULTIPLE DATEADD.DATEADD.SYRGRCH_PIDM AND DATEADD.DATEADD.SYRGRCH_CRN AND DATEADD.DATEADD.SYRGRCH_TERM_CODE AND DATEADD.DATEADD.NEWDATE IN DATEADD AS J4
END
TABLE FILE MORE
PRINT
MORE.MORE.PERSON_UID NOPRINT
MORE.MORE.ID
MORE.MORE.NAME
MORE.MORE.STUDENT_LEVEL
MORE.MORE.STANDING
MORE.MORE.HONORS
MORE.MORE.CGPA
MORE.MORE.TGPA
MORE.MORE.COURSE_REFERENCE_NUMBER
MORE.MORE.COURSE_LEVEL
MORE.MORE.SUBJECT
MORE.MORE.COURSE_NUMBER
MORE.MORE.ACADEMIC_PERIOD
MORE.MORE.GRADE_CHANGE_DATE
MORE.MORE.FINAL_GRADE
MORE.MORE.I?
MORE.MORE.SEQ
MORE.MORE.GRADE_CHANGE_REASON_DESC
MORE.MORE.GRADE_COMMENT
MORE.MORE.GRADE_COMMENT_DESC
MORE.MORE.COLLEGE
MORE.MORE.DEPARTMENT_DESC
MORE.MORE.INSTRUCTOR
MORE.MORE.APPLIED
MORE.MORE.SPRT
DATEADD.WEB
DATEADD.NEWDATE
DATEADD.SYRGRCH_APPR_ONE_PIDM
DATEADD.SYRGRCH_APPR_TWO_PIDM
DATEADD.SYRGRCH_APPR_ONE_DECN
DATEADD.SYRGRCH_APPR_ONE_DECN
DATEADD.SYRGRCH_ADDITIONAL_1_COMMENT
DATEADD.SYRGRCH_ADDITIONAL_2_COMMENT
BY MORE.MORE.PERSON_UID
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
-**END***********************************

JOIN
PERSON_UID AND COURSE_REFERENCE_NUMBER AND ACADEMIC_PERIOD AND GRADE_CHANGE_DATE IN MORE TO MULTIPLE
SYRGRCH_PIDM AND SYRGRCH_CRN AND SYRGRCH_TERM_CODE AND NEWDATE IN DATEADD AS J4
END

You're joining on more than one column, yet you only have one BY statement per TABLE FILE. The data will most likely be out of sequence.

By the way, MORE is a concatenation command in WebFOCUS - I'd stay away from using "MORE" as a HOLD file name.

To add to Francis's comment:

1. If you want to join 2 sequential files (in this case MORE and DATEADD) they must be sorted in the same order. Here you are joing on 4 fields so they must be BY fields in both the HOLD files.
2. In the MORE file you have written PERSON_UID NOPRINT, probably because PERSON_UID is your BY field. Not only is it unnecessary but it also adds a field to the HOLD file.
3. Refrain from using FOCUS names like MORE and DATEADD.
4. Beware when you join sequential files. If a same value appears multiple times in both files you will not get a many-to-many result.

Danny, I missed the DATEADD file name!

Maybe this post is a plant, just to see if we're paying attention.