Date: Tue, 5 Aug 2003 05:02:40 -0700
Reply-To: Wei <wei112@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Wei <wei112@HOTMAIL.COM>
Organization: http://groups.google.com/
Subject: Re: Pass thorugh SQL to Oracle
Content-Type: text/plain; charset=ISO-8859-1
You need comma between each number in &id. If your &id is generated by
PROC SQL, you need to add
separated by ","
If you list is generated by %let, you have to add commas one by one.
Wei
david.parent@CAPITALONE.COM (Parent, David) wrote in message news:<D5170EA5CC08D5119F160002A52CBD1F1035894C@kdcnt4mbx06.kdc.capitalone.com>...
> Hi Rodney,
>
> Not sure if this is it, but when I submit to Oracle, I receive the
> "ORA-00907: missing right parenthesis" error when my in list is not
> delimited by commas. I would try to inserting commas between values in your
> oracle in list.
>
> David Parent
>
> -----Original Message-----
> From: rpresley [mailto:rpresley@GMCF.ORG]
> Sent: Monday, August 04, 2003 5:14 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Pass thorugh SQL to Oracle
>
> Sas-L,
>
> SAS 8.2 on Windoze. Oracle 8.x on remote.
>
> This is the program:
>
> proc sql;
> connect to oracle(user='rpresley'
> orapw='r0a9b18e' path="tns:c1s80_sdps"
> schema="np_sdps" );
> /*create table temp.hse as */ validate
> select * from connection to oracle
> (select state_code, to_char(hse_unique_id) as hse_unique_id,
> bene_clm_num, hse_clm_from_dt, hse_clm_thru_dt,
> hse_inpat_outpat_ind,
> hse_clm_admsn_dt, hse_bene_age, hse_clm_mdcl_rec_num,
> bene_sex_ident_cd,
> hsp_id
> from hse
> where state_code='GA' and hsp_id='110002'
> and hse_inpat_outpat_ind='I'
> and hse_unique_id in (&id ) )
> ;
> quit;
>
> This is the log:
>
> 683 proc sql;
> 684 connect to oracle(user='rpresley'
> 685 orapw=XXXXXXXXXX path="tns:c1s80_sdps"
> 686 schema="np_sdps" );
> 687 /*create table temp.hse as */ validate
> 688 select * from connection to oracle
> 689 (select state_code, to_char(hse_unique_id) as hse_unique_id,
> 690 bene_clm_num, hse_clm_from_dt, hse_clm_thru_dt,
> hse_inpat_outpat_ind,
> 691 hse_clm_admsn_dt, hse_bene_age, hse_clm_mdcl_rec_num,
> bene_sex_ident_cd,
>
> 692 hsp_id
> 693 from hse
> 694 where state_code='GA' and hsp_id='110002'
> 695 and hse_inpat_outpat_ind='I'
> 696 and hse_unique_id in (&id ) )
> 697 ;
> ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL
> statement: select
> state_code, to_char(hse_unique_id) as hse_unique_id, bene_clm_num,
> hse_clm_from_dt,
> hse_clm_thru_dt, hse_inpat_outpat_ind, hse_clm_admsn_dt,
> hse_bene_age,
> hse_clm_mdcl_rec_num, bene_sex_ident_cd, hsp_id from hse where
> state_code='GA' and
> hsp_id='110002' and hse_inpat_outpat_ind='I' and hse_unique_id in
> (199905280001618434
> 199906250000447626 199906250000447626 199908270001178461
> 199909240001064754
> 199909240001225491 199910080000966318 199910220001358542
> 199911190001220053
> 199912100002200369 199912170003076418 199912170003077715
> 199912240003627478
> 199912240003627591 200001070001760902 200001070001763013
> 200001070001767536
> 200001070001772973 200001070001785243 200001070001902561
> 200001070002020093
> 200001140001762766 200001140001778364 200001210001778333
> 200001210001778362
> 200001280001770939 200001280002229248 200002040003350889
> 200002040003356297
> 200002110003154372 200002110003343624 200002110003364063
> 200002110003384585
> 200002180003996551 200002250003345276 200002250003345952
> 200002250003352810
> 200002250003364064 200003030004559119 200003030004562280
> 200003100004568150
> 200003170004568338 200003240004561494 200003240004725679
> 200003310004557657
> 200003310004574766 200003310004578498 200004070005784005
> 200004070005830436
> 200004140000036079 200004140000036079 200004140000036079
> 200004140005835846
> 200004140005847459 200004210005824515 200004210005836049
> 200004210005838412
> 200004210005847163 200004210006414865 200004210006478690
> 200004280005830150
> 200005050006946756 200005050006951233 200005050006955674
> 200005050006956888
> 200005050006958623 200005050006963637 200005050006966745
> 200005120006966931
> 200005120006967345 200005120007215212 200005190006946757
> 200005190006956717
> 200005260006957650 200006160008112426 200006160008120846
> 200006160008144888
> 200006230008107619 200006230008121648 200006230008381550
> 200006300008106006
> 200006300008112799 200006300008112799 200006300008120115
> 200006300008120735
> 200006300008121246 200006300008769631 200007070000655719
> 200007140001115555
> 200007210001203051 200007210001203769 200007210001207996
> 200007280001511946
> 200008040000651371 200008180001185887 200008250001507486
> 200008250001510850
> 200009010000748921 200009010000750286 200009010000751453
> 200009010000753791
> 200009010000753868 200009080001009258 200009080001010911
> 200009150001277305
> 200009150001278081 200009220001559726 200009220001563536
> 200009220001563961
> 200009290001877439 200009290001877871 200009290001879917
> 200009290001879982
> 200009290001880213 200009290001881272 200010060000037750
> 200010060000760861
> 200010060000764105 200010060000765063 200010200001368269
> 200010200001369829
> 200010200001374403 200010270001683398 200010270001683466
> 200010270001684782
> 200010270001686759 200010270001687405 200011030000695954
> 200011100000933799
> 200011100000937077 200011100000938916 200011100000939646
> 200011100000939902
> 200011170001212392 200011170001213163 200011240001547941
> 200012010000831657
> 200012010000833812 200012010000834874 200012010000835552
> 200012010000835961
> 200012010000837763 200012010000837857 200012010000839357
> 200012150001376876
> 200012150001377197 200012150001378166 200012150001379668
> 200012150001379722
> 200012220001640613 200012220001648675 200101050000569328
> 200101050000570707
> 200101050000570708 200101050000571643 200101050000571663
> 200101050000571923
> 200101050000571924 200101050000572113 200101050000574334
> 200101050000575241
> 200101050000575441 200101050000575905 200101120000795258
> 200101120000795259
> 200101120000798489 200101120000798490 200101120000800876
> 200101120000800877
> 200101120000802730 200102020000438877 200102020000439476
> 200102020000440686
> 200102020000440939 200102020000441254 200102020000441644
> 200102090000765532
> 200102090000968905 200102160001114409 200102160001116408
> 200102160001116700
> 200102160001116729 200102160001116923 200102160001117220
> 200102160001118335
> 200102160001118449 200102160001120357 200102230001416165
> 200102230001416963
> 200102230001420457 200102230001420862 200103020000508019
> 200103020000509525
> 200103090000858787 200103090000860013 200103090000860811
> 200103160001140842
> 200103160001141980 200103160001142492 200103160001143967
> 200103160001144122
> 200103160001144367 200103160001145320 200103230001505683
> 200103230001507578
> 200103230001507579 200103230001507743 200103230001508571
> 200103230001511013
> 200103230001512119 200103230001514681 200103300001815712
> 200104060000370163
> 200104060000373008 200104060000375439 200104060000376932
> 200104060000376933
> 200104130000715154 200104130000715944 200104130000716952
> 200104130000719526
> 200104200001008443 200104200001008473 200104200001011772
> 200104200001013701
> 200104200001013701 200104200001013702 200104270000623373
> 200104270001348532
> 200104270001350966 200104270001350967 200104270001351005
> 200105040000368278
> 200105040000369175 200105040000369803 200105040000369803
> 200105040000370279
> 200105040000370370 200105040000370968 200105040000371373
> 200105040000372901
> 200105040000373875 200105110000675078 200105180000999687
> 200105180001000136
> 200105180001000146 200105180001001688 200105180001001791
> 200105180001002537
> 200105180001003632 200105180001005482 200105250001354984
> 200105250001355762
> 200105250001356987 200105250001358158 200105250001358480
> 200105250001358659
> 200105250001362444 200105250001362445 200106010000388469
> 200106010000388470
> 200106010000393703 200106010000394451 200106080000662272
> 200106080000662406
> 200106080000662896 200106080000664020 200106150001023603
> 200106150001023835
> 200106220001306145 200106220001313192 200107060000301347
> 200107130000556409
> 200107130000558248 200107200000814918 200107200000815551
> 200107270001158317
> 200107270001158317 200108030000349904 200108030000349908
> 200108030000408206
> 200108030000410820 200108100000697951 200108170001010320
> 200108170001014043
> 200108240001360403 200108240001360950 200108240001361726
> 200108240001362882
> 200108310001636653 200109070000323983 200109070000326925
> 200109070000327209
> 200109070000329603 200109070000329821 200109070000331516
> 200109140000648056
> 200109210000924235 200109210000926028 200109210000927855
> 200109280001233420
> 200109280001234874 200109280001235231 200110050002156109
> 200110120002494718
> 200110120002495670 200110190002791639 200110190002792163
> 200110190002797501
> 200110260003130457 200110260003131949 200110260003131950
> 200110260003134438
> 200110260003134677 200110260003134777 200110260003135104
> 200110260003135658
> 200110260003136989 200110260008874803 200110260008874803
> 200111020002016407
> 200111020004055145 200111020004055145 200111090002255481
> 200111090002257669
> 200111090002258519 200111090002262516 200111090004460631
> 200111160002585010
> 200111160002585011 200111160002585012 200111160002586855
> 200111160002590176
> 200111160005721338 200111160006301304 200111160006301373
> 200111230002893286
> 200111230002893364 200112070000353437 200112070000355939
> 200112070000359618
> 200112070000359623 200112140000651669 200112210000944461
> 200112210000944852
> 200112210000947713 200112210000948327 200112210000953291
> 200112210000954466
> 200201040000647531 200201040000649063 200201040000651214
> 200201040000651285
> 200201110000855621 200201110000856925 200201180001113360
> 200201180001113418
> 200201180001119146 200201250001478137 200202080000162995
> 200202080000884950
> 200202080000888951 200202080010931001 200202150001186811
> 200202150001189895
> 200202150001191379 200202220001520508 200202220001522014
> 200202220001522019
> 200202220001523607 200202220001525144 200202220001526436
> 200202220001526436
> 200202220011386871 200202220011389045 200203010000510604
> 200203010000510728
> 200203010000511566 200203010000511688 200203010000511740
> 200203010000513460
> 200203010000515648 200203010000517120 200203080000839638
> 200203080000840780
> 200203080000844096 200203150001156516 200203150001157223
> 200203150001157705
> 200203150001157706 200203150001160379 200203150001162669
> 200203220001490592
> 200203290001808256 200203290001808257 200203290001808258
> 200203290001808259
> 200204050000396851 200204120000719565 200204120000719856
> 200204120000722793
> 200204120000724152 200204120000724153 200204190001061007
> 200204190001061010
> 200204190001063927 200204260001385408 200205030005051000
> 200205100005323223
> 200205100005325881 200205100005326993 200205100005328918
> 200205100005328919
> 200205100013650676 200205100013650748 200205100013652478
> 200205170005693347
> 200205170005693348 200205310006289285 200205310006290305
> 200205310006290306
> 200206070000915329 200206070000917728 200206140001218850
> 200206140001220688
> 200206140001222959 200206210001518596 200206280001876405
> 200206280001876406
> 200206280001877351 200207050000813390 200207050000816621
> 200207050000816764
> 200207050000817700 200207120001050971 200207190001328050
> 200207260001602029
> 200208020001188035 200208090001504652 200208090001505352
> 200208090001505353
> 200208090001506120 200208090001506733 200208090001506802
> 200208090001506802
> 200208160001839205 200208160001839660 200208160001839661
> 200208160001841814
> 200208160001843251 200208230002158822 200208230002159745
> 200208300002453523
> 200209060000940677 200209130001177710 200209130001180087
> 200209200001463436
> 200209200001472040 200209270001838345 200209270001840526
> 200210040001051443
> 200210040001054485 200210110000293898 200210110001344670
> 200210110001347887
> 200210110001348106 200210180001682514 200210250002007321
> 200211010001224569
> 200211080001469140 200211080001469174 200211080001472367
> 200211080001473835
> 200211080001473841 200211080001473841 200211150001774245
> 200211220002081223
> 200211290002374861 200211290002374862 200212060000929055
> 200212060000929056
> 200212060000930167 200212060000930796 200212060000934799
> 200212060000935593
> 200212130001265934 200212270001866514 200212270001866515
> 200301100001294873
> 200301170001594598 200301170001595104 200301170001598575
> 200301170001600153
> 200301240001877829 200301240001878557 200301240001878853
> 200301240001881578
> 200301240001881838 200302070000999923 200302140001365781
> 200302140001365782
> 200302140001365782 200302140001367326 200302140001369312
> 200302140001369911
> 200302140001373029 200302210001678052 200302280001978399
> 200302280001980775
> 200302280001984217 200302280001984530 200303070000965312
> 200303070000967156
> 200303140001272940 200303280001883825 200303280001886889
> 200304040000969171
> 200304040000969713 200304040000969713 200304110000269963
> 200304110000269963
> 200304110001272794 200304110001277601 200304250000729636
> 200305020001180891
> 200305090001489424 200305090001489486 200305160001801999
> 200305160001805649
> 200305160001806377 200305230002108504 200305230002109424
> 200305230002110100
> 200305230002110100 200306060001020637 200306130001360206
> 200306200001667531
> 200306200001667532 200306200001668043 200306200001668367
> 200306270001975826
> 200306270001975973 200306270001976453 200306270001979210
> 200306270001981161
> 200306270001981217 200306270001982030 200306270001983609 ). 698
> quit;
> NOTE: The SAS System stopped processing this step because of errors.
> NOTE: PROCEDURE SQL used:
> real time 1.62 seconds
> cpu time 0.06 seconds
>
>
> It appears as though the second right parenthesis in line 696 has been read
> as a period ".". But if I add one more right parenthesis the SAS compiler
> generates an error message before the code is sent to Oracle. I have
> encountered this problem before and did not find a solution then but simply
> approached the problem another way. That option is not available this time.
>
>
> Any insight would be appriciated.
>
> Rodney
>
>
> Rodney J. Presley, Ph.D.
> Director of Data Analysis
> Georgia Medical Care Foundation
> 1455 Lincoln Parkway
> suite 800
> Atlanta, GA 30346
> 678 - 527 - 3474 voice
> 678-527-3574 fax
>
> **************************************************************************
> 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.
| 
