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,

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...

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

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