Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-10-2015
12:13 PM

02-10-2015
12:13 PM

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 - 2^{31 }to 2^{31}-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?

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-10-2015
12:17 PM

02-10-2015
12:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-14-2015
04:03 AM

02-14-2015
04:03 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-21-2015
10:28 AM

02-21-2015
10:28 AM

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.

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.