ORA-00905:由于多个案例条件而缺少关键字

Question

在逻辑时，由于多个情况，我得到missing keyword错误。下面是我的代码

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;

Answer 1

您不能在没有任何间隔的情况下拥有ELSE END，但是您也不能有一个条件作为WHEN或ELSE子句来计算。

您可以使用布尔逻辑而不是大小写表达式：

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))

Answer 2

不能从这样的情况返回条件(布尔值)：

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

相反，您可以使用这样的案例表达式：

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