8 Replies Latest reply on Aug 7, 2012 12:04 PM by 935684

    only valid dates in target

    935684
      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
          Sutirtha Roy
          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
            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
              Sutirtha Roy
              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
                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
                  Sutirtha Roy
                  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
                    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
                      Sutirtha Roy
                      You may find another approach to do the same but no in this approach .
                      • 8. Re: only valid dates in target
                        935684
                        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?