|
Actually you could have a problem with both quotes and length of list. The
IN operator definitely requires that you have quotes around character
strings. ANSI SQL syntax requires quotes. I have seen as well instances
where the length of the IN list exceeds system limits.
In this case you are not using Oracle features in the 'pass-thru' query and
could try using the a LIBNAME with the ORACLE engine instead. That would
allow you to substitute an INNER JOIN to a table containing a column
variable containing the list of character strings. An .. a INNER JOIN .. B
WHERE a.x=b.x produces the same results as the SELECT ... FROM ... WHERE x
IN ( ...) query. You might set up a small test to see if the SAS ORACLE
engine passes thru the query to the Oracle machine, or passes the Oracle
data back to the SAS machine. If the former, the SAS ORACLE engine will
solve your problem.
Sig
-----Original Message-----
From: rpresley [mailto:rpresley@GMCF.ORG]
Sent: Tuesday, August 05, 2003 11:52 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL pass through to Oracle -- again
SAS-L,
SAS 8.2 on Windoze. Oracle 8.x on remote.
Well I am back with what appears to me to be the same question.
In response to my previous post Wei and David Parnent pointed out that I
needed to have commas between each Id in the IN list. I did this and it
worked fine. But below is what appears to me to be the same problem with
what appears to me to be a very similar SQL. In the log just above line 213
it looks like the second right parenthesis is "seen" as a period.
What is going on here? What obvious thing am I missing?
Again thanks for taking time to help.
Rodney
Rodney J. Presley, PhD
Director of Data Analysis
Georgia Medical Care Foundation
1455 Lincoln Parkway
suite 800
Atlanta, GA 30346
678-527-3474
678-527-3574 fax
rpresley@gmcf.org
This is the program:
proc sql noprint;
connect to oracle(user='rpresley'
orapw='xxxxxxxx' path="tns:c1s80_sdps"
schema="np_sdps" );
/* create table temp.benes as */ validate
select * from connection to oracle
(select bene_birth_dt, bene_srnm_name,
bene_gvn_name, bene_mdl_name, bene_clm_num
from beneficiary
where state_code='GA' and bene_clm_num IN (&benes ) )
;
quit;
%runn;
This is the log file:
201
202 proc sql noprint;
203 connect to oracle(user='rpresley'
204 orapw=XXXXXXXXXX path="tns:c1s80_sdps"
205 schema="np_sdps" );
206 /* create table temp.benes as */ validate
207 select * from connection to oracle
208 (select bene_birth_dt, bene_srnm_name,
209 bene_gvn_name, bene_mdl_name, bene_clm_num
210 from beneficiary
211 where state_code='GA' and bene_clm_num IN (&benes ) )
212 ;
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL
statement: select
bene_birth_dt, bene_srnm_name, bene_gvn_name, bene_mdl_name,
bene_clm_num from
beneficiary where state_code='GA' and bene_clm_num IN (063222187A,
070364197A,
117283480A, 199100368D, 218265058A, 223667236A, 231207182W,
233100721A, 237340450A,
242098605D, 244666078A, 248586935A, 250629478A, 252018544D,
252085284A, 252123660D6,
252156830A, 252189042A, 252189522A, 252207572A, 252276779A,
252322352A, 252362848A,
252363676A, 252364639A, 252427277D, 252460949A, 252464713D,
252525321A, 252525987D,
252526397A, 252527567TA, 252548729A, 252643110A, 252843714A,
252963549A, 253070288D,
253071004D, 253071201A, 253072298A, 253072329D, 253073261A,
253073828A, 253073870A,
253073873A, 253074008A, 253074130A, 253074167A, 253074225A,
253074517A, 253169401A,
253200095A, 253242966A, 253243418A, 253280683D, 253324918A,
253484605D, 253501934A,
253882763M, 253981250A, 253981400A, 254096920A, 254123798A,
254140301A, 254148581B,
254182681A, 254182990A, 254222657A, 254245533A, 254264512A,
254343596TA, 254346384A,
254402139A, 254420311D, 254447417A, 254456666A, 254486241A,
254486707A, 254487140A,
254489620A, 254520440A, 254528351A, 254528923A, 254540169A,
254585745A, 254647486A,
254703409A, 254728569A, 254784775A, 254885454A, 255033072A,
255186965A, 255204996A,
255205974A, 255240331A, 255245836A, 255380543A, 255384045A,
255394445A, 255422306D,
255428065A, 255505079A, 255580550A, 255581151A, 255581556A,
255608036W, 255608776A,
255622935A, 255809573A, 255927663A, 256019413A, 256055604D,
256091069A, 256096419A,
256140617A, 256145178A, 256180715A, 256182185D, 256216791T,
256222123D4, 256244310A,
256280643A, 256283209A, 256283615A, 256385110A, 256403066D1,
256425104A, 256468477A,
256601627A, 256628634A, 256643288A, 256746975A, 256786798A,
256841221A, 256869150A,
256885143A, 256968459A, 257038732A, 257104421A, 257107272A,
257148416A, 257160134A,
257166499A, 257166755C2, 257179498A, 257247159A, 257344545A,
257362586A, 257364014D,
257364298A, 257369543A, 257369812A, 257443849A, 257444992A,
257446640B6, 257466622A,
257560311A, 257661757A, 257885462A, 258096741A, 258107739D,
258107828A, 258146416A,
258158128A, 258206289A, 258209009A, 258248987A, 258327583A,
258328378A, 258404640A,
258488206A, 258489467A, 258507895C1, 258600922A, 259016788A,
259057315A, 259220015D,
259221040A, 259221661A, 259229202A, 259229306A, 259240503A,
259244668D, 259260582D,
259285316D, 259289323D, 259404614A, 259440385A, 259480731B,
259526470A, 259642826A,
259727636A, 259763389A, 259882724A, 259904286A, 259904409A,
259960847A, 260031239A,
260031591A, 260033115A, 260058654A, 260124113A, 260140528D,
260144139D, 260230984A,
260243372A, 260243976A, 260243976B, 260248597A, 260249764A,
260286021A, 260300425A,
260301525A, 260306088A, 260306533A, 260429722A, 260442058A,
260443919A, 260443960A,
260447309A, 260481001A, 260483409A, 260502693A, 260689237A,
260729445A, 260843049A,
262280319A, 262364894A, 263285370A, 263446579A, 263643430A,
264126254D, 264768498A,
264861322A, 266185171D, 267143480A, 267348201A, 267808839A,
272056536D2, 286265380A,
313444008A, 316262393A, 342055455A, 362320221A, 408444561A,
410540866A, 411162773A,
412142341A, 413102885A, 414247197A, 415133623A, 416287471A,
416448788A, 419307155A,
419400724A, 422206129A, 423185026D, 424346263A, 425364259A,
426629177A, 429074937D,
463205631A, 508429888A, 516468231A, 526182211D, 528545531A,
540202617D1, A259221032,
A908565, WA253263892, WA719142912 ).
213 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 1.78 seconds
cpu time 0.00 seconds
214 %runn;
"This code was run on 08/05/03 at 11:43 by Rodney Presley."
|
