CODEWITHSUNDEEP
oracle-databasecase
HEEN
HEEN

Reputation: 4721

ORA-00905: missing keyword due to multiple CASE Conditions

I m gettingmissing keyword error due to multiple CASE WHEN logic. Below is my code

select inv.ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH, inv.SPAN_TYPE,  ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, 
ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH,
ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4)/ ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4)*100 as ug_percentage
,mv.rj_intracity_link_id  FROM NE.MV_SPAN@DB_LINK_NE_VIEWER mv
join TBL_FIBER_INV_CMP_REPORT_MV inv
on   (CASE WHEN  inv.SPAN_TYPE = 'INTERCITY' THEN inv.SPAN_LINK_ID = mv.rj_span_id ELSE END)
      CASE WHEN inv.SPAN_TYPE = 'INTRACITY' THEN inv.SPAN_LINK_ID = mv.rj_intracity_link_id ELSE END)
      CASE WHEN inv.SPAN_TYPE = 'ENTERPRISE' THEN inv.SPAN_LINK_ID = mv.rj_intracity_link_id ELSE END)
GROUP BY mv.rj_intracity_link_id, inv.ROUTE_APPROVED_BY_CMM, inv.SPAN_TYPE;

Upvotes: 0

Views: 80

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132570

You cannot return a condition (Boolean) from a CASE like this:

CASE WHEN inv.SPAN_TYPE = 'INTRACITY' THEN inv.SPAN_LINK_ID = mv.rj_intracity_link_id ELSE END

Instead you can use a CASE expression like this:

ON inv.SPAN_LINK_ID = CASE inv.SPAN_TYPE
                         WHEN 'INTERCITY' THEN mv.rj_span_id
                         WHEN 'INTRACITY' THEN mv.rj_intracity_link_id
                         WHEN 'ENTERPRISE' THEN mv.rj_intracity_link_id
                         END

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

You can't have ELSE END without anything in between, but you also can't have a condition to evaluate as the WHEN or ELSE clause.

You can use Boolean logic rather than case expressions:

join TBL_FIBER_INV_CMP_REPORT_MV inv
on   ((inv.SPAN_TYPE = 'INTERCITY' AND inv.SPAN_LINK_ID = mv.rj_span_id)
   or (inv.SPAN_TYPE = 'INTRACITY' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id)
   or (inv.SPAN_TYPE = 'ENTERPRISE' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id))

Upvotes: 1

Related Questions