VARCHAR TO BIGINT

[srivalli_b]

HI,

MY JOB :

TDSTAGE -----> XFM---> TDSTAGE

MY SOUCE COLUMN DATATYPE IS VARCHAR
TARGET COLUMN DATATYPE IS BIGINT

I went through dsxchange forum. some body is telling that no conversion is required.But in my case it is not working.

I want to change the target datatype in datastage level.

Is it right way?

[chulett]

Some body is incorrect. Investigate the StringToDecimal() function.

[srinivasudatastage]

Conversion is not required for your senario.

we can't convert integer to varchar.

[chulett]

Conversion is not required for your senario.

we can't convert integer to varchar.

a) yes it is
b) yes you can


ps. they're going the other way (varchar to bigint)

[zulfi123786]

TDSTAGE -----> XFM---> TDSTAGE

MY SOUCE COLUMN DATATYPE IS VARCHAR
TARGET COLUMN DATATYPE IS BIGINT

use the derivation

Code: Select all

If IsValid("int64",Trim(input.column)) Then Trim(input.column) Else SetNull()

Assuming the invalids should be set to Null.

[srivalli_b]

sorry, my target datatype is smallint

source value: 978972
But I am getting in the output= -4,068
Please help us

[zulfi123786]

Your Source value is too big to fit in smallint. If you have to store the value then change the data type to integer

[ray.wurlod]

You are getting the "correct" value as far as the computer is concerned. What you are seeing is an example of the condition called arithmetic overflow, where the unsigned number is large enough to turn on the most significant bit, causing the signed number to be represented as negative.

A quick fix for some cases would be to change the target data type to uint16 rather than int16. However 978972 is too big even for uint16. You need at least int32 to store this string as an integer data type.