Updating Oracle table with a date lookup

[ak77]

Hi

I am updating an ORACLE 8i table with where date = date

Incoming date field is selected in the format

Code: Select all

TO_CHAR(DATEfield,'YYYY-MM-DD HH24:MI:SS') 

Then I am using ICONV/OCONV Combo to get this date format in a stage variable

Code: Select all

Oconv(Iconv(Left(Link1.DATEfield,10), "D-YMD"), "D-DMY[2,A3,2]")

i am updating the table with user-defined query

Code: Select all

UPDATE table SET field6=:6,field7=:7 WHERE field1=:1 AND field2=:2 AND field3=:3 AND field4=:4 AND field5=TO_DATE(:5, 'DD-MON-YY')

Its showing that 19 rows got processed and finished the 10000 row limit that i set but get the warning message and aborted

Code: Select all

NumDt.JoinTables1: At row 50, link "DSLink98", while processing column "DATEfield"
Value treated as NULL
Attempt to convert String value "05-JAN-04" to Date type unsuccessful

can someone help with this?

Kishan

[chulett]

Are you using an OCI stage? Have you read the .pdf document that comes with the plug-in on how it handles dates?

There's no need for User Defined SQL just to do this. You should be declaring the target field as a Date or Timestamp datatype. You should be using the 'conv' brothers to get the date into the format expected by the stage - an ISO format:

YYYY-MM-DD if Date,
YYYY-MM-DD HH24:MI:SS if Timestamp.

The stage will automatically apply an appropriate TO_DATE mask, you just need to make sure you get your dates into the format it is expecting. After that, you'll be fine.

[chinek]

Hi

I am updating an ORACLE 8i table with where date = date

Incoming date field is selected in the format

Code: Select all

TO_CHAR(DATEfield,'YYYY-MM-DD HH24:MI:SS') 

Then I am using ICONV/OCONV Combo to get this date format in a stage variable

Code: Select all

Oconv(Iconv(Left(Link1.DATEfield,10), "D-YMD"), "D-DMY[2,A3,2]")

i am updating the table with user-defined query

Code: Select all

UPDATE table SET field6=:6,field7=:7 WHERE field1=:1 AND field2=:2 AND field3=:3 AND field4=:4 AND field5=TO_DATE(:5, 'DD-MON-YY')

Its showing that 19 rows got processed and finished the 10000 row limit that i set but get the warning message and aborted

Code: Select all

NumDt.JoinTables1: At row 50, link "DSLink98", while processing column "DATEfield"
Value treated as NULL
Attempt to convert String value "05-JAN-04" to Date type unsuccessful

can someone help with this?

Kishan

My guess is you are using the DATE as datatype, try using TIMESTAMP as your datatype or even VARCHAR since you are doing a TO_DATE function in your user defined SQL.

[ak77]

Thanks

Am sorry about all the confusion
I just left DataStage handle everything

I am selecting this date field and just using this as a condition for update

Yes, Timestamp helped and DataStage did all the TO_CHAR and TO_DATE and it was a simple thing which I complicated by using all the conversion and tryin the user-defined query

Use the meta-data definition and just follow it through out unless if you are doing any comparison or date manipulation

Just I panicked under pressure I guess

Thanks again
Kishan

[chulett]

Use the meta-data definition and just follow it through out unless if you are doing any comparison or date manipulation

And even then, most of the time. Dates in that ISO format are directly comparable without conversion to internal format.