BookmarkSubscribeRSS Feed
Dreamer
Obsidian | Level 7

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

9 REPLIES 9
Hima
Obsidian | Level 7

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;

Dreamer
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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
ballardw
Super User

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.

jakarman
Barite | Level 11

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 --<-----
LinusH
Tourmaline | Level 20

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
jakarman
Barite | Level 11

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 --<-----
Dreamer
Obsidian | Level 7

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

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2751 views
  • 3 likes
  • 5 in conversation