## How to return 122345789 from the numeric value 1234567.89

## How to return 122345789 from the numeric value 1234567.89

(OP)

sql server 2008 R2

initial Query for review;

SELECT

SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN TRAN_AMOUNT ELSE 0 END) AS AR FROM GLTRANS

This returns 50881616.07

desired result is 5088161607. No decimal.

Tried using cast as int, query below for review;

SELECT SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN CAST(TRAN_AMOUNT*100 AS INT) ELSE 0 END) AS AR FROM GLTRANS

but

getting; Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

Appreciate help with this.

Thanks in advance, zaw

initial Query for review;

SELECT

SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN TRAN_AMOUNT ELSE 0 END) AS AR FROM GLTRANS

This returns 50881616.07

desired result is 5088161607. No decimal.

Tried using cast as int, query below for review;

SELECT SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN CAST(TRAN_AMOUNT*100 AS INT) ELSE 0 END) AS AR FROM GLTRANS

but

getting; Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

Appreciate help with this.

Thanks in advance, zaw

## RE: How to return 122345789 from the numeric value 1234567.89

Thanks, zaw

## RE: How to return 122345789 from the numeric value 1234567.89

SELECT

SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN

TRAN_AMOUNT ELSE 0 END) AS AR FROM GLTRANS

gives you 50881616.07, but you want 5088161607

wouldn't this do the trick?

SELECT

SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN

TRAN_AMOUNT ELSE 0 END) * 100 AS AR FROM GLTRANS

---- Andy

There is a great need for a sarcasm font.## RE: How to return 122345789 from the numeric value 1234567.89

I used the cast as BIGINT to drop the .00

Thanks

## RE: How to return 122345789 from the numeric value 1234567.89

Integer constants greater than 2,147,483,647 are converted to the decimal data typeWhich is why you got the issue and BIGINT solved it.

"Trying is the first step to failure..." - Homer

## RE: How to return 122345789 from the numeric value 1234567.89

## CODE

I'm not sure a high TRAN_AMOUNT*100 would get converted precise or at all, as it would need to be decimal/double float intermediately, but we can test the corner case of the biggest int.

## CODE

So it seems @intcolumn*100 is not implicitly converted to decimal or a float type at all, and you even would need to convert to BigInt before multiplying with 100 to make sure this works out. Your seem to not fail on this detail, as it's not the single values crossing the range limit, but the overall SUM.

Something scaling up to BigInt limits all the way even in every single summand would be

## CODE

## CODE

The overall idea is to first convert to the datatype you want to calculate with, then calculate using its full range.

Bye, Olaf.