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,

Hi BM

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

What kind of file?

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 file created off of DBMS (SQL)

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.

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

Try this way

TABLE FILE acb
SUM yourField/P16
BY ...
END

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.

quote:

Originally posted by MartinY:
Try this way

TABLE FILE acb
SUM yourField/P16
BY ...
END

Thanks but didn't work

I guess no other way other than adding a Define field that converts the field to BIGINT?

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,

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.

Thanks.. Changed the Actual to I4 and Usage to I8.
Still the same error.

Why not issue an ALTER statement in SQL and then regenerate the Synonym?

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

T

BM, I would go with USAGE=D20 and ACTUAL=D8, if that doesn't work try regenerating the synonym.

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

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.