DSXchange

I'm Trying to Insert a value into an oracle table, my source is also oracle, but I'm performaing a logic, like changing the output date depending on the input date.
anyway while doing this, I was getting ORA error "ORA- 1856: a non-numeric character found instead where a digit was expected" so I changed the date format in transform and concatinated a time value to it. now the data is loaded into the target but DataStage spits out an error
"DataStage Job 52 Phantom 3164
Program "JOB.2000320632.DT.1340850175.TRANS1": Line 57, Nonnumeric data when numeric required. Zero used.
Program "JOB.2000320632.DT.1340850175.TRANS1": Line 57, Nonnumeric data when numeric required. Zero used.
DataStage Phantom Finished
"
So I'm not able to figure out where this error is generated.
I was thinking while concatinating the Time to the date I've added a space in between. does this matter.

Please Help
Regards
Vanam

Check all of the metadata carefully in your transformer. You can also write the same data that you're writing to Oracle to a delimited file and check it out to see if anything looks wrong.

Good Luck,
Tony

Please post your derivation for appending the time

This is my derivation.
here FINAL is a stage variable which computes to a date in the format "DD MONTH YYYY"
Oconv(Iconv(FINAL,'D/E'),'D YMD'): " " : Oconv(@TIME,'MTHS')

My guess is that this is not your problem. You have some mathematical expression that is using non-numeric data inside some column derivation in your transformer. Look for anything that is doing math and investigate.

This is the logic

FINAL = if (DSLink3.ATTRIBUTE8 < "1990/09/30") then Oconv(Iconv("07/01/1990",'D/E'),'D DMY[,A,]') Else if (

"1990/09/30" < DSLink3.ATTRIBUTE8 > "09/30/1993" ) then

(Oconv((MONTH.LAST(BETWEEN)+1),'D DMY[,A,]')) else @NULL

and
BETWEEN = Field(DSLink3.ATTRIBUTE8,'/',1) :"-": Field(DSLink3.ATTRIBUTE8,'/',2)

and the Derivation is
Oconv(Iconv(FINAL,'D/E'),'D YMD'): " " : Oconv(@TIME,'MTHS')

what I'm doing is if my input date is less than 1990/09/30 then I output 1990/07/01 or if it is between 1990/09/30 and 1993/09/03 then 1st day of the next month everything else is a NULL
and I'm using BETWEEN stage variable just to convert input date to "yyyy-mm" format
I've been trying different things but was getting new errors, spend more than 15 hrs on this date thing, can't figure it out. please help

Thanks
James

Hi James,

Did you check the database date format???

When thats formats are different present this problems

I hope this help.

OCI expects you to be using a datatype of Timestamp when dealing with DATE fields, in "YYYY-MM-DD HH24:MI:SS" format. Read the plugin documentation for the stage.

On the server machnie take a look at the code generated by compiling the Transformer stage. This can be found at RT_BP3164/JOB.2000320632.DT.1340850175.TRANS1 - what's happing in and around line 57?

Thanks for all the suggestions, I checked the fully generated query I've used, for some reason the order wasn't right, then I did a userdefined SQL query, and with the necessary timestamp format, it works fine.
but still I wonder how could that happen, the generated query did a mismatch of the order of the columns while inserting into the database table.
Regards
Jamy

Hmmm... another reason to not use the Fully Generated option. I personally stick with Column Generated.