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

I have a job that is erroring out at the very end, and getting an error at the keyword "WHERE".  It's using a Subquery to extract the most recent row processing date, and using that date as a filter for bringing in the source data.  Below is the code that is erroring out:

 


from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily,
(
select
MAX(V_DMI_CIF_Daily_sub.DWDATE_Int ) as Max_Date
from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily_sub
)
where
V_DMI_CIF_Daily.DWDATE_Int = Max_Date

);

%rcSet(&sqlrc);

disconnect from ODBC;
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
Ok so it's Join transformations with explicit pass thru. Looks like it's generated code, right?
Even so, it's possible to generate non syntactically correct SQL. Try the pass thru part in SQL Server Management Studio.

Why are you using explicit pass thru?
Looks like your query could easily be restructured from the in line view join to group by-having. By use that simple syntax chances are pretty high that it will be passed thru implicitly and that would hopefully solve your syntax problem.
Data never sleeps

View solution in original post

7 REPLIES 7
jklaverstijn
Rhodochrosite | Level 12

Would you like to share the log with the complete code and error massages? That wouldgreatly help others Help you.

 

Cheets Jan.

ballardw
Super User

In your code

from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily, 
(
select
MAX(V_DMI_CIF_Daily_sub.DWDATE_Int ) as Max_Date
from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily_sub
)
where
V_DMI_CIF_Daily.DWDATE_Int = Max_Date

);


From what you post the last ) does not have a matching ( .

 

 

The log is always a good idea as it is often pretty good about showing where issues are.

Daylon_Hunt
Obsidian | Level 7

Attached is the log, and the error is occurring at row 1320

LinusH
Tourmaline | Level 20

The "disconnect from ODBC" tells me that the problem i s probably related to some syntax within your (guessing here) ...from connection to-block. So technically, it may not be a SAS issue.

 

Data never sleeps
LinusH
Tourmaline | Level 20
Ok so it's Join transformations with explicit pass thru. Looks like it's generated code, right?
Even so, it's possible to generate non syntactically correct SQL. Try the pass thru part in SQL Server Management Studio.

Why are you using explicit pass thru?
Looks like your query could easily be restructured from the in line view join to group by-having. By use that simple syntax chances are pretty high that it will be passed thru implicitly and that would hopefully solve your syntax problem.
Data never sleeps
Daylon_Hunt
Obsidian | Level 7
Using the "Having" instead of "Where" appears to have done the trick. Although, it wouldn't work without using the "Group By" too. I still feel like I'm not going about this particular job in the most efficient way, I'm learning a lot as I go.
LinusH
Tourmaline | Level 20
Edit: not GROUP BY - just HAVING. 😳
Data never sleeps

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
  • 7 replies
  • 8272 views
  • 1 like
  • 4 in conversation