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     [SOLVED] Arithmetic overflow error converting expression to data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Arithmetic overflow error converting expression to data
 Login/Join
 
BM
Platinum Member
posted
Hi,
Here is a situation..
In my master file the data type if the field is

Actual: I11
USAGE: P20

When I try to SUM the field I am getting the error below.
I realize that I can create a Define and Convert the field into BIGINT but not sure if there is another way.


(8115) [S0002] Arithmetic overflow error converting expression to data
: type int.

I have tried change the metadata to

Actual: P10
USAGE: P20

but that doesn't work either.


Thanks for your help

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Hi BM

Try ACTUAL=D10 and USAGE=D20 or ACTUAL=I11 and USAGE=I11


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
jgelona
Master
posted Hide Post
What kind of file?


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
BabakNYC
Virtuoso
posted Hide Post
Is this a synonym you created off of a DBMS or was it created with HOLD syntax? It'll help us if you provide details such as the source of the data associated with this synonym, do you have this problem with any other table, how you created the synonym and what the column looks like in the original table.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
BM
Platinum Member
posted Hide Post
Master file created off of DBMS (SQL)


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
BM
Platinum Member
posted Hide Post
quote:
Originally posted by Chuck Wolff:
Hi BM

Try ACTUAL=D10 and USAGE=D20 or ACTUAL=I11 and USAGE=I11


Tried both suggestions. This is the error I get

(8115) [S0002] Arithmetic overflow error converting expression to data
: type int.


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
BM
Platinum Member
posted Hide Post
quote:
Originally posted by BabakNYC:
Is this a synonym you created off of a DBMS or was it created with HOLD syntax? It'll help us if you provide details such as the source of the data associated with this synonym, do you have this problem with any other table, how you created the synonym and what the column looks like in the original table.


Master field off of DBMS (SQL).
In SQL table the field is declared as INT.
The issue arise when SUM that field. In SQL I have to convert to BIGINT before I can SUM that field.

I was hoping that in MetaData I can simply change the Actual to P16 and it would let me SUM it but doesn't look like it.
I can create a DEFINE with that field and convert but I am trying to avoid it.

Is there any other way other than creating a Define field.


Thanks


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Try this way
TABLE FILE acb
SUM yourField/P16
BY ...
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BabakNYC
Virtuoso
posted Hide Post
You should never change the ALIAS= or ACTUAL= of a master file for a DBMS. Those two are what the DBMS told WebFOCUS the real column name and the internal format of that column is.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
BM
Platinum Member
posted Hide Post
quote:
Originally posted by MartinY:
Try this way
TABLE FILE acb
SUM yourField/P16
BY ...
END


Thanks but didn't work


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
BM
Platinum Member
posted Hide Post
I guess no other way other than adding a Define field that converts the field to BIGINT?


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
jgelona
Master
posted Hide Post
The problem I have with the original post is the ACTUAL=I11. We have Oracle, and all integers are stored in full words, I4 or I8. An ACUTAL of I11 is not on a full word boundary. I'm kind of surprised that the synonym builder did not build it correctly. We have some fields in our database that are defined has just a NUMBER with no size. When the synonym builder generates the field, it has USAGE=D20, ACUTAL=D8. Again all the actuals are full words so ACTUAL=D10 is not a full word so I don't see how that could work. I can't recall ever seeing a number format with ORACLE where the ACTUAL format is DX or IX where X is not 4 or 8.

This message has been edited. Last edited by: jgelona,


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
FP Mod Chuck
Virtuoso
posted Hide Post
Very good point jgelona! I agree with what you have said. BM I would recommend changing the ACTUAL to I4 or I8 and give it a try.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
BM
Platinum Member
posted Hide Post
Thanks.. Changed the Actual to I4 and Usage to I8.
Still the same error.


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report This Post
Tony A
Expert
posted Hide Post
Why not issue an ALTER statement in SQL and then regenerate the Synonym?

ALTER TABLE [table name] ALTER COLUMN [column name] BIGINT


T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
jgelona
Master
posted Hide Post
BM, I would go with USAGE=D20 and ACTUAL=D8, if that doesn't work try regenerating the synonym.


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
Tony A
Expert
posted Hide Post
I would also be inclined to trap the SQL being produced to see if the sum is being actioned within the SQL (as it should, if all is well).

If that is the case then no amount of changed USAGE and ACTUAL is likely to make any difference (a bit like you're experiencing!).

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
BM
Platinum Member
posted Hide Post
quote:
Originally posted by Tony A:
I would also be inclined to trap the SQL being produced to see if the sum is being actioned within the SQL (as it should, if all is well).

If that is the case then no amount of changed USAGE and ACTUAL is likely to make any difference (a bit like you're experiencing!).

T


Thanks all.
The SUM is being captured by SQL.
However, if I were to take that SQL and run it in SQL Server management studio, I still have to convert it to BIG INT as the field is declared as INT on the SQL table.


WebFOCUS 8202M
 
Posts: 167 | Location: Montreal | Registered: September 23, 2014Report 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     [SOLVED] Arithmetic overflow error converting expression to data

Copyright © 1996-2020 Information Builders