CODEWITHSUNDEEP
sqloracle-database
Kaushal Talniya
Kaushal Talniya

Reputation: 192

Syntax error "Missing keyword" in case statement in WHERE clause

I am having some syntax error in my case statement where I am checking below condition:

((RAP10B.CLMSRC = '2', RAP01.EFFDT - 3 years <= RAP10.LOSSDT < RAP01.EFFDT) 
       OR 
(RAP10B.CLMSRC <> '2', RAP01.EFFDT - 3 years <= RAP10.LOSSDT < RAP01.EFFDT - 60 days)

Below is the code what I am using.

CASE
  WHEN RAP10B.CLMSRC = '2'
  THEN rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND rap01.teffdt_t
  WHEN RAP10B.CLMSRC <> '2'
  THEN rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND (rap01.teffdt_t - 60)
END

The error message is "ORA-00905: missing keyword". But I am not able to find the missing thing.

Upvotes: 0

Views: 238

Answers (3)

jarlh
jarlh

Reputation: 44766

It's generally much better to use AND/OR constructions in the WHERE clause instead of case expressions:

WHERE (RAP10B.CLMSRC = '2'
       AND rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND rap01.teffdt_t)
   OR (RAP10B.CLMSRC <> '2'
       AND rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND (rap01.teffdt_t - 60))

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try below -

CASE
  WHEN RAP10B.CLMSRC = '2'
  and rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND rap01.teffdt_t then yourval
  WHEN RAP10B.CLMSRC <> '2'
  and rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) AND (rap01.teffdt_t - 60) yourval1
END

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Are you looking for something like this?

CASE
   WHEN RAP10B.CLMSRC = '2'
      AND rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) 
      AND rap01.teffdt_t THEN <your_column>
   WHEN RAP10B.CLMSRC <> '2'
      AND rap10.lossdt_t BETWEEN Add_months(rap01.teffdt_t, - 36) 
      AND (rap01.teffdt_t - 60) THEN <your_column>
END

Upvotes: 0

Related Questions