Data Services Transform SQL Where Clause

[Former Member]

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?

[former_member198401]

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

[former_member208402]

use----

where EventDate > [$g_maxdate]