Oracle error, missing right parathesis

Rsadiq — Tue, 02 Jan 2018 15:55:27 GMT

Hello

I had added a couple policy numbers to my sas code and now I am getting this error below, it could be from the quotations as well but not entirely sure since I do have matching parenthesis.

(select distinct C.MBR_PGM_ID
,C.COV_EFF_DT ,C.COV_END_DT ,C.POL_NM ,C.POL_NBR ,C.lob_typ_id from STG_HSR.MBR_PGM_COV C where pol_nbr in
('X05951','005951','00005951', '000X05951','0X05951','000005951', 'X06163','006163','00006163','000X06163','0X06163','000006163',
'X00181','0X00181','000X00181','000181','00000181','000000181', '36000','036000','00036000','000036000', '360000', 'X06754',
'006754','00006754','000X06754','0X06754','000006754', 'X06764', '006764','00006764','000X06764','0X06764','000006764', 'X06029',
'006029','00006029','000X06029','0X06029','000006029', 'X07015', '007015','00007015','000X07015','0X07015','000007015', 'X08427',
'008427','00008427','000X08427','0X08427','000008427', 'X08401','008401','00008401','000X08401','0X08401','000008401',
'X08426','008426','00008426','000X08426','0X08426','000008426', 'X08434','008434','00008434','000X08434', '0X08434','000008434',
'X09211','009211','00009211','000X09211','0X09211','000009211', 'X09227','009227','00009227','000X09211','0X09227','000009227',
'X09228','009228','00009228','000X09228','0X09228','000009228', 'X09229','009229','00009229','000X09229','0X09229','000009229'
'X03024','003024','00003024','000X03024','0X03024','000003024' 'X05735','005735','00005735','000X05735','0X05735','000005735'
'X06029','006029','00006029','000X06029','0X06029','000006029' 'X09573','009573','00009573','000X09573','0X09573','000009573'
'X09574','009574','00009574','000X09574','0X09574','000009574') and C.COV_END_DT >= to_timestamp('01022018','mmddyyyy') and
C.COV_EFF_DT >= to_timestamp('01022008','mmddyyyy') ) as K ;
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: select distinct C.MBR_PGM_ID ,C.COV_EFF_DT
       ,C.COV_END_DT ,C.POL_NM ,C.POL_NBR ,C.lob_typ_id from STG_HSR.MBR_PGM_COV C where pol_nbr in ('X05951','005951','00005951',
       '000X05951','0X05951','000005951', 'X06163','006163','00006163','000X06163','0X06163','000006163',
       'X00181','0X00181','000X00181','000181','00000181','000000181', '36000','036000','00036000','000036000', '360000', 'X06754',
       '006754','00006754','000X06754','0X06754','000006754', 'X06764', '006764','00006764','000X06764','0X06764','000006764',
       'X06029', '006029','00006029','000X06029','0X06029','000006029', 'X07015',
       '007015','00007015','000X07015','0X07015','000007015', 'X08427', '008427','00008427','000X08427','0X08427','000008427',
       'X08401','008401','00008401','000X08401','0X08401','000008401',
       'X08426','008426','00008426','000X08426','0X08426','000008426', 'X08434','008434','00008434','000X08434',
       '0X08434','000008434', 'X09211','009211','00009211','000X09211','0X09211','000009211',
       'X09227','009227','00009227','000X09211','0X09227','000009227',
       'X09228','009228','00009228','000X09228','0X09228','000009228',
       'X09229','009229','00009229','000X09229','0X09229','000009229'
       'X03024','003024','00003024','000X03024','0X03024','000003024'
       'X05735','005735','00005735','000X05735','0X05735','000005735'
       'X06029','006029','00006029','000X06029','0X06029','000006029'
       'X09573','009573','00009573','000X09573','0X09573','000009573'

My code

proc sql inobs=max outobs=max;
    connect to oracle (USER="&OIMID." PASSWORD="&OIMPW." PATH='URNPRR');
        create table input.mbr_pgmcv as
        select distinct K.* from connection to oracle
      (select distinct        C.MBR_PGM_ID
               ,C.COV_EFF_DT
               ,C.COV_END_DT
               ,C.POL_NM
               ,C.POL_NBR
               ,C.lob_typ_id
               /* added coverage effective and end date logic */
        from STG_HSR.MBR_PGM_COV C
        where pol_nbr in ('X05951','005951','00005951', '000X05951','0X05951','000005951',
     'X06163','006163','00006163','000X06163','0X06163','000006163',
     'X00181','0X00181','000X00181','000181','00000181','000000181',
     '36000','036000','00036000','000036000', '360000',
     'X06754', '006754','00006754','000X06754','0X06754','000006754',
     'X06764', '006764','00006764','000X06764','0X06764','000006764',
     'X06029', '006029','00006029','000X06029','0X06029','000006029',
     'X07015', '007015','00007015','000X07015','0X07015','000007015',
    'X08427', '008427','00008427','000X08427','0X08427','000008427',
    'X08401','008401','00008401','000X08401','0X08401','000008401',
     'X08426','008426','00008426','000X08426','0X08426','000008426',
     'X08434','008434','00008434','000X08434', '0X08434','000008434',
     'X09211','009211','00009211','000X09211','0X09211','000009211',
     'X09227','009227','00009227','000X09211','0X09227','000009227',
     'X09228','009228','00009228','000X09228','0X09228','000009228',
     'X09229','009229','00009229','000X09229','0X09229','000009229'
     'X03024','003024','00003024','000X03024','0X03024','000003024'
   'X05735','005735','00005735','000X05735','0X05735','000005735'
    'X06029','006029','00006029','000X06029','0X06029','000006029'
    'X09573','009573','00009573','000X09573','0X09573','000009573'
    'X09574','009574','00009574','000X09574','0X09574','000009574')

     and C.COV_END_DT >= to_timestamp(&endt.,'mmddyyyy')
     and C.COV_EFF_DT >= to_timestamp(&begdt.,'mmddyyyy')
     ) as K
        ;
        disconnect from oracle;

Re: Oracle error, missing right parathesis

kiranv_ — Tue, 02 Jan 2018 16:50:31 GMT

   ) as K
        ;
        disconnect from oracle;

as K is not needed

change select distinct K.* from connection to oracle

to select distinct * from connection to oracle

as K is needed for a derived table(not necessary in this scenario) .

Re: Oracle error, missing right parathesis

Patrick — Wed, 03 Jan 2018 02:32:22 GMT

@Rsadiq

And to add to what @kiranv_ wrote: Get rid of the DISTINCT on the SAS side as you've got already such a DISTINCT on the Oracle side

 select * from connection to oracle
  (select distinct 
   C.MBR_PGM_ID
   ....

topic Oracle error, missing right parathesis in SAS Enterprise Guide

Oracle error, missing right parathesis

Re: Oracle error, missing right parathesis

Re: Oracle error, missing right parathesis