Date: Tue, 5 Aug 2003 13:10:42 -0400
Reply-To: "Parent, David" <david.parent@CAPITALONE.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Parent, David" <david.parent@CAPITALONE.COM>
Subject: Re: SQL pass through to Oracle -- again
Content-Type: text/plain
Hi Rodney,
This list contains character data and therefore must have values enclosed in
single quotes in Oracle. Adding the quotes should eliminate the "right
parenthesis" error.
David Parent
-----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."
**************************************************************************
The information transmitted herewith is sensitive information intended only
for use by the individual or entity to which it is addressed. If the reader
of this message is not the intended recipient, you are hereby notified that
any review, retransmission, dissemination, distribution, copying or other
use of, or taking of any action in reliance upon this information is
strictly prohibited. If you have received this communication in error,
please contact the sender and delete the material from your computer.
| 
