SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Incorrect Syntax near the keyword 'WHERE'

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Incorrect Syntax near the keyword 'WHERE'

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;


Accepted Solutions
Solution
‎04-11-2016 04:19 PM
Super User
Posts: 5,430

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt
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


All Replies
Super Contributor
Posts: 441

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt

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

 

Cheets Jan.

Super User
Posts: 11,343

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt

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.

Contributor
Posts: 20

Re: Incorrect Syntax near the keyword 'WHERE'

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

Super User
Posts: 5,430

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt

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
Solution
‎04-11-2016 04:19 PM
Super User
Posts: 5,430

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt
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
Contributor
Posts: 20

Re: Incorrect Syntax near the keyword 'WHERE'

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.
Super User
Posts: 5,430

Re: Incorrect Syntax near the keyword 'WHERE'

Posted in reply to Daylon_Hunt
Edit: not GROUP BY - just HAVING. 😳
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 758 views
  • 1 like
  • 4 in conversation