Skip to Content

Data Services Transform SQL Where Clause

Nov 22, 2017 at 05:42 PM


avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Arun Sasi Nov 23, 2017 at 02:16 PM


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)


Arun Sasi

10 |10000 characters needed characters left characters exceeded
Ravi kiran Pagidi Nov 23, 2017 at 12:55 PM


where EventDate > [$g_maxdate]

10 |10000 characters needed characters left characters exceeded