Load Ingest Data
Page 1 of 2 12 LastLast
Results 1 to 10 of 11
Like Tree1Likes

Thread: How to: I only want to replicate data greater than a certain date...

  1. #1
    Roswell.Hall is offline Junior Member
    Join Date
    Nov 2009
    Location
    USA
    Posts
    3
    Rep Power
    0

    How to: I only want to replicate data greater than a certain date...

    Hello, all. On my source table (Oracle) I have a column of DATE datatype, and I only want to replicate the records greater than 12/31/2012 to my target SQL Server database. I've tried unsuccessfully testing with expressions, filters, etc. The documentation does not clearly spell this out.

    I hope this is something simple to accomplish.

    Thanks
    Roswell Hall
    kiaralaurent likes this.

  2. #2
    JeffInFlorida's Avatar
    JeffInFlorida is offline US Technical Team
    Join Date
    Oct 2006
    Posts
    95
    Rep Power
    13

    Filtering by date for Oracle source

    You can use an expression in the filter.

    Example: $DATE1 > strftime('2012-12-31')

    Where $DATE1 is the name of the input column.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Roswell.Hall is offline Junior Member
    Join Date
    Nov 2009
    Location
    USA
    Posts
    3
    Rep Power
    0

    Filtering by date for Oracle source

    Jeff, this worked like a champ!

    Thanks so much
    Roswell

  4. #4
    Roswell.Hall is offline Junior Member
    Join Date
    Nov 2009
    Location
    USA
    Posts
    3
    Rep Power
    0

    Follow up question...

    Jeff, as mentioned previously I was able to create the expression and it works perfectly. Is it possible to apply this expression to all tables, much like the "global transformation" can be reused for all tables?

    Basically, the expression I created is for one table to replicate data created/added after a certain date. I want to use this expression on all tables since all source tables have this same column (ADDDTTM). Is this possible?

    Thanks again
    Roswell

  5. #5
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Roswell

    Applyiing a filter for all tables ("Global filter") is currently not supported but, it is planned to be supported in next version that will be available latter this year.


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  6. #6
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    159
    Rep Power
    12

    Perl script to add the same column based filter to all selected table in tasks

    While not a global manipulation as such, the script in the link below can be used to add the same manipulation to all (selected) tables in all (selected) tasks. It was provided to the topic creator back in February.

    Hein

    http://www.attunity.com/forums/repli....html#post5164

  7. #7
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    Current time with fraction of seconds


    strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')

  8. #8
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6

    Transformations - Default minimum date

    Default minimum date


    It is useful with SQL Server datetime datatype that has a minimum date of 1753-01-01:


    CASE WHEN STRFTIME('%Y', $HIRE_DATE) < '1900' THEN DATE('1900-01-01')
    ELSE $HIRE_DATE END

  9. #9
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6

    Transformations - Checking for invalid year

    Checking for invalid year


    case strftime('%Y', $DECISION_DATE) when '0000'
    then replace($DECISION_DATE,'0000','0001') else $DECISION_DATE end

  10. #10
    tharunkumary is offline Junior Member
    Join Date
    Aug 2018
    Posts
    2
    Rep Power
    0
    Any update on how to select rows from particular time with milliseconds as in Global transformations?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •