How to convert a 32 Bit Integer Number to its negative values as same like C Concepts

Analytics

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?

Tags (1)
3 REPLIES

Re: How to convert a 32 Bit Integer Number to its negative values as same like C Concepts

Range of Integer over here : -2147483648 to 2147483647

Senior Apprentice

Re: How to convert a 32 Bit Integer Number to its negative values as same like C Concepts

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?

Re: How to convert a 32 Bit Integer Number to its negative values as same like C Concepts

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.