INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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


RE: How to return 122345789 from the numeric value 1234567.89

(OP)
Resolved. Needed to use BIGINT.

Thanks, zaw

RE: How to return 122345789 from the numeric value 1234567.89

If this:
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

(OP)
That produced 5088161607.00

I used the cast as BIGINT to drop the .00

Thanks

RE: How to return 122345789 from the numeric value 1234567.89

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

RE: How to return 122345789 from the numeric value 1234567.89

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

CODE

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

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

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

CODE

(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:

CODE

(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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close