troubleshooting Question

ORA-00907: missing right parenthesis

Avatar of it-rex
it-rex asked on
Oracle Database
1 Comment1 Solution1159 ViewsLast Modified:
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;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros