Focal Point Banner


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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED
 Login/Join
 
Prasanna
Gold member
posted
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


 
Posts: 80 | Registered: March 21, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
Prasanna

Please post your code in between [ code] and [ /code] (forget the spaces)

I suppose you have a sum in your difine but without the code it is difficult to be sure.




Frank
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, 2006Report This Post
Prasanna
Gold member
posted Hide Post
Here is the model of the program that i am trying.
DEFINE FILE T1
COL1/D12=IF COL2='Y' THEN AMOUNT ELSE 0;
END
-RUN
TABLE FILE T1
SUM 
COL3
COL1
BY CNUM
BY MBRID
WHERE CNUM EQ &CLIENT
ON TABLE PCHOLD FORMAT HTML
END 
-RUN


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


 
Posts: 80 | Registered: March 21, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
If Amount is a filed in your T1 master AND if COL2 is an alpha field in your T1 master then this should work.

Is it?

And what about the other fields?

Can you post your master?




Frank
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, 2006Report This Post
focuzsambit
Platinum Member
posted Hide Post
wats the datatype for AMOUNT.
Same should be thr for col1.
if its D20, then for Col1 it should also be D20


WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
 
Posts: 133 | Registered: May 12, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
Focuszambit

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.




Frank
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, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
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
SUM 
AMOUNT
COL3
BY CNUM
BY MBRID
BY COL2
WHERE CNUM EQ &CLIENT
ON TABLE HOLD AS H001
END
-RUN

DEFINE FILE H001
COL1/D12=IF COL2='Y' THEN AMOUNT ELSE 0;
END
-RUN

TABLE FILE H001
SUM 
COL3
COL1
BY CNUM
BY MBRID
ON TABLE PCHOLD FORMAT HTML
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
jgelona
Master
posted Hide Post
Try using COMPUTE if there is only one COL2 for every CNUM and MBRID:
TABLE FILE T1
SUM 
COL3
AMOUNT NOPRINT
MAX.COL2 NOPRINT
COMPUTE COL1/D12=IF MAX.COL2='Y' THEN AMOUNT ELSE 0;
BY CNUM
BY MBRID
WHERE CNUM EQ &CLIENT
ON TABLE PCHOLD FORMAT HTML
END 
-RUN

Depending on how your data relationships and structure, this may work.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED

Copyright © 1996-2020 Information Builders