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!
Try:
where
dwdate=max(dwdate)
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
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.
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.