Now I also need to join a disputes table to the aging table and filter out all of the transaction numbers that are in the disputes table.
When I joined the Disputes table to the Aging table and filtered out all of the aging transaction numbers that are in the disputes table I received an error message saying that there are too many records returned.
This is what my where looks like:
Where (Disputes.Transaction_num NE AGING.TRNSCTN_NUMBER )
This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.9 Windows all output (Excel, PDF, HTML)
April 20, 2010, 04:08 PM
Francis Mariani
SQL and WebFOCUS both have a limited number of values that can be in a filter - SQL's limit is 1000.
Since these are DB2 tables, I would suggest doing a left outer join and filtering on a column in the cross reference table. Something like this:
-*-- 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
Because left outer join is oddly implemented in WebFOCUS (I think it gets fixed in some version newer than v7.6.5), this may actually not work - you may need to use SQL passthru instead:
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
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
April 27, 2010, 01:24 PM
BJosipovic
I tried doing a left outer join and filtering on a column but that didn't work either. That is the same thing I did for the first filter.
The thing that I do not understand is why does it return results when I have the invoice_notes.transaction_number filter but when I add a second filter, I get an error message saying that there are too many records being returned. Isn't the second filter supposed to return less results than only one filter?
WebFOCUS 7.6.9 Windows all output (Excel, PDF, HTML)
April 27, 2010, 02:04 PM
Francis Mariani
Post the complete code.
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
April 27, 2010, 02:43 PM
Dan Satchell
You should turn on SQL trace to see how the code is being translated into SQL. I suspect the addition of the second filter is, for some reason, causing WebFOCUS to manage the join instead of the database. This would result in all records being returned to WebFOCUS from all tables involved in the query.
WebFOCUS 7.7.05
April 27, 2010, 02:47 PM
BJosipovic
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);
For Collector A it returns 237 invoices For Collector B I get an error because there are to many results
If I run the same report with only one filter
( J0.DR_INV_NOTES_V.TRNSCT_NUM NE DR_DETAIL_AGING_V.DR_DETAIL_AGING_V.TRNSCTN_NUMBER )
instead of the two I get 237 invoices for Collector A and 956 invoices for Collector B.
My questions is shouldn't the the two filters return less than 956 invoices for Collector B because using only 1 filter gets me 956 results and 2 should either be the same or less than that
WebFOCUS 7.6.9 Windows all output (Excel, PDF, HTML)
April 27, 2010, 03:37 PM
njsden
As Dan said:
quote:
You should turn on SQL trace to see how the code is being translated into SQL.
Then, post the whole SQL translation in your trace (including any FOC messages and with any HTML entities already decoded, please!) so we can see what's going on ...
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
WebFOCUS 7.6.9 Windows all output (Excel, PDF, HTML)
April 27, 2010, 04:13 PM
Dan Satchell
The trace clearly shows that WebFOCUS is managing the join, which causes your query to exceed resource limits. I think you have two options:
1. if you are comfortable with coding SQL, code the query using pass-thru SQL so you can create a single, efficient query 2. split your query into two separate queries so you can apply as many filter criteria as possible to each, and join the resulting hold files in WebFOCUS to generate the final output