SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Max Date in Extract Transformation

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Max Date in Extract Transformation

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!


Accepted Solutions
Solution
‎03-10-2016 12:22 PM
Super User
Posts: 5,256

Re: Max Date in Extract Transformation

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.
Data never sleeps

View solution in original post


All Replies
Super User
Posts: 17,828

Re: Max Date in Extract Transformation

Try:

where

dwdate=max(dwdate)

Super User
Posts: 3,106

Re: Max Date in Extract Transformation

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 
Super User
Posts: 5,256

Re: Max Date in Extract Transformation

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.
Data never sleeps
Contributor
Posts: 20

Re: Max Date in Extract Transformation

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.  

Super User
Posts: 5,256

Re: Max Date in Extract Transformation

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.
Data never sleeps
Solution
‎03-10-2016 12:22 PM
Super User
Posts: 5,256

Re: Max Date in Extract Transformation

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.
Data never sleeps
Contributor
Posts: 20

Re: Max Date in Extract Transformation

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 579 views
  • 1 like
  • 4 in conversation