• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1041
  • Last Modified:

ORA-00907: missing right parenthesis

this insert statement works fine in toad but in sqlplus it returns

ORA-00907: missing right parenthesis
database version is 11g




insert /*+ APPEND */ into sd_schema.fact_sec_tab
        (reqID, YEAR, MONTH, QUARTER, LABS, RADIOLOGY, PHARMRX, PHARMOTC, DME, TRANSPORT,
         EYECARE, LICENSE_NO, req_CD, LOADDATE, EXPORTDATE, TOTAL, HH, BILLING_DATE, LABS_CLAIM_CT,
         RADIOLOGY_CLAIM_CT, PHARMRX_CLAIM_CT, PHARMOTC_CLAIM_CT, DME_CLAIM_CT, TRANSPORT_CLAIM_CT,
             EYECARE_CLAIMS_CT, NURSE, NURSE_CLAIM_CT, HH_CLAIMS_CT, CLINPSYCH, CLINPSYCH_CLAIM_CT)
select req_id, year, month, 'Q'||to_char(to_date(month||year,'MMYYYY'),'Q') quarter, sum(lab) lab, sum(rad) rad, sum(rx) rx, sum(otc) otc, sum(dme) dme,
       sum(trn) trn, sum(eye) eye, null lic, null reqcd, sysdate loaddate, sysdate exportdate, sum(lab+rad+rx+otc+dme+trn+eye+hh) total, sum(hh) hh, to_date(month||year,'MMYYYY') billing_date,
       sum(lab_ct) lab_ct, sum(rad_ct) rad_ct, sum(rx_ct) rx_ct, sum(otc_ct) otc_ct, sum(dme_ct) dme_ct, sum(trn_ct) trn_ct, sum(eye_ct) eye_ct, sum(nurse) nurse, sum(nurse_ct) nurse_ct, sum(hh_ct) hh_ct, sum(clinpsych) clinpsych, sum(clinpsych_ct) clinpsych_ct
from
    (      
        select prescriber_req_ID req_id, year, month, EYE, DME, HH, LAB, OTC, RAD, RX, TRN, lab_ct,rad_ct,rx_ct,otc_ct,dme_ct,trn_ct,nurse,nurse_ct,hh_ct,clinpsych,clinpsych_ct,eye_ct
        fromorders@dtware
        union
        select REFER_req_ID req_id, year, month, EYE, DME, HH, LAB, OTC, RAD, RX, TRN, lab_ct,rad_ct,rx_ct,otc_ct,dme_ct,trn_ct,nurse,nurse_ct,hh_ct,clinpsych,clinpsych_ct,eye_ct
        from ORD_Ret@dtware
    )
where exists (select 1 from sd_schema.fac_tab1 m where m.reqID = req_id)
group by req_id, year, month;
0
it-rex
Asked:
it-rex
1 Solution
 
it-rexAuthor Commented:
I got
it is actually a bug for inser over a dblink
Insert Over Dblink Get Ora-00907 [ID 444002.1]
the fix is just to use /*+ NO_UNNEST */
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now