topic Re: Error in Pass through query (ODBC) in SAS Procedures

Error in Pass through query (ODBC)

Dreamer — Mon, 08 Sep 2014 20:34:51 GMT

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

Re: Error in Pass through query (ODBC)

Hima — Mon, 08 Sep 2014 21:01:17 GMT

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;

Re: Error in Pass through query (ODBC)

Dreamer — Mon, 08 Sep 2014 21:32:56 GMT

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

Re: Error in Pass through query (ODBC)

ballardw — Mon, 08 Sep 2014 22:17:09 GMT

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.

Re: Error in Pass through query (ODBC)

LinusH — Tue, 09 Sep 2014 07:02:33 GMT

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?

Re: Error in Pass through query (ODBC)

jakarman — Tue, 09 Sep 2014 08:22:50 GMT

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

Re: Error in Pass through query (ODBC)

LinusH — Tue, 09 Sep 2014 11:39:35 GMT

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

Re: Error in Pass through query (ODBC)

jakarman — Tue, 09 Sep 2014 12:00:16 GMT

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.

Re: Error in Pass through query (ODBC)

Dreamer — Fri, 31 Oct 2014 18:28:57 GMT

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

Re: Error in Pass through query (ODBC)

jakarman — Fri, 31 Oct 2014 18:49:21 GMT

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.