Help using Base SAS procedures

Error in Pass through query (ODBC)

Reply
Frequent Contributor
Posts: 89

Error in Pass through query (ODBC)

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

Regular Contributor
Posts: 233

Re: Error in Pass through query (ODBC)

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;

Frequent Contributor
Posts: 89

Re: Error in Pass through query (ODBC)

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!!

Super User
Posts: 5,256

Re: Error in Pass through query (ODBC)

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?

Data never sleeps
Super User
Posts: 10,500

Re: Error in Pass through query (ODBC)

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.

Valued Guide
Posts: 3,208

Re: Error in Pass through query (ODBC)

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

---->-- ja karman --<-----
Super User
Posts: 5,256

Re: Error in Pass through query (ODBC)

SASTRACE won't probably get you any more info when using explicit pass-thru: what you type is what you get...Smiley Wink

Data never sleeps
Valued Guide
Posts: 3,208

Re: Error in Pass through query (ODBC)

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.

---->-- ja karman --<-----
Frequent Contributor
Posts: 89

Re: Error in Pass through query (ODBC)

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!! Smiley Happy

Valued Guide
Posts: 3,208

Re: Error in Pass through query (ODBC)

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.    

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 9 replies
  • 874 views
  • 3 likes
  • 5 in conversation