This discussion is archived
8 Replies Latest reply: Aug 7, 2012 5:04 AM by 935684 RSS

only valid dates in target

935684 Newbie
Currently Being Moderated
Hi all,

we have an interface where we are using file(.csv file) as source and database(oracle) table as target.In one of the columns in target ,the datatype is DATE.(we cant change the datatype)

we tried to populate datas into the target table from the file which contains some invalid dates .As datatype is DATE , interface is getting failed(ERROR:ODI-1228
Caused By: java.sql.SQLDataException: ORA-01847: day of month must be between 1 and last day of month).
so to enter the valid records into the target ,we tried changing the CONTROL to reject rows/null if error(inactive trace)/null if error(active trace) in that date column of that table in the MODEL.But same error is occuring.
IKM used:oracle incremental update
LKM used:LKM file to sql
CKM used:CKM oracle

we want only valid datas to enter in the target table without failing the interafce and error file should be generated for invalid dates.


Ex: the date field in file contains 12/6/2012
23/7/2012
34/5/2012
as per our requirement,the target table should contain 12/6/2012
23/7/2012
error file should contain 34/5/2012

Please let us know the solution for this problem.
  • 1. Re: only valid dates in target
    SutirthaRoy Guru
    Currently Being Moderated
    Create a function which takes input srting and returns number
    Inside the function check for date validation

    Then use that function as a constraint in ODI .
    You interface will load only valid date and invalid one will be populated in error table .
    You can generate a file from the error tale for your reference.

    Thanks,
    Sutirtha
  • 2. Re: only valid dates in target
    935684 Newbie
    Currently Being Moderated
    from your reply,i understand that you are asking to put that function as constraint on target.but the problem with that is,the data will be validated for DATE datatype first and then only the constraint will be checked after insertion.here our interface is failing,so we are not able to insert the data even which later should be checked by the constraints.
  • 3. Re: only valid dates in target
    SutirthaRoy Guru
    Currently Being Moderated
    Break the interface into 2 part.
    1st one loads a temp table which shore the date data in a varchar2 type column .
    NO validation required and data will be passed from your file to this tem table.
    2nd one loads data from temp table into your target
    In the 2nd interface , put a filter on your date column coming from source . some thing like
    IS_VALID_DATE_CHECK(TEST_SOURCE.TEST_DT)=1

    Here I am using a function

    CREATE OR REPLACE FUNCTION IS_VALID_DATE_CHECK(p_strval in VARCHAR2)
    RETURN NUMBER
    IS
    l_dateval date;
    BEGIN
    l_dateval := TO_DATE(p_strval,'dd/mm/yyyy');
    RETURN 1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
    END IS_VALID_DATE_CHECK;

    This filter will ensure that only valid dates gets passed to Target.

    After this you can do a MINUS query of Target and TEMP table to figure out the invalid dates and prepare a file for it.

    Thanks,
    Sutirtha
  • 4. Re: only valid dates in target
    935684 Newbie
    Currently Being Moderated
    Hi sutirtha,

    thanks for the reply.but when we are trying to use temporary table in the 2nd interface,we are unable to enable "use temporay table as subselect" opition.so the interface is getting failed.


    so please let us know where we are missing out something and can we create a temporary target from a file?
  • 5. Re: only valid dates in target
    SutirthaRoy Guru
    Currently Being Moderated
    thanks for the reply.but when we are trying to use temporary table in the 2nd interface,we are unable to enable "use temporay table as subselect" opition.so the interface is getting failed.
    No need to select this option. We are interested to load data into a relational table only.
    After that we are going to check for valid date and load those into final table.
    so please let us know where we are missing out something and can we create a temporary target from a file?
    Yes , you can create a yellow interface for this requirement
  • 6. Re: only valid dates in target
    935684 Newbie
    Currently Being Moderated
    we want to use a temporary table as target .we dont want to create any relational table in the database.is it possible to do ?
  • 7. Re: only valid dates in target
    SutirthaRoy Guru
    Currently Being Moderated
    You may find another approach to do the same but no in this approach .
  • 8. Re: only valid dates in target
    935684 Newbie
    Currently Being Moderated
    Please help me understand this.Are you saying that we cant create a temporary interface having file as source and temporary table(not RDBMS) as target?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points