Incorrect Syntax near the keyword 'WHERE'

[Daylon_Hunt]

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;

[jklaverstijn]

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

Cheets Jan.

[ballardw]

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]

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

[LinusH]

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.

[LinusH · Accepted solution]

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.

[Daylon_Hunt]

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]

Edit: not GROUP BY - just HAVING. 😳