ORA - 00905 Missing Keyword 1

[IngDep]

Having trouble locating a "Missing Keyword" error in the sql below that I am using within Sql Developer to extract data from an Oracle database.

What is the problem and resolution?

Thanks in advance.

Meanwhile, I will continue to troubleshoot.

alter session set nls_date_format = 'MM/DD/YY';
SELECT CLCL_ID,
CDML_SEQ_NO,
CLCL.PRPR_ID AS SVC_PRPR_ID,
CLCL.CLCL_PAYEE_PR_ID AS PAY_TO_PRPR_ID,
CLCL.CLCL_PRPR_ID_PCP AS PCP_PRPR_ID,
PRPR.PRPR_NAME,
MEME_CK,
(CASE
WHEN CLCL.GRGR_CK = 4 THEN
CASE
WHEN MEPE.CSCS_ID = 'KD03' THEN 'KAH'
WHEN MEPE.CSCS_ID = 'CD03' THEN 'CAIS'
WHEN MEPE.CSCS_ID = 'PL03' THEN 'PLAF'
END
CASE
WHEN CLCL.GRGR_CK = 8 THEN
CASE
WHEN MEPE.CSCS_ID = 'TR04' THEN 'THGR'
END
ELSE 'XX'
END) AS PRODUCT,
SBSB_ID,
GRGR_CK,
CSCS_ID,
CDML_FROM_DT,
CDML_TO_DT,
CDML_CONSIDER_CHG,
CDML_PR_PYMT_AMT,
CDML_SB_PYMT_AMT,
CLCL.CLCL_PAID_DT,

FROM CMC_CLCL_CLAIM CLCL
INNER JOIN CMC_PRPR_PROV PRPR
ON CLCL.PRPR_ID = PRPR.PRPR_ID
AND CLCL.GRGR_CK = 8
AND CLCL.CSCS_ID = 'TR04'
AND CLCL.CLCL_PAID_DT BETWEEN '04/06/09' AND '04/12/09'
INNER JOIN CMC_CDML_CL_LINE CDML
ON CLCL.CLCL_ID = CDML.CLCL_ID
INNER JOIN CMC_SBSB_SUBSC SBSB
ON CLCL.SBSB_CK = SBSB.SBSB_CK
INNER JOIN CMC_EXCD_EXPL_CD EXCD
ON CDML.CDML_DISALL_EXCD = EXCD.EXCD_ID
LEFT JOIN CMC_CLHP_HOSP CLHP
ON CLCL.CLCL_ID = CLHP.CLCL_ID

 

[Turkbear]

Hi,
The first thing I noticed was the comma after the last column in the select statement ( just before the FROM) - it is not needed and will cause that error...








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

1) Try formatting your code and you may find the error (for tek-tips forum use the code-/code tags)

2) Apart from the extra comma, you have an extra "CASE":

SELECT clcl_id, cdml_seq_no, clcl.prpr_id AS svc_prpr_id,
       clcl.clcl_payee_pr_id AS pay_to_prpr_id,
       clcl.clcl_prpr_id_pcp AS pcp_prpr_id, prpr.prpr_name, meme_ck,
       (CASE
           WHEN clcl.grgr_ck = 4
              THEN CASE
                     WHEN mepe.cscs_id = 'KD03'
                        THEN 'KAH'
                     WHEN mepe.cscs_id = 'CD03'
                        THEN 'CAIS'
                     WHEN mepe.cscs_id = 'PL03'
                        THEN 'PLAF'
                  END
        [red][b]CASE[/b][/red] <-- Here
        WHEN clcl.grgr_ck = 8
              THEN CASE
                     WHEN mepe.cscs_id = 'TR04'
                        THEN 'THGR'
                  END
           ELSE 'XX'
        END
       ) AS product,
       sbsb_id, grgr_ck, cscs_id, cdml_from_dt, cdml_to_dt, cdml_consider_chg,
       cdml_pr_pymt_amt, cdml_sb_pymt_amt, clcl.clcl_paid_dt
  FROM cmc_clcl_claim clcl INNER JOIN cmc_prpr_prov prpr
       ON clcl.prpr_id = prpr.prpr_id
     AND clcl.grgr_ck = 8
     AND clcl.cscs_id = 'TR04'
     AND clcl.clcl_paid_dt BETWEEN '04/06/09' AND '04/12/09'
       INNER JOIN cmc_cdml_cl_line cdml ON clcl.clcl_id = cdml.clcl_id
       INNER JOIN cmc_sbsb_subsc sbsb ON clcl.sbsb_ck = sbsb.sbsb_ck
       INNER JOIN cmc_excd_expl_cd excd ON cdml.cdml_disall_excd =
                                                                  excd.excd_id
       LEFT JOIN cmc_clhp_hosp clhp ON clcl.clcl_id = clhp.clcl_id

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb