As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
Hi, I am trying to pull some calculation which is there in DEFINE in to TABLE FILE. But while creating the SQL query it is not recognizing the DEFINED column and it is throwing following error. AGGREGATION NOT DONE FOR THE FOLLOWING REASON: (FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED
I tried creating separate table where i gave the column that i needed in the DEFINE, and put the condition that i validated in DEFINE as WHERE condition. Later i thought of merging both the table using MATCH. But the second table is not giving me the desired result.
Does anyone faced same type of issue or previous forum discussion is available?
Dev: Dev Studio 7.1.6,Report Caster 7.1.1,Servlet Local: Dev Studio 7.1.6,Report Caster 7.1.1,Apache 5.0.2, Win XP
You are not right on all points. You may change te picture form D20 to D12 but you must realize that the result might not fit, but if the used value is less than 12 characters it would not give any problem.
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006
A lot of DEFINE statements will not get translated to SQL.
The message you get is not an error, but a warning - the report should still run, though it could take a very long time because the data is not aggregated by the DBMS.
In situations like this I usually do not perform the DEFINE in the data extraction part of my program, I would do something like this:
TABLE FILE T1
WHERE CNUM EQ &CLIENT
ON TABLE HOLD AS H001
DEFINE FILE H001
COL1/D12=IF COL2='Y' THEN AMOUNT ELSE 0;
TABLE FILE H001
ON TABLE PCHOLD FORMAT HTML
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