[SOLVED] (FOC1539) STATIC TABLE: SCREENING DATA OUT OF RANGE
Hello,
The code below gives me the report I expect. As soon as I uncomment the SUM fields I get " (FOC1539) STATIC TABLE: SCREENING DATA OUT OF RANGE :" Any help would be appreciated.
JOIN
F0005.F0005.DRDL01 IN F0005 TO MULTIPLE F4008.F4008.TAX_RATE_AREA IN F4008
AS J0
END
JOIN
F4008.F4008.TAX_AUTHORITY IN F0005 TO MULTIPLE F0101.F0101.ADDRESS_NUMBER
IN F0101 AS J3
END
JOIN
F4008.F4008.TAX_AUTHORITY_2 IN F0005 TO MULTIPLE F0101.F0101.ADDRESS_NUMBER
IN F0101 AS J4
END
JOIN
F4008.F4008.TAX_AUTHORITY_3 IN F0005 TO MULTIPLE F0101.F0101.ADDRESS_NUMBER
IN F0101 AS J5
END
JOIN
F4008.F4008.TAX_AUTHORITY_4 IN F0005 TO MULTIPLE F0101.F0101.ADDRESS_NUMBER
IN F0101 AS J6
END
JOIN
F4008.F4008.TAX_AUTHORITY_5 IN F0005 TO MULTIPLE F0101.F0101.ADDRESS_NUMBER
IN F0101 AS J7
END
DEFINE FILE F0005
TAXTOTAL/D5.2 = TAX_RATE_1 + TAX_RATE_2 + TAX_RATE_3 + TAX_RATE_4 + TAX_RATE_5;
END
TABLE FILE F0005
SUM
TAXTOTAL NOPRINT
J3ADDRESS_NUMBER AS ''
J3ALPHA_NAME AS ''
TAX_AUTHORITY AS ''
TAX_RATE_1 AS ''
OVER
J4ADDRESS_NUMBER AS ''
J4ALPHA_NAME AS ''
TAX_AUTHORITY_2 AS ''
TAX_RATE_2 AS ''
OVER
-* J5ADDRESS_NUMBER AS ''
-* J5ALPHA_NAME AS ''
TAX_AUTHORITY_3 AS ''
TAX_RATE_3 AS ''
OVER
-* J6ADDRESS_NUMBER AS ''
-* J6ALPHA_NAME AS ''
TAX_AUTHORITY_4 AS ''
TAX_RATE_4 AS ''
OVER
-* J7ADDRESS_NUMBER AS ''
-* J7ALPHA_NAME AS ''
TAX_AUTHORITY_5 AS ''
TAX_RATE_5 AS ''
BY DRKY AS 'Tax,Code'
BY TAX_AREA_DESCRIPTION AS 'Tax Area,Description'
ON DRKY SUBFOOT
"Total Tax : <TAXTOTAL "
END
This message has been edited. Last edited by: Kerry,
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
September 03, 2008, 03:43 PM
Francis Mariani
I would turn SQL Traces on to see if there are messages. There may be SQl rules to consider - you're joining to the same table five times, perhaps DB2 doesn't like that...
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 03, 2008, 03:59 PM
Sayed
This is the SQL I get on the trace. I need to join to the table as a lookup for the ALPHA_NAME to corresponding ADDRESS_NUMBER. I couldn't think of another way of achieving this. The layout of the report is important.
15.58.25 AE SELECT T1."DRSY",T1."DRRT",T1."DRKY",T1."DRDL01" FROM 15.58.25 AE HBAPRDCTL.F0005 T1 ORDER BY T1."DRSY",T1."DRRT",T1."DRKY"; 15.58.25 AE SELECT T2."TATXA1",T2."TAITM",T2."TAEFDJ",T2."TATAXA", 15.58.25 AE T2."TATA1",T2."TATXR1",T2."TATA2",T2."TATXR2",T2."TATA3", 15.58.25 AE T2."TATXR3",T2."TATA4",T2."TATXR4",T2."TATA5",T2."TATXR5" FROM 15.58.25 AE HBAPRDDTA.F4008 T2 WHERE (T2."TATXA1" = ?) ORDER BY T2."TATXA1", 15.58.25 AE T2."TAITM",T2."TAEFDJ"; 15.58.25 AE SELECT T3."ABAN8",T3."ABALPH" FROM HBAPRDDTA.F0101 T3 WHERE 15.58.25 AE (T3."ABAN8" = ?) ORDER BY T3."ABAN8"; 15.58.25 AE SELECT T4."ABAN8",T4."ABALPH" FROM HBAPRDDTA.F0101 T4 WHERE 15.58.25 AE (T4."ABAN8" = ?) ORDER BY T4."ABAN8"; 15.58.25 AE SELECT T5."ABAN8",T5."ABALPH" FROM HBAPRDDTA.F0101 T5 WHERE 15.58.25 AE (T5."ABAN8" = ?) ORDER BY T5."ABAN8"; 15.58.25 AE SELECT T6."ABAN8",T6."ABALPH" FROM HBAPRDDTA.F0101 T6 WHERE 15.58.25 AE (T6."ABAN8" = ?) ORDER BY T6."ABAN8"; 15.58.25 AE SELECT T7."ABAN8",T7."ABALPH" FROM HBAPRDDTA.F0101 T7 WHERE 15.58.25 AE (T7."ABAN8" = ?) ORDER BY T7."ABAN8"; ...RETRIEVAL KILLED 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
Sayed
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
September 03, 2008, 05:08 PM
Sayed
Changing my JOIN's seems to fix my issue.
JOIN
F0005.F0005.DRDL01 IN F0005 TO MULTIPLE F4008.F4008.TAX_RATE_AREA IN F4008
AS J0
END
JOIN
LEFT_OUTER F4008.F4008.TAX_AUTHORITY IN F0005 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG T1 AS J3
END
JOIN
LEFT_OUTER F4008.F4008.TAX_AUTHORITY_2 IN F0005 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG T2 AS J4
END
JOIN
LEFT_OUTER F4008.F4008.TAX_AUTHORITY_3 IN F0005 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG T3 AS J5
END
JOIN
LEFT_OUTER F4008.F4008.TAX_AUTHORITY_4 IN F0005 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG T4 AS J6
END
JOIN
LEFT_OUTER F4008.F4008.TAX_AUTHORITY_5 IN F0005 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG T5 AS J7
END
DEFINE FILE F0005
TAXTOTAL/D5.2 = TAX_RATE_1 + TAX_RATE_2 + TAX_RATE_3 + TAX_RATE_4 + TAX_RATE_5;
END
TABLE FILE F0005
SUM
'F4008.F4008.TAXTOTAL' NOPRINT
'T1.F0101.ADDRESS_NUMBER' AS ''
'T1.F0101.ALPHA_NAME' AS ''
'F4008.F4008.TAX_AUTHORITY' AS ''
'F4008.F4008.TAX_RATE_1' AS '' OVER
'T2.F0101.ADDRESS_NUMBER' AS ''
'T2.F0101.ALPHA_NAME' AS ''
'F4008.F4008.TAX_AUTHORITY_2' AS ''
'F4008.F4008.TAX_RATE_2' AS '' OVER
'T3.F0101.ADDRESS_NUMBER' AS ''
'T3.F0101.ALPHA_NAME' AS ''
'F4008.F4008.TAX_AUTHORITY_3' AS ''
'F4008.F4008.TAX_RATE_3' AS '' OVER
'T4.F0101.ADDRESS_NUMBER' AS ''
'T4.F0101.ALPHA_NAME' AS ''
'F4008.F4008.TAX_AUTHORITY_4' AS ''
'F4008.F4008.TAX_RATE_4' AS '' OVER
'T5.F0101.ADDRESS_NUMBER' AS ''
'T5.F0101.ALPHA_NAME' AS ''
'F4008.F4008.TAX_AUTHORITY_5' AS ''
'F4008.F4008.TAX_RATE_5' AS ''
BY 'F0005.F0005.DRKY' AS 'Tax,Code'
BY 'F4008.F4008.TAX_AREA_DESCRIPTION' AS 'Tax Area,Description'
END
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE