LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2003, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


Back to: Top of message | Previous page | Main SAS-L page