BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daylon_Hunt
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
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

8 REPLIES 8
Reeza
Super User

Try:

where

dwdate=max(dwdate)

SASKiwi
PROC Star

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
Tourmaline | Level 20
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
Daylon_Hunt
Obsidian | Level 7

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
Tourmaline | Level 20
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
LinusH
Tourmaline | Level 20
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
Daylon_Hunt
Obsidian | Level 7

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2532 views
  • 1 like
  • 5 in conversation