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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.