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,
Web Focus version 7.1.4 Server Windows Server 2003
July 26, 2006, 04:43 PM
Kerry
To all,
FYI, here is the Symptom/Problem/Solution page for this issue.
Kerry Zhan Focal Point Moderator Information Builders, Inc.
July 26, 2006, 05:02 PM
susannah
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?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
July 27, 2006, 02:05 PM
Kerry
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
Kerry Zhan Focal Point Moderator Information Builders, Inc.
September 15, 2008, 03:49 PM
ColdWhiteMilk
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?
Production - 7.6.4 Sandbox - 7.6.4
September 15, 2008, 04:08 PM
Francis Mariani
I don't understand why one would need to change code that is correct just to make it run in Report Caster.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 15, 2008, 04:16 PM
ColdWhiteMilk
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.
Production - 7.6.4 Sandbox - 7.6.4
September 15, 2008, 07:14 PM
j.gross
"(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).
- Jack Gross WF through 8.1.05
September 16, 2008, 09:13 AM
ColdWhiteMilk
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.
Production - 7.6.4 Sandbox - 7.6.4
September 16, 2008, 09:35 AM
Kamesh
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,
WFConsultant
WF 8105M on Win7/Tomcat
September 16, 2008, 10:10 AM
ColdWhiteMilk
That gives me the error message:
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: BY
Production - 7.6.4 Sandbox - 7.6.4
September 16, 2008, 10:22 AM
Kamesh
the code was wrong.
It should be
ON TABLE HOLD AS BASEPOP FORMAT FOCUS INDEX EMPID
WFConsultant
WF 8105M on Win7/Tomcat
September 16, 2008, 10:29 AM
j.gross
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.
- Jack Gross WF through 8.1.05
September 16, 2008, 11:14 AM
ColdWhiteMilk
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
Production - 7.6.4 Sandbox - 7.6.4
September 16, 2008, 11:33 AM
Darin Lee
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,
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 16, 2008, 11:48 AM
j.gross
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.
- Jack Gross WF through 8.1.05
September 16, 2008, 01:17 PM
ColdWhiteMilk
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.
Production - 7.6.4 Sandbox - 7.6.4
September 16, 2008, 02:58 PM
j.gross
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.
- Jack Gross WF through 8.1.05
September 16, 2008, 03:20 PM
ColdWhiteMilk
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!
Production - 7.6.4 Sandbox - 7.6.4
January 25, 2010, 03:51 PM
Hua
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
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
January 25, 2010, 05:27 PM
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.