Max Date in Extract Transformation

[Daylon_Hunt]

In DI Studio, I'm working in an Extract Transformation and trying to insert a WHERE clause that will load only the most recent date into the Target table.  The Numeric field is called "DWDATE", and the format is "yyyymmdd".  I currently use the same logic for a table in SQL Management Studio, and the statement is:  

FROM From.Dmi_Cif_Daily
WHERE DWDATE=(SELECT MAX(DWDATE)FROM DMI_CIF_Daily)

I'm trying to replicate this in the Extract Transformation in a job in DI Studio.  Please help!

[Reeza]

Try:

where

dwdate=max(dwdate)

[SASKiwi]

I don't use DI Studio but it must be able to do an inner join like this:

select A.*
FROM Dmi_Cif_Daily as A
inner join
(SELECT MAX(DWDATE) as dwdate_max 
 FROM DMI_CIF_Daily
) as B
on A.DWDATE=B.dwdate_max 

[LinusH]

First, if you wish to filter out records based on an aggregate function applied on the same table, use a having clause.
Second, <having> is not supported by the extract transformation, so you need to use the Join transformation instead.

[Daylon_Hunt]

I'm a little confused as to how and why I would incorporate the Join Transformation if I'm not joining together multiple tables.  I'm going from an Extract transformation to a Loader.  

[LinusH]

Addition : if you still like the sub query better, that is also supported in the Join transformation, perhaps not as intuitively as one could wish for, but it's there.

[LinusH · Accepted solution]

DI Studio is a great tool, but not perfect. I think that the Join transformation was named to distinguish it from the extract. But in fact, it's a query builder. Joining is not required in the Join transformation.
Again, if you wish to use metadata, and have need for Having or sub query, Join is the transformation that does the job.

[Daylon_Hunt]

This definitely took some time for me to engineer my first time through, but in the end I was able to build the Having function into the Join transformation.  Thank you LinusH!

[smilbuta]

Hello,

Daylon, can you share how you did this. I need to accomplish the same task in a New job we are creating and this is throwing me for a loop.