CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperplsqldeveloper
Bharath
Bharath

Reputation: 195

Oracle: Case in Where clause

How to achieve this in Oracle query. I am getting missing expression error in IN clause

  WHERE TBL_DTL_HOST.HOST_METHOD = 'CCtxnPostRq'
AND TRUNC(TBL_DTL_FEATURE.START_DATETIME)  BETWEEN TO_DATE (i_startdate, 'DD/MM/YYYY') AND     TO_DATE (i_enddate,'DD/MM/YYYY')
AND (SELECTED_DNIS IS NULL OR TBL_DTL_FEATURE.DNIS = SELECTED_DNIS)
AND (TEMP_CUSTOMER_ID IS NULL OR TBL_DTL_FEATURE.CUSTOMER_ID = TEMP_CUSTOMER_ID)
AND     
(CASE WHEN i_Feature='All' 
THEN (TBL_DTL_HOST.FEATURE_ID IN ('F020','F021'))
ELSE (TBL_DTL_HOST.FEATURE_ID IN ('F020'));
END)

am i missing anything..? Any help would be appreciated..Thanks

Upvotes: 0

Views: 135

Answers (2)

ruakh
ruakh

Reputation: 183241

Oracle doesn't treat Boolean expressions like other expressions — it handles them as syntax rather than as a type — so CASE expressions can't evaluate to Booleans.

In your case, I think the clearest code is if you just rewrite it a bit:

AND (    TBL_DTL_HOST.FEATURE_ID = 'F020'
      OR (i_Feature = 'All' AND TBL_DTL_HOST.FEATURE_ID = 'F021')
    )

Upvotes: 2

DarrenMB
DarrenMB

Reputation: 2380

the last part of the clause should be like this to evaluate correctly

(CASE WHEN i_Feature='All' AND (TBL_DTL_HOST.FEATURE_ID IN ('F020','F021')) THEN 1
WHEN i_Feature<>'All' AND (TBL_DTL_HOST.FEATURE_ID IN ('F020')) THEN 1
ELSE 0
END) = 1

Upvotes: 0

Related Questions