Where (Disputes.Transaction_num NE AGING.TRNSCTN_NUMBER )  

-*-- Enable DB2 Outer Join -------------
SQL DB2 SET SQLJOIN OUTER ON
END
-RUN

JOIN
LEFT_OUTER AGING.TRNSCTN_NUMBER IN AGING TO Disputes.Transaction_num IN Disputes 
AS J1
END
-RUN

TABLE FILE AGING
PRINT
...
WHERE Disputes.Transaction_num IS MISSING;
END
-RUN

SQL DB2
SELECT
...
FROM AGING
LEFT OUTER JOIN Disputes ON Disputes.Transaction_num = AGING.TRNSCTN_NUMBER

WHERE Disputes.Transaction_num IS NULL
FOR FETCH ONLY;

TABLE ON TABLE HOLD AS H001
END
-RUN

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

 SET ALL = PASS
JOIN
 LEFT_OUTER DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER IN
DR_DETAIL_AGING_V TO UNIQUE DR_INV_NOTES_V.DR_INV_NOTES_V.TRNSCT_NUM
 IN DR_INV_NOTES_V TAG J0 AS J0
 END
JOIN
 LEFT_OUTER DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER IN
DR_DETAIL_AGING_V TO UNIQUE DR_DISPUTES_V.DR_DISPUTES_V.TRNSCT_NUM
 IN DR_DISPUTES_V TAG J1 AS J1
 END
TABLE FILE DR_DETAIL_AGING_V
SUM
     'DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.INVOICEAMOUNT/P20.2CM'
 AS 'Obligation Amt'
     'CNT.DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER'
 AS 'Number of Invoices'
BY 'DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.COLLECTOR_NAME' AS 'Collector'
BY 'DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.COMPANY_ID' AS 'Business'
ON TABLE SUBHEAD
""
HEADING
"Invoices over 10 Days Past Due Follow Up"
FOOTING
""

WHERE ( DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.COMPANY_ID EQ '&Business' ) AND ( DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.COLLECTOR_NAME EQ '&Collector' )
	AND( DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRANSACTION_TYPE EQ 'Invoice' ) AND ( DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.PASTDUEDAYS GT 10 )
	AND ( J0.DR_INV_NOTES_V.TRNSCT_NUM NE DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER ) AND ( J1.DR_DISPUTES_V.TRNSCT_NUM NE DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER); 

  ( J0.DR_INV_NOTES_V.TRNSCT_NUM NE DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER )

quote:

You should turn on SQL trace to see how the code is being translated into SQL.

  0 NUMBER OF RECORDS IN TABLE=        3  LINES=      1
 15.47.26 BS  (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
 15.47.26 BS  (FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
 15.47.26 BS  (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL  : WHERE exp
 15.47.26 BS  (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL  : WHERE exp
 15.47.26 BS  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 15.47.26 BS  (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
 15.47.26 AE    SELECT T1."COMPANY_ID",T1."COLLECTOR_NAME",
 15.47.26 AE   T1."TRANSACTION_TYPE",T1."TRNSCTN_NUMBER",T1."INVOICEAMOUNT",
 15.47.26 AE   T1."PASTDUEDAYS" FROM EMAGIA.DR_DETAIL_AGING_V T1 WHERE
 15.47.26 AE   (T1."PASTDUEDAYS" > 10) AND (T1."TRANSACTION_TYPE" = 'Invoice')
 15.47.26 AE   AND (T1."COLLECTOR_NAME" = 'Roslyn Tompkins') AND
 15.47.26 AE   (T1."COMPANY_ID" = '02084') FOR FETCH ONLY;
 15.47.26 AE    SELECT T2."TRNSCT_NUM" FROM EMAGIA.DR_INV_NOTES_V T2 WHERE
 15.47.26 AE   (T2."TRNSCT_NUM" = ?) FOR FETCH ONLY;
 15.47.26 AE    SELECT T3."TRNSCT_NUM" FROM EMAGIA.DR_DISPUTES_V T3 WHERE
 15.47.26 AE   (T3."TRNSCT_NUM" = ?) FOR FETCH ONLY;
 (FOC1400) SQLCODE IS -905 (HEX: FFFFFC77)
 : [57014] [IBM][CLI Driver][DB2] SQL0905N  Unsuccessful execution due to r
 : esource limit being exceeded.  Resource name = "ASUTIME", limit = "00000
 : 0000041" CPU seconds ("000000948600" service units) derived from "DB2ADM
 : .DSNRLST01".  SQLSTATE=57014
 L    (FOC1406) SQL OPEN CURSOR ERROR.  : DR_DISPUTES_V