Can someone please help me figure out this error message I getting on my report. The report runs fine with the error message when you run the report On-Demand. But since I am running this report through Report Caster I need to fix this error since Report Caster does not run reports with errors. Any Suggestions?

Below is the code for my report and I also included where the error is occuring at.


-DEFAULT &OUTPUT = 'EXL2K';
SET ASNAMES=ON

-*INCLUDE SQLTRACEXOFF

-SET &HOUR = EDIT(&TOD,'99');
-SET &M = IF &TOD GT '12.00.00' THEN 'pm' ELSE 'am';
-SET &FLAG = IF &TOD GT '13.00.00' THEN 1 ELSE 0 ;
-SET &HOUR = IF &FLAG IS 1 THEN &HOUR - 12 ELSE &HOUR ;
-SET &MINUTES = EDIT(&TOD,'$$$99');
-SET &SECONDS = EDIT(&TOD,'$$$$$$99');
-SET &TIME = &HOUR | ':' | &MINUTES | &M;


JOIN CLEAR *
JOIN F4102.IBITM IN F4102 TO F4101.IMITM IN F4101 TAG A1 AS J0
JOIN F4101.IMITM IN F4102 TO F41021.LIITM IN F41021 TAG A2 AS J1
JOIN F4101.IMITM IN F4102 TO F4105.COITM IN F4105 TAG A3 AS J2


TABLE FILE F4102
PRINT
IMLITM
IBMCU
IMDSC1
COLEDG_CODE
COUNCS
COMCU AS 'COMCU_1'
LIPQOH
LILOCN
BY IMITM
WHERE IBMCU EQ COMCU
WHERE IBMCU EQ LIMCU
WHERE IMSTKT_CODE EQ 'P'
WHERE IMGLPT_CODE IN ( 'ILBL', 'ICLO', 'IFIB', 'IM09', 'IPKW', 'IM01',
'IM02', 'IM03', 'IM04', 'IM05', 'IM06', 'IM07',
'IM08', 'IM10', 'IM11', 'IM12', 'IM13', 'IM14',
'IM23', 'IM24', 'IM40', 'IM41', 'IM42', 'IM43',
'IM44', 'IM45', 'IM46', 'IM47', 'IM48', 'IM49' )
WHERE COLEDG_CODE EQ '07'
WHERE COUNCS GT '.0000'
WHERE NOT IBMCU IN ( ' MID' )
ON TABLE HOLD AS DAILYBP FORMAT FOCUS INDEX IMITM
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 9404 LINES= 9404

TABLE FILE F4102
PRINT
IBMCU AS 'IBMCU_1'
BY IBITM
WHERE F4102.IBMCU IN ( ' BDY', ' INT', ' CA3', ' FLD', ' IL3', ' PA1',
' LMW', ' MOD', ' BRW', ' SNW', ' GA1', ' KS1',
' NJ1', ' TX1', ' FL1', ' LPK', ' B10', ' WHE' )
ON TABLE HOLD AS DAILYBP1 FORMAT FOCUS INDEX IBITM
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 211726 LINES= 211726

JOIN DAILYBP.IMITM IN DAILYBP TO ALL DAILYBP1.IBITM IN DAILYBP1 TAG A4 AS J3

TABLE FILE DAILYBP
SUM
IMLITM
IBMCU
IMDSC1
COLEDG_CODE
COUNCS
COMCU_1
LIPQOH
LILOCN
IBMCU_1
BY IMITM
BY IBMCU_1
WHERE IBMCU NE IBMCU_1
ON TABLE HOLD AS DAILYBP2
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 26580 LINES= 6561

MATCH FILE DAILYBP2
SUM
IMLITM
IBMCU
IMDSC1
COLEDG_CODE
COUNCS
COMCU_1
LIPQOH
LILOCN
BY IMITM
BY IBMCU_1
RUN

FILE F4105
SUM
COUNCS AS 'COUNCS_1'
COMCU AS 'COMCU_2'
COLEDG_CODE AS 'COLEDG_1'
BY COITM AS 'IMITM'
BY COMCU AS 'IBMCU_1'
WHERE COUNCS NE 0
WHERE COUNCS IS-NOT MISSING
AFTER MATCH HOLD AS DLYBP1 OLD-NOT-NEW
END
-RUN

0 NUMBER OF RECORDS SELECTED= 6561 LINES= 6561
(FOC1425) OVERFLOW, ZERODIVIDE OR CONVERSION ERROR ON ITEM # 4

- The Error Message above repeats at least a thousand times -

0 NUMBER OF RECORDS SELECTED= 1743916 LINES= 392064
0 LINES OF MATCH OUTPUT = 6

TABLE FILE DLYBP1
PRINT
IMDSC1
IMITM
IBMCU
LIPQOH
LILOCN
COLEDG_CODE
COUNCS
IBMCU_1
COLEDG_1
COUNCS_1
BY IMLITM
WHERE COMCU_2 NE COMCU_1
WHERE COLEDG_1 EQ ' '
BY IMLITM
ON TABLE HOLD AS DAILY_BP3
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 6 LINES= 6

-SET &NOREC = IF &LINES EQ 0 THEN 'No Errors were found' ELSE ' ';


TABLE FILE DAILY_BP3
PRINT
IMLITM AS 'Sku,Number'
IMDSC1 AS ',Description'
IMITM AS 'Short,Item No.'
IBMCU AS 'Business,Unit'
LIPQOH AS 'Quantity,On Hand'
LILOCN AS 'Location'
COLEDG_CODE AS 'Cost,Method'
COUNCS AS 'Unit,Cost'
IBMCU_1 AS 'Additional,Branch Plant'
COLEDG_1 AS 'Cost,Method'
COUNCS_1 AS 'Unit,Cost'
BY IMLITM NOPRINT
ON TABLE PCHOLD FORMAT &OUTPUT
ON TABLE SET EMPTYREPORT ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET BYDISPLAY ON
HEADING
" "
"Daily Uncosted BP Components"
"As of: &DATEMDYY &TIME"
" "
"&NOREC.EVAL "
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=.25,
-* RIGHTMARGIN=.25,
TOPMARGIN=.5,
BOTTOMMARGIN=.5,
ORIENTATION=LANDSCAPE,
PAGESIZE=LEGAL,
FONT='TIMES NEW ROMAN',
SQUEEZE=ON,
SIZE=9,$
TYPE=REPORT, GRID=OFF, JUSTIFY=LEFT,$
TYPE=REPORT, TITLETEXT='dly_uncosted_bp_comp',$
TYPE=HEADING, JUSTIFY = CENTER, $
TYPE=HEADING, LINE = 2, SIZE = 12, STYLE = BOLD, $
TYPE=HEADING, LINE = 5, SIZE = 10, STYLE = BOLD, COLOR=RED,$
TYPE=TITLE, SIZE=10, STYLE = BOLD,$
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 6 LINES= 6

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

look up in your manuals the setting for
SET STRICTMATH
its to be set to either OLD or NEW
and has to do with the way divide by zero gets handled.
If you don't have it set explicitly, try settign it to OLD.
There are some special characters in the setting...i can't remember ...so look it up

SET %STRICTMATH%= OLD
i just looked it up

you know, you don't have to HOLD FORMAT FOCUS
in order to index some field and use it in a join.
you can just hold as a foctemp, and the join will work just fine, as long as the join field
is the BY field in each of the two files you're joining.
ON TABLE HOLD is all you need.
...

and you don't need to put your AS fieldname in quotes; thats only for column titles. as long as your AS fieldname is a single word, which is had to be, leave the quotes off.
...
and further down, you've got your BY field listed twice
BY IMLITM
in the TABLE FILE DLYBP1 section

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

Susannah,

I tried using SET %STRICTMATH% = OLD and it did not work. I also took your advice and removed the FORMAT FOCUS INDEX. The only reason I am using AS fieldname in quotes is because I am joining to certain tables twice and in the final report I need the same fieldname twice. I also noticed I had the BY Field "BY IMLITM" twice. But in the end I am still getting the same Error Message (FOC1425) OVERFLOW, ZERODIVIDE OR CONVERSION ERROR ON ITEM # 4.

Is there anything else I can try?

prodrigu,

1st, I would do a JOIN CLEAR * before your MATCH - R4105 is still JOINed to F4101
2nd, In your JOINed structure, you have
WHERE COUNCS GT '.0000'

Where in the MATCH, you have,
WHERE COUNCS NE 0
???

3rd, If the above doesn't work, TABLE FILE F4105 and HOLD; see if there are any errors/messages.

This is probably your problem:

WHERE COUNCS NE 0
WHERE COUNCS IS-NOT MISSING


If not, do the MATCH with that HOLD file

 
TABLE FILE F4105
SUM
COUNCS AS 'COUNCS_1'
COMCU  AS 'COMCU_2'
COLEDG_CODE AS 'COLEDG_1'
BY COITM AS 'IMITM'
BY COMCU AS 'IBMCU_1' 
WHERE COUNCS NE 0
WHERE COUNCS IS-NOT MISSING
  ON TABLE HOLD AS F4105_OUT
END
-RUN
MATCH FILE DAILYBP2
PRINT
IMLITM
IBMCU
IMDSC1
COLEDG_CODE
COUNCS
COMCU_1
LIPQOH
LILOCN
BY IMITM
BY IBMCU_1
RUN
FILE F4105_OUT
PRINT
COUNCS_1
COMCU_2
COLEDG_1
BY IMITM
BY IBMCU_1
AFTER MATCH HOLD AS DLYBP1 OLD-NOT-NEW
END
-RUN
 

Hope this helps...

Tom

I vote for Tom's #3

-- the messages come between

0 NUMBER OF RECORDS SELECTED= 6561 LINES= 6561 (from the first leg of the Match File)
and
0 NUMBER OF RECORDS SELECTED= 1743916 LINES= 392064 (marking the end of the second, before merging takes place),

so the errors occur within the second leg:

FILE F4105
SUM
COUNCS AS 'COUNCS_1'
COMCU AS 'COMCU_2'
COLEDG_CODE AS 'COLEDG_1'
BY COITM AS 'IMITM'
BY COMCU AS 'IBMCU_1'
WHERE COUNCS NE 0
WHERE COUNCS IS-NOT MISSING

Is F4105 located on a database server (as the commented
-*INCLUDE SQLTRACEXOFF
leads me to suspect)? If so, the issue may be in the behind-the-scenes transcription/transformation of the column as received from the RDBMS to the internal layout used by Focus, and Table would test that hypothesis (and perhaps help isolate which values and instances cause the message).

Can you post the synonym for F4105?

This message has been edited. Last edited by: j.gross,

Since this appears to be Peoplesoft(JDEdwards) based on the file names, are you using the real fields or the defines that Webfocus creates at the bottom of the .mas.
Since JDE doesn't store decimal places, it sounds as if running live uses the defined field (that includes the decimal places) and running through RC is using the real field that doesn't have the decimal places...Is that possible?

I have to doubt that the "The report runs fine with the error message when you run the report On-Demand." If you have any output take a look at your numbers. Do they have all zeros after the decimal? As Pat pointed out, Peoplesoft and JDE don't carry the decimal places. WebFocus aligns the decimal point so a field like the one below that has 15 digits would overflow. Also keep in mind that if the database is on an AS400 or Mainframe the number is really packed. If you don't have defined fields at the bottom of the master you will need to define the field dividing the number by however many decimal places there should be. You can do this in the program or in the master.

FIELD=INVOICE_AMOUNT,
ALIAS='I1$IVA', USAGE=P15.2NS , ACTUAL=P8.0,
DESCRIPTION='Invoice Amount',
TITLE=' Invoice, Amount',$

is defined as
INVOICE_AMT/P15.2=I1$IVA/100;

The synonym for F4105 is:

FILENAME=F4105, SUFFIX=EDA , $
SEGMENT=F4105, SEGTYPE=S0, $
FIELDNAME=COITM, ALIAS=COITM, USAGE=P10, ACTUAL=P5, $
FIELDNAME=COMCU, ALIAS=COMCU, USAGE=A12, ACTUAL=A12, $
FIELDNAME=COLOCN, ALIAS=COLOCN, USAGE=A20, ACTUAL=A20, $
FIELDNAME=COLOTN, ALIAS=COLOTN, USAGE=A30, ACTUAL=A30, $
FIELDNAME=COLEDG_CODE, ALIAS=COLEDG_CODE, USAGE=A2, ACTUAL=A2, $
FIELDNAME=COLITM, ALIAS=COLITM, USAGE=A25, ACTUAL=A25, $
FIELDNAME=COAITM, ALIAS=COAITM, USAGE=A25, ACTUAL=A25, $
FIELDNAME=COLOTG_CODE, ALIAS=COLOTG_CODE, USAGE=A3, ACTUAL=A3, $
FIELDNAME=COUNCS, ALIAS=COUNCS, USAGE=P17.4, ACTUAL=P8, $
FIELDNAME=COCSPO, ALIAS=COCSPO, USAGE=A1, ACTUAL=A1, $
FIELDNAME=COCSIN, ALIAS=COCSIN, USAGE=A1, ACTUAL=A1, $
FIELDNAME=COURCD, ALIAS=COURCD, USAGE=A2, ACTUAL=A2, $
FIELDNAME=COURDT, ALIAS=COURDT, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=COURAT, ALIAS=COURAT, USAGE=P17.2, ACTUAL=P8, $
FIELDNAME=COURAB, ALIAS=COURAB, USAGE=P10, ACTUAL=P5, $
FIELDNAME=COURRF, ALIAS=COURRF, USAGE=A15, ACTUAL=A15, $
FIELDNAME=COUSER, ALIAS=COUSER, USAGE=A10, ACTUAL=A10, $
FIELDNAME=COPID, ALIAS=COPID, USAGE=A10, ACTUAL=A10, $
FIELDNAME=COJOBN, ALIAS=COJOBN, USAGE=A10, ACTUAL=A10, $
FIELDNAME=COUPMJ, ALIAS=COUPMJ, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=COTDAY, ALIAS=COTDAY, USAGE=P8, ACTUAL=P4, $
FIELDNAME=COLEDG_DESC, ALIAS=COLEDG_DESC, USAGE=A28, ACTUAL=A28, $
FIELDNAME=COLOTG_DESC, ALIAS=COLOTG_DESC, USAGE=A26, ACTUAL=A26, $

I notice that the data request has
WHERE COUNCS IS-NOT MISSING

but the field is not declared with MISSING=ON:
FIELDNAME=COUNCS, ALIAS=COUNCS, USAGE=P17.4, ACTUAL=P8, $

Since you have suffix=EDA, there are actually two WebFocus or iWAY servers invoved here, each with its own respective synonym. If the real data reside on a relational database server, then there's a third definition in the mix.

If the two (or three) data definitions are not in harmony with regard to the Nulls/Missing attribute, then source rows with this column missing might result in the data exceptions you received. -- For example, if the remote EDA server has MISSING=ON, and passes back the data in character format, the local EDA server will receive " . " as the value and (since it has MISSING=OFF) treat that as an invalid value.

Check the local and remote synonyms (both Master and Access) for agreement with each other (and with the SQL table definition if applicable).

prodrigu,

I think j.gross is right - there must be some mismatch in the data definition(s).

The report may indeed run fine outside of report caster, but not within. In both cases the messages are produced, but these are warnings, not abortive messages. Only when you run it from report caster it will also flag warning messages as being abortive.