Phantom error

Jamesvanam · Post by **Jamesvanam** » Wed Sep 15, 2004 2:29 pm

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

tonystark622 · Post by **tonystark622** » Wed Sep 15, 2004 3:06 pm

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

kcbland · Post by **kcbland** » Wed Sep 15, 2004 3:08 pm

Please post your derivation for appending the time

Jamesvanam · Post by **Jamesvanam** » Wed Sep 15, 2004 3:20 pm

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

kcbland · Post by **kcbland** » Wed Sep 15, 2004 3:23 pm

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.

Jamesvanam · Post by **Jamesvanam** » Wed Sep 15, 2004 3:36 pm

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

jseclen · Post by **jseclen** » Fri Sep 17, 2004 12:10 pm

Hi James,

Did you check the database date format???

When thats formats are different present this problems

I hope this help.

chulett · Post by **chulett** » Fri Sep 17, 2004 12:46 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 17, 2004 3:59 pm

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?

Jamesvanam · Post by **Jamesvanam** » Mon Sep 20, 2004 8:59 am

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

chulett · Post by **chulett** » Mon Sep 20, 2004 9:15 am

Hmmm... another reason to not use the Fully Generated option.

I personally stick with Column Generated.