Hi All,

We upgraded to 7.1.4 and we now have one of our procedures that fails when run as a scheduled job from report caster. If we open the procedure on the server and run it it works just fine. When run as a scheduled job it fails with the following problems:

Executing focexec.

DUPLICATES IN JOIN FROM FIELD : SDCMREPORT/061706674921

DUPLICATES IN JOIN FROM FIELD : SDCMREPORT/061706674921

DUPLICATES IN JOIN FROM FIELD : SDCMREPORT/061706674921

No report to distribute.

It appears our Join will not function when run from the report caster schedule.

Has anyone else experienced this or should I just log it with IBI?

Thanks for any input in advance.

Rayden.

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

To all,

FYI, here is the Symptom/Problem/Solution page for this issue.

FOC1072 error on Caster schedules after upgrading to 714
http://techsupport.informationbuilders.com/sps/22012040.html

Cheers,

Kerry

but Kerry, that solution doesn't make any sense to me.
You've changed a 1-many join to a 1-1 join.
Are you saying that Caster makes all joins 1-many?
ARe you saying that Caster can't handle 1-many joins?

Hi Susannah,

I just spoke with the Technical Support Representative who handled the case regarding the solution. The case has a very similar scenario with Rayden's, and the customer provided the solution that works for her.

If you have questions regarding the solution, please use the Feedback form at the bottom of the Symptom/Problem/Solution page to submit your questions/comments and then we can review the page.

Hope that helps.

Cheers,

Kerry

Will this work on a "LEFT OUTER" join with a "To MULTIPLE"?

ex.:

JOIN
LEFT_OUTER
BASEPOP.EMPLID
IN BASEPOP 
TO MULTIPLE ACCOMP.EMPLID
IN ACCOMP
AS J2
END

If I understand the articl correctly, I need to include a

SET ALL = ON

and change my join to:

JOIN
BASEPOP.EMPLID
IN BASEPOP 
TO ACCOMP.EMPLID
IN ACCOMP
AS J2
END

However when I run my query, for the instances where there were two records on the right side of the left outer join, I am getting a duplicate of the first record from the right side, and don't get the second record from the right side.

Any ideas?

I don't understand why one would need to change code that is correct just to make it run in Report Caster.

When I run the "Left Outer to Multiple" with Report caster, I get the error:

DUPLICATES IN JOIN FROM FIELD

The query runs fine when I run or run deferred, but when I run with report caster, I get that error.

"(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD %1%2%3%4
Duplicates in the FROM field are not allowed
if the JOIN is to a FIX, COM or VSAM/ESDS
file and the 'TO' file has more than one
non-occurs segment."

What kind of files are you joining from and to?

Verify whether the duplication also arises when run on the server (and the difference is in whether foc1072 is issued for the identical data) -- or perhaps the duplication only occurs when your procedure is run in RC (in which case the issue really lies somewhere upstream).

Both files being joined are hold files.

ON TABLE HOLD AS 'BASEPOP'
END

ON TABLE HOLD AS 'ACCOMP'
END

One is a list of employees, so there is only one record per EMPLID field value.

The second is a list of training history, so there could be more than one record per EMPLID field value.

Try

ON TABLE HOLD AS 'BASEPOP' FORMAT FOCUS INDEX BY EMPID

do the same for other hold file. No need to change the JOIN.

Hope this helps,

That gives me the error message:

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: BY

the code was wrong.

It should be

ON TABLE HOLD AS BASEPOP FORMAT FOCUS INDEX EMPID

I suggest you post the code -- at least the table requests that produce the two HOLD files you are joining; the join; and the table request that fails.

This is the code that works in Run/Run Deferred, but fails in Report Caster:

SET ASNAMES = ON
SET ALL = ON

-SET &TODAY = DATECVT(&YYMD, 'I8YYMD', 'YYMD');
-SET &LSTBOM = DATEMOV(DATEADD(&TODAY,'M',-1), 'BOM');
-SET &LSTEOM = DATEMOV(DATEADD(&TODAY,'M',-1), 'EOM');
-SET &LSTXII = &LSTBOM+15;

-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* BASE POPULATION
-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
JOIN CLEAR *

JOIN
JOB_RPT.EMPLID
IN JOB_RPT TO EMPLOYEES_RPT.EMPLID
IN EMPLOYEES_RPT AS J1
END

DEFINE FILE JOB_RPT
SSN4/A4 = SUBSTR ( 20 , JOB_RPT.NATIONAL_ID , 6 , 10 , 4 , SSN4 ) ;
TRMCRITRA/A7 = JOB_RPT.ACTION ||'/'|JOB_RPT.ACTION_REASON;
END

TABLE FILE JOB_RPT
PRINT 
SSN4 AS 'SSN4'
NATIONAL_ID AS 'NATIONAL_ID'

BY EMPLID AS 'EMPLID'

WHERE (JOB_RPT.EFFDT GE &LSTXII ) AND (JOB_RPT.EFFDT LE &LSTEOM )
	AND (JOB_RPT.EFFSEQ_LASTIND_LM EQ 'Y' )
WHERE JOB_RPT.PER_TYPE NE 'G' 

ON TABLE HOLD AS 'BASEPOP'
END

-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* ACCOMPLISHMENT DATA
-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
JOIN CLEAR *

JOIN
ACCOMPLISHMENTS.ACCOMPLISHMENT
IN ACCOMPLISHMENTS TO ACCOMP_TBL.ACCOMPLISHMENT
IN ACCOMP_TBL
AS J1
END

DEFINE FILE ACCOMPLISHMENTS
ISUDT1/I8MDYY = DATECVT ( ACCOMPLISHMENTS.DT_ISSUED , 'MDYY' , 'I8MDYY' ) ;
ISUDT2/A15 = EDIT ( ISUDT1 , '99/99/9999' ) ;
END

TABLE FILE ACCOMPLISHMENTS
PRINT 
ACCOMP_TBL.ACCOMPLISHMENT AS 'CODE'
ACCOMP_TBL.DESCR AS 'DESCR'
ACCOMP_TBL.DESCRSHORT AS 'DESCRSHORT'
ACCOMPLISHMENTS.SCHOOL AS 'SCHOOL'
ISUDT2 AS 'DT_ISSUED'

BY ACCOMPLISHMENTS.EMPLID AS 'EMPLID'

WHERE ACCOMP_TBL.ACCOMP_CATEGORY EQ 'DEG'
WHERE ACCOMP_TBL.DESCR NE ''
WHERE ACCOMPLISHMENTS.GRADUATE_INDICATOR EQ 'Y'

ON TABLE HOLD AS 'ACCOMP'
END


-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* JOIN DATA BACK TOGETHER
-* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
JOIN CLEAR *

JOIN
LEFT_OUTER
BASEPOP.EMPLID
IN BASEPOP 
TO MULTIPLE ACCOMP.EMPLID
IN ACCOMP
AS J2
END

TABLE FILE BASEPOP
PRINT 
BASEPOP.SSN4 AS 'SSN4'
BASEPOP.EMPLID AS 'Empl ID'
BASEPOP.NATIONAL_ID AS 'NID'
ACCOMP.CODE AS 'Code'
ACCOMP.DESCR AS 'Descr'
ACCOMP.DESCRSHORT AS 'Short Desc'
ACCOMP.SCHOOL AS 'School'
ACCOMP.DT_ISSUED AS 'Issue Date'

WHERE ACCOMP.DESCR NE ''

ON TABLE PCHOLD FORMAT TABT
END

For starters, try terminating all of your WHERE statements with a semicolon ( ; ) - this is especially important for compound WHERE's. I know it works sometimes but it goes against documentation. There are a few different issues which causes RC not to work when it works otherwise. For example the use of -EXIT causes RC problems but otherwise works. Some of these issues are documented in Forums posts and I believe there are a few recommendations in documentation as well.

This message has been edited. Last edited by: Darin Lee,

Verify whether or not you actually have duplication in the 'from' hold file, BASEPOP:

JOIN CLEAR *
TABLE FILE BASEPOP
  WRITE CNT.SSN4
    BY EMPLID
  PRINT *
    BY EMPLID
  WHERE TOTAL CNT.SSN4 GT 1 ;
END

Add that after the HOLD AS BASEPOP request, and run both ways (scheduled in RC and directly on server)

- - -

If there is duplication, tighten your selection logic, and/or use SUM FST., to ensure uniqueness -- or else make the second hold FORMAT FOCUS INDEX EMPLID to avoid the limitations inherent in a flat-to-flat join.

That seems to be the first problem!

I have duplicates on the left side of the LEFT OUTER join.

I'll tighten this up and try again.

Thank you.

quote:

TABLE FILE JOB_RPT
PRINT 
SSN4 AS 'SSN4'
NATIONAL_ID AS 'NATIONAL_ID'

BY EMPLID AS 'EMPLID'

WHERE (JOB_RPT.EFFDT GE &LSTXII ) AND (JOB_RPT.EFFDT LE &LSTEOM )
	AND (JOB_RPT.EFFSEQ_LASTIND_LM EQ 'Y' )
WHERE JOB_RPT.PER_TYPE NE 'G' 

ON TABLE HOLD AS 'BASEPOP'
END

If NATIONAL_ID is an attribute of EMPLID, just change PRINT to SUM.

I was getting duplicates in the BASEPOP file because of the date range of the EFFDT criteria.

Once I added

BY HIGHEST 1 JOB_RPT.EFFDT NOPRINT

to the hold file code, that eliminated the duplicates on the left side and Report Caster is letting it run now.

Thank you for the help. The "find duplication" code is going in my notebook of validation code for troubleshooting!

quote:

If there is duplication, tighten your selection logic, and/or use SUM FST., to ensure uniqueness -- or else make the second hold FORMAT FOCUS INDEX EMPLID to avoid the limitations inherent in a flat-to-flat join.

Does the logic has to be 1:1 or 1:n for report caster to work? I have 2 hold files that are n:1 or n:0 that my first file consists of all outstanding invoices(detail) per salesrep/customer, and my 2nd file has the total un-applied payments per customer. A customer has one or more salesreps and may or may not have payments during a report period.
So, I don't have the ideal situation for report caster. This is big supprise to me that the users calling directly from the server have no complains and field salespeople can not get their weekly email reports from the same fex.

Please help.

Hua

My problem is resolved in either of the following ways:
1) I removed "MULTIPLE" in JOIN-to file as sugguested by the Techsupport link above.

2) I used DB_LOOKUP into my 2nd hold file instead of JOIN.

It seemed to me that either the report server is too ignorant to the join errors or the report caster is too sensitive. I just remembered I had a similar incident on this error:

quote:

FOC32456) ANSI-COMPLIANCE ENFORCED BY CHANGING OUTER JOIN TO INNER
A left outer join in the root-path of an inner join has been changed into an inner join.

Thanks.


Hua