String to Number Convertion ( User defined SQL ) problem

sreddy · Post by **sreddy** » Fri Feb 12, 2010 9:41 am

Hi

I write a user defined query in Oracle EE stage to read columns for look up. In that SQL i used TO_NUMBER for one of column to covert string to Number. Because the target column data type is Number.

I am getting error while i am reading.

##I TFCN 000001 10:24:40(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5.1A
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved

##I TUTL 000031 10:24:40(001) <main_program> The open files limit is 8192; raising to.
##I TOSH 000002 10:24:41(000) <main_program> orchgeneral: loaded
##I TOSH 000002 10:24:41(001) <main_program> orchsort: loaded
##I TOSH 000002 10:24:41(002) <main_program> orchstats: loaded
##I TFSC 000001 10:24:41(003) <main_program> APT configuration file: /usr/local/etl/default.apt
##W TCOS 000049 10:24:42(000) <main_program> Parameter specified but not used in flow: DSProjectMapName
>##E TFIP 000000 10:24:43(001) <Oracle_Enterprise_9> Error when checking operator: When binding output interface field "ACT_ID" to field "ACT_ID": Implicit conversion from source type "decimal[38,10]" to result type "string[12]": Not enough room in string for decimal[38,10].
>##E TFSR 000019 10:24:43(002) <main_program> Could not check all operators because of previous error(s)
##W TFIP 000022 10:24:43(003) <Oracle_Enterprise_9> Error when checking operator: When binding output interface field "ACT_ID" to field "ACT_ID": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted.
>##E TCOS 000029 10:24:43(004) <main_program> Creation of a step finished with status = FAILED.
>

Please help what is wrong.

chulett · Post by **chulett** » Fri Feb 12, 2010 9:48 am

##E TFIP 000000 10:24:43(001) <Oracle_Enterprise_9> Error when checking operator: When binding output interface field "ACT_ID" to field "ACT_ID": Implicit conversion from source type "decimal[38,10]" to result type "string[12]": Not enough room in string for decimal[38,10].

sreddy · Post by **sreddy** » Fri Feb 12, 2010 9:58 am

Thank you Hullett

I know you can understand well, But my source table Column is like this

ACT_ID --- Char(12)

That is why i write like this.

TO_NUMBER(Table.ACT_ID) ACT_ID

When i run the query on TOAD it is working. When i remove TO_NUMBER
i can view the data.

Please explain me more, That make me better.

ArndW · Post by **ArndW** » Fri Feb 12, 2010 10:03 am

In TOAD you aren't limiting the display output, but there is no way that a valid DECIMAL(38,10) number such as '12345678901234567890' will fit into a VarChar(12).

sreddy · Post by **sreddy** » Fri Feb 12, 2010 10:09 am

Thank you Arnd

It means, instread of write user defined SQL convertion function. Can i covert at Transformer stage.

I am reading Oracle Stage to read the Table for Lookup.

Please explain

ArndW · Post by **ArndW** » Fri Feb 12, 2010 10:19 am

Although I'm not sure what you are asking, why don't you use a VarChar(64) column which is more than sufficiently large.

kbsuryadev · Post by **kbsuryadev** » Fri Feb 12, 2010 10:37 am

try using Varchar in the source, it looks like you specified your source as decimal and target is varchar.... try changing the source to varchar and target as varchar.

sreddy · Post by **sreddy** » Fri Feb 12, 2010 11:15 am

Thank you Surya

I am using Surce as Char (12 ) i check in my source table column.

When i follow Arnd's message using Varchar(64) source column. I can view the data, but ACT_ID data is all zeros. Rest of the columns are good.

priyadarshikunal · Post by **priyadarshikunal** » Fri Feb 12, 2010 11:56 am

what i am not sure is if you need data in varchar column why you are converting it to number?

sreddy · Post by **sreddy** » Fri Feb 12, 2010 12:28 pm

Thank you Priyadarshi Kunal

I need data in Number that is why i am converting from String to Number.

please follow the history messages and help me.

kbsuryadev · Post by **kbsuryadev** » Fri Feb 12, 2010 12:35 pm

It seems you have decimal(38,10) in Datastage though your
source is char(12)

also you have a null in the source but your target appers to be NOT NULL , so you have to make it null
check datatypes you defined in the source stage and target stage in datastage.

sreddy · Post by **sreddy** » Fri Feb 12, 2010 12:46 pm

Thank you Surya

My source and Target, both are NOT NULL. I have to investigate the problem.

kbsuryadev · Post by **kbsuryadev** » Fri Feb 12, 2010 1:00 pm

error says you have a null in the source. and target is not nullable.

source ACT_ID(NULL) Target ACT_ID(Not Null)

sreddy · Post by **sreddy** » Fri Feb 12, 2010 2:38 pm

Thank you Surya

But when i am remove that TO_NUMBER from SQL. I can view the data.
double check my source & target table. It show Nullable as NO.

Thanks for spending time for me.

kbsuryadev · Post by **kbsuryadev** » Fri Feb 12, 2010 3:21 pm

"ACT_ID" to field "ACT_ID": Implicit conversion from source type "decimal[38,10]" to result type "string[12]": Not enough room in string for decimal[38,10].
>##E TFSR 000019 10:24:43(002) <main_program> Could not check all operators because of previous error(s)
##W TFIP 000022 10:24:43(003) <Oracle_Enterprise_9> Error when checking operator: When binding output interface field "ACT_ID" to field "ACT_ID": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted. >##E TCOS 000029 10:24:43(004) <main_program> Creation of a step finished with status = FAILED.

If you read the above errors which are in BOLD .... then you will have an idea

If it is resolved then mark it as RESOLVED.