Issue with Left(%string%,%length%)

[owen3]

When I apply Left(DSLink2.src_surrogate_comp,19) to
00012109614152087431682301311722200502
00012109614155689601682301311722200502
it gives
1210961415208743
1210961415568960 which is expected.
But when i apply for
99999999999999999999999999999999900001
99999999999999999999999999999999900002
it gives
9223372036854775807
9223372036854775807
I need to do for Right(DSLink2.src_surrogate_comp,19) and I am getting same output.
Anybody has any idea.........whats going on? and how to debug this.

Thanks in advance.

[DSguru2B]

What is your target datatype set as?

[owen3]

[quote="DSguru2B"]What is your target datatype set as?[/quote]

Source is flat file with NVarChar, length=38 and target Netezza with SQL type =BigInt and length=19

[DSguru2B]

I doubt big int can hold that number. Its way above its limit. Check what the upper integer limit is for BigInt.

[owen3]

[quote="DSguru2B"]I doubt big int can hold that number. Its way above its limit. Check what the upper integer limit is for BigInt.[/quote]

But it works with
00012109614152087431682301311722200502
00012109614155689601682301311722200502
as an input

[DSguru2B]

There is a huge difference between a number that starts with 12 and a number that starts with 99.
See how much is 2^63. I bet its what you are getting.

[OddJob]

A BigInt type is a 64 bit number, signed. 2^64 is 18446744073709551616, signed is +-9223372036854775807.

This value is less than 9999999999999999999 that you're trying to hold, sorry!

If you don't need it to be signed, set the extended properties to unsigned and you'll get 20 digits to play with.

[owen3]

[quote="DSguru2B"]There is a huge difference between a number that starts with 12 and a number that starts with 99.
See how much is 2^63. I bet its what you are getting. ...[/quote]

Thanks. I changed SQL type from BigInt to Varchar and I'm getting the required result. Thanks again for your help. I will mark it as resolved.