How to return 122345789 from the numeric value 1234567.89

[awaria]

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

 

[awaria]

Resolved. Needed to use BIGINT.

Thanks, zaw

 

[Andrzejek]

If this:
[tt]SELECT
SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN
TRAN_AMOUNT ELSE 0 END) AS AR FROM GLTRANS[/tt]

gives you [tt]50881616.07[/tt], but you want [tt]5088161607[/tt]

wouldn't [blue]this[/blue] do the trick?
[tt]SELECT
SUM(CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN
TRAN_AMOUNT ELSE 0 END) [blue]* 100[/blue] AS AR FROM GLTRANS[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[awaria]

That produced 5088161607.00

I used the cast as BIGINT to drop the .00

Thanks

 

[janni78]

The topic throws you off a bit since that would actually fit in an INT.

Integer constants greater than 2,147,483,647 are converted to the decimal data type

Which is why you got the issue and BIGINT solved it.

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

 

[Olaf Doschke]

For consistency in any case you'd either use that cast in both THEN and ELSE branch (1) or have an overall CAST (2):

(1) ...THEN CAST(TRAN_AMOUNT*100 AS BIGINT) ELSE [b]CAST(0 AS BIGINT)[/b] END
(2) [b]CAST([/b]CASE WHEN ACCT_UNIT = '90015' AND ACCOUNT = '1251' THEN TRAN_AMOUNT*100 ELSE 0 END [b]AS BIGINT)[/b]

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.

DECLARE @intcolumn as Int
SET @intcolumn = CAST(2147483647 as Int) -- corner case test
SELECT CAST( @intcolumn*100 as Bigint)-- overflow error
SELECT CAST( CAST(@intcolumn as Bigint)*100 as Bigint) -- works

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

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

Now, this nests two BigInt casts, even though once TRAN_AMOUNT is converted before the operation *100 that result should stay within the BigInt type, but the outmost CAST also ensures the 0 in ELSE is converted to BigInt. Alternatively, a less nested version - may be easier to read is:

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

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

Bye, Olaf.