Page 1 of 1
VARCHAR TO BIGINT
Posted: Sun Jun 10, 2012 6:42 am
by 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?
Posted: Sun Jun 10, 2012 7:45 am
by chulett
Some body is incorrect. Investigate the StringToDecimal() function.
Re: VARCHAR TO BIGINT
Posted: Sun Jun 10, 2012 11:10 pm
by srinivasudatastage
Conversion is not required for your senario.
we can't convert integer to varchar.
Re: VARCHAR TO BIGINT
Posted: Sun Jun 10, 2012 11:26 pm
by chulett
srinivasudatastage wrote: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)
Re: VARCHAR TO BIGINT
Posted: Sun Jun 10, 2012 11:43 pm
by zulfi123786
srivalli_b wrote: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.
Posted: Mon Jun 11, 2012 2:32 am
by srivalli_b
sorry, my target datatype is smallint
source value: 978972
But I am getting in the output= -4,068
Please help us
Posted: Mon Jun 11, 2012 3:02 am
by 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
Posted: Mon Jun 11, 2012 8:40 pm
by 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.