Hi
I am trying to pull data from Sybase using pass through query via ODBC.
Following is the code that I'm trying:
Proc Sql;
Connect to odbc as mycon (datasrc=XXXX user=XXXX password=XXXXX);
select * from connection to mycon
(SELECT DISTINCT
MEME.MEME_LAST_NAME,
MEME.MEME_FIRST_NAME,
(SELECT MAX(MEPE.CSCS_ID)
FROM CMC_MEPE_PRCS_ELIG as MEPE
WHERE MEME.MEME_CK = MEPE.MEME_CK ) as ClassID
FROM CMC_MEME_MEMBER as MEME
WHERE MEME.MEME_REL <> 'O'
);
disconnect from mycon;
quit;
But I'm getting following error:
ERROR: CLI describe error: [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near '2'.
I'll not get error if I remove inner "WHERE" clause highlighted in the code. But doesn't matter what condition I give there, I'll get the same error.
But I need that where clause to filter records for processing.
Do we have any solution to avoid this error?
Thanks
Hi Dreamer,
can you give this a try?
proc sql;
connect to odbc as mycon (datasrc=xxxx user=xxxx password=xxxxx);
select * from connection to mycon
(select distinct
meme.meme_last_name,
meme.meme_first_name,
max(mepe.cscs_id) as classid
from cmc_meme_member as meme, meme_ck as mepe
where meme.meme_ck = mepe.meme_ck and meme.meme_rel <> 'O'
group by meme.meme_ck);
disconnect from mycon;
quit;
Thanks Himi but I didn't get expected result and it took a long time.
Btw my original sql executes perfectly on other sql tools like Winsql and even on excel using odbc connection. This means SQL is perfect for ODBC.
Also it runs perfectly in SAS EG if I remove the inner where clause.
I'm not sure if I'm missing anything here!!
Am I missing something here? Isn't this a Sybase SQL issue, not a SAS SQL pass-thru issue? SAS sends the entire query to the source DBMS for execution, I'm having a hard time believe that SAS will infect this code different than other SQL tools.
Does your "original SQL" includes the troublesome sub-query?
I would try instead of MEME.MEME_CK in the subquery the full name CMC_MEME_MEMBER.Meme_CK in case there is some timing issue with where/when the allias MEME is valid in the pass through.
use the sastrace option to debug what is happening. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition
SAS is using ANSI-Sql the esparanto for RDBMS systems, none of them is the same all are using dialects.
You are using the ODDC driver SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition Is this one is running on your server side? Than the odbc driver at hat side could have issues not being the same as on your desktop.
I was searching for some differences with using an execute statement sql explicit pass through and your approach. They are sometimes behaving different, could not get my fingers on that.
SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition
SASTRACE won't probably get you any more info when using explicit pass-thru: what you type is what you get...
Yep you are correct explicit pass through should not get any inference by SAS. Seen this with TD is not exactly the same. The execute interface (link in updated post) did behave different.
The chunks send to the RDBMS are better in control needing the commits cleanups or whatever. Several other options in SAS did some automation on that. Automation on some things you do not control is an contradiction.
As said I couldn't get my finger why those differences were there.
I totally forgot to update this thread.
I didn't get any good solution for this problem. But I did a work around.
I took out all the nested SQLs. Created temporary tables from results of those queries and then merged those temporary tables to get all variables in my final result.
I know this is not a good solution but I couldn't get any better and only good thing here is that it worked for me. I got output what I wanted!!
Dreamer, Now you are mentioning those nested queries and temporary results. How big are those intermediate tables. Sometimes limits have been set (like max 10.000 rows) causing unpredictabel result and errors. The best thing, you have your results.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: