All Forums Analytics
kirankrj12 3 posts Joined 11/14
10 Feb 2015
How to convert a 32 Bit Integer Number to its negative values as same like C Concepts

Hi,
I've scenario where I need to convert an Integer value to its Negative range
As we know that, range of Integer(32 Bit) is - 231 to 231-1
When I try to get the negative value, for example 3434340000 should return -1286856352
But below return with numeric overflow, which is an expected error
SEL CAST (3434340000 AS INTEGER)
But do we have any workaround, so that when I provide a number crossing the range of INT (32 Bit) and will end up getting negative value.
 
Is there any way to control such exceptional handlings?

Regards, Kiran
kirankrj12 3 posts Joined 11/14
10 Feb 2015

Range of Integer over here : -2147483648 to 2147483647

Regards,
Kiran

dnoeth 4628 posts Joined 11/04
14 Feb 2015

Hi Kiran,
why do you need this?

CAST(CASE 
       WHEN   CAST(x AS BIGINT) > 2147483647
       THEN -(CAST(x AS BIGINT) - 2147483648) 
       ELSE   CAST(x AS BIGINT) 
     END AS INT)

This matches your example.
But what result do you expect for 2147483648? 0?
 

Dieter

kirankrj12 3 posts Joined 11/14
21 Feb 2015

Hi Dnoeth,

Thanks for your response..

Expected value for 2147483648 is -2147483648 , in negative INT range.

I think below should work.

SEL
CAST(?value AS BIGINT) VAL,

CAST( 2**31 AS BIGINT) INT_RANGE,

CAST(
CASE
WHEN VAL> INT_RANGE - 1
THEN
(CASE
WHEN ( VAL - 2* INT_RANGE) > INT_RANGE -1
THEN (VAL - 4* INT_RANGE)
ELSE ( VAL - 2* INT_RANGE)
END )
ELSE VAL
END AS INT
) INT_VALUE

We loaded the negative value in our staging table from Datastage where it passed negative values to an field (of CHAR type)
So we are finding difficulties in identifying those negative values passed from source.

Regards,
Kiran

You must sign in to leave a comment.