DSXchange

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.

##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].

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.

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).

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

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

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.

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.

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

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.

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.

Thank you Surya

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

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

source ACT_ID(NULL) Target ACT_ID(Not Null)

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.

"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.