Arithmetic OverFlow Error in Universe

[former_member196240]

Hello All,

In my Universe when i do the joins between two tables for the Plant field and  then validate, I get the below error. Could you please help to address?

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type int.

Error Screen:

[Former Member]

Hi Vinay,

Removing IDT from picture, when you simply write a SQL joining these tables against database directly, does the query executes properly or do you receive any error?

This will help us in troubleshooting on which level does the issue exist.

Regards,

Yuvraj

[former_member196240]

Hi Yuvraj,

I have  written a SQL query by joining both of these tables and SQL is able to execute the query without any issues.

Looks strange but that's how universe is responding. But have moved the universe and report to QA and the reports are working fine without any issues.

thanks,

Vinay

[former_member207052]

How many columns do you have in each of these tables?  Since this is a Database error, use MS SQL server studio and try to create a simple query with the same join. If you get the same error (for sure you will) try selecting only top N rows to see if it works.

"Arithmetic Overflow" error occurs when the limits of the data types used are breached.

The issue could be that you are exceeding the # of rows (defined by the integer type limits) while creating the joins.

[Former Member]

Hi Vinay,

It seems like your schema is not updated in IDT.

So I will suggest you to go to Action tab and click on Refresh structure to refer all the tables structure.

After refresh please check your code again.

Thanks,

Swapnil

[former_member196240]

Hi Swapnil,

I just refreshed the structure and again validated the join, but the issue is still outstanding.

thanks,

Vinay

[Former Member]

can you please try to convert your colums to char & then join it.

You can use following code

CAST([column] AS CHAR([required length]))

Note: In join condition itself you can add above code & then try to parse it.

Thanks,

Swapnil

[former_member196240]

Thanks Swapnil, I tried the same way as you mentioned but still no luck. Please let me know if  anything wrong with the code.

[Former Member]

Does it give more information when you click on the "show details" button?

[former_member196240]

This is the message when click to show details:

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type int.

[Former Member]

So, you join the plant column to the werks column and you get that error?

Have you carried out an integrity check on the whole universe? If not, I would do so now.

[former_member196240]

Yes, when WERKS is being joined  the issue can be reproduceable. Integrity check is been carried out, and the error what I got is "Check Joins".

[Former Member]

And the structure is definitely up to date? (as in nobody has changed the data types because the error is looking for an integer overflow and you've got char colums, which doesn't follow)

[former_member196240]

I dont think WERKS underwent any change in the data type, other then my Data Services job which updates the MARC and Faield fact table where WERKS is part of those tables. But that's data refresh nothing more then that.

With that being said, have published the Universe in the repository and my WEBi reports are working fine.

So, just curious shall i move the Universe to QA or will there any negative impact??

[Former Member]

So there's no message saying that the structure of either of the tables has changed?

[former_member196240]

nope, no message

[Former Member]

It could well be a bug then. I've seen a few people with issues on joins and conditions not behaving as you'd expect.

[amitrathi239]

Hi,

It seems issue with Plant and Werks data type.Check the data type of these objects in the SQL server.

Use bigint instead of int data type in database and then try in universe.

Amit

[former_member196240]

Hi Amit,

I just checked the data type for Plant and it looks to be same in the both the tables

[former_member196240]

Hi , and Former Member have you had a chance to see this thread??