Skip to Content
avatar image
Former Member

Data Services Transform SQL Where Clause

I am more familiar with SQL but need to write a transform that gets records from a Order table where EventDate > @g_maxdate. the CustomerNumber is not unique so I would like to get all the records for that customer where any of their Order.EventDates have changed. The embedded SQL statement would look like this. SELECT * from Orders where CustomerNumber in(select CustomerNumberfrom Orders where EventDate >'2017-11-22').

How can I accomplish this in Data Services Data Flow Transform?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 23, 2017 at 02:16 PM

    Patty,

    You can simply declare a variable in a script called $G_MAXDATE and initialize it to as follows

    $G_MAXDATE=sql('DS_NAME','select max(EVENTDATE) from TARGET_TABLE');

    Create a data flow and use ORDERS as Source table. Now drag and drop a Query transform and map the necessary columns from Orders table in to the Outpu Schema.

    In the where clause include Orders.EVENTDATE > $G_MAXDATE

    You can place a Table Comparison Transform after the query transform to handle upserts(update/insert) where it will compare the Source input with the Target data and load the data depending on the operation(Insert/Update)

    In Table Comparison transform you would use CustomerNumber as Input Column and in Compare Columns you can place the columns which you think could change.

    Everytime you run the job it will check the MAX of EVENDATE and will only bring the records after this date. You might set the CustomerNumber as Primary Key in your target table.

    Below should be the simplest Data Flow

    Script>Data Flow(Source>Query>Table_Comparison>Target)

    Regards

    Arun Sasi

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 23, 2017 at 12:55 PM

    use----

    where EventDate > [$g_maxdate]

    Add comment
    10|10000 characters needed characters exceeded