Forum Stats

  • 3,768,258 Users
  • 2,252,769 Discussions
  • 7,874,506 Comments

Discussions

How to change and convert datatype in ODI for specific field before loading in target table

user12251389
user12251389 Member Posts: 322 Blue Ribbon
edited Oct 11, 2021 12:12PM in Data Integrator

Hallo All,

I am using ODI 12c and quiet new with this tool. I want to truncate and reload the target table from source but getting error as ORA-01861: literal does not match format string.

In my source table SRC_TBL i have column modtime which is of Varchar datatype. And the value in this field is something like '2011-02-25-08:47:43,731984'.

Before i load the data into target table TRG_TBL, i want to convert this modtime field to date and use below condition such that the value store in the TRG_TBL for field modtime will be '2011-02-25 08:47:43'

to_date(SUBSTR(modtime, 1, 19), 'YYYY-MM-DD-HH24:MI:SS') AS modtime

Can someone please help how to change this datatype in ODI and use above condition for field modtime. I dont want to use option CUSTOME_TEMPLATE in ODI where we have to write select query and override the logic of loading.


Below is my simple mapping:

Tagged:

Best Answer

  • Marco Fris
    Marco Fris Member Posts: 128 Bronze Badge
    Accepted Answer

    You can simply paste your expression (without the AS modtime) in the target field. Then open the expression editor and replace the 'modtime' with the correct attribute from the left pane from the expression editor.

Answers

  • Marco Fris
    Marco Fris Member Posts: 128 Bronze Badge
    Accepted Answer

    You can simply paste your expression (without the AS modtime) in the target field. Then open the expression editor and replace the 'modtime' with the correct attribute from the left pane from the expression editor.