Substring in Transfomer Stage

[dsdeveloper123]

Hi all,

I have a input to the transformer something like this "700.123, 2007,06" its a comma separated value.
I want it to be mapped to 3 different column by doing a substring on it.
I used Field function
Field(Input.string,',',1)

but the output is 700 and not 700.123

Can anyone help me.

Is that I am missing out on something?

Thanks in advance!!

[chulett]

What datatype did you 'substring' into?

[dsdeveloper123]

I am taking it into a string and then doing a to_number in the insert query.

[ArndW]

What mask did you use in the to_number function and what is the SQL datatype in the database?

[dsdeveloper123]

Its a float coloumn in the database.

i used to_number(string)

[ArndW]

Sounds like the to_number() is being called correctly. Can you add an output column to a peek stage that contains the string value that you are extracting to make sure that it is actually getting "700.123" in the string. Also, could you manually insert a row using your favorite tool with to_number('700.123") into a float column to see if works as expected?

[dsdeveloper123]

Ya I did extract the string I get and its as expected. i.e "700.123,2007,1"
but when I do Field (Input.str,',',1) i get only 700 as output

[ArndW]

You are doing 2 actions - the first is the FIELD() function and the next is the insert into the database. We need to eliminate one or the other. Have you done your FIELD() function into a string variable and then outputted that value to a PEEK stage or sequential file to confirm what the extracted string is I don't think you have, but perhaps I misunderstand your post. If you have done a field("700.123,2007,1",",",1) and get "700" instead of "700.123" then you will have discovered a major bug in DataStage.

my guess is that you used a "." instead of "," in the 2nd field of the field function.