Case嵌套case语句

Question

您好，我在向嵌套的case语句中添加另一组条件时遇到问题。我尝试了几种方法重新格式化语句，并将"END“放在不同的位置，但我似乎无法通过ORA-00909:无效的参数数量错误。我认为我只需要在每个情况下结束一个结束，并且结束需要在其他结束之前结束那个条件。这是错的吗？如果我注释掉最后一个else条件，它工作得很好。

根据下面的注释，我已经尝试整理代码并设置最相关的else和end语句，但我仍然收到相同的错误。本质上应该有5组条件。

    CASE 
    WHEN IND0.M_COM_FUT <>0 AND IND0.M_COM_FUT = IND5.M_COM_IND 
    THEN 
        CASE    WHEN IND5.M_COM_IND = CMIND5.M_REFERENCE 
                THEN
                    CASE    WHEN CMIND5.M_PHYSICAL = PHYS5.M_REFERENCE 
                            THEN PHYS5.M_LABEL 
                    END 
        END 
    ELSE
        CASE    WHEN   IND0.M_UNDRL = IND3.M_INDEX AND IND3.M_IND_LAB LIKE '%NBY%' AND IND3.M_COM_NBY_T = 2 
                THEN
                    CASE    WHEN  IND3.M_COM_FUT = IND6.M_COM_IND 
                            THEN 
                                CASE    WHEN IND6.M_COM_IND = CMIND6.M_REFERENCE 
                                        THEN
                                            CASE    WHEN CMIND6.M_PHYSICAL = PHYS6.M_REFERENCE 
                                                    THEN PHYS6.M_LABEL 
                                            END 
                                END 
                    END
          END          
       ELSE
            CASE    WHEN IND0.M_UNDRL = IND3.M_INDEX 
                    THEN
                        CASE    WHEN IND3.M_COM_IND = CMIND3.M_REFERENCE 
                                THEN
                                    CASE    WHEN CMIND3.M_PHYSICAL = PHYS3.M_REFERENCE 
                                            THEN PHYS3.M_LABEL 
                                    END 
                        END                         
            END
        ELSE
            CASE    WHEN   IND0.M_UNDRL = IND3.M_INDEX AND IND3.M_IND_LAB LIKE '%NBY%' AND IND3.M_COM_NBY_T = 0 
                    THEN 
                        CASE    WHEN  IND3.M_COM_FUT = FUT.M_REFERENCE 
                                THEN 
                                    CASE    WHEN FUT.M_QUOT_FWD= QUOT.M_REFERENCE 
                                            THEN
                                                CASE    WHEN QUOT.M_INDEX = IND0.M_INDEX 
                                                        THEN PHYS0.M_LABEL 
                                                END 
                                    END 
                        END 
            END 
         ELSE
            CASE    WHEN IND0.M_IND_LAB= IND0.M_IND_LAB 
                    THEN PHYS0.M_LABEL
            END 

END AS PRODUCT,

Answer 1

如果您更好地格式化您代码，您应该会发现它有重复的else，并且还遗漏了一些end

CASE 
    WHEN IND0.M_COM_FUT <>0 AND IND0.M_COM_FUT = IND5.M_COM_IND 
    THEN 
        CASE    WHEN IND5.M_COM_IND = CMIND5.M_REFERENCE 
                THEN
                    CASE    WHEN CMIND5.M_PHYSICAL = PHYS5.M_REFERENCE 
                            THEN PHYS5.M_LABEL 
                    END 
        END 
    ELSE
        CASE    WHEN   IND0.M_UNDRL = IND3.M_INDEX AND IND3.M_IND_LAB LIKE '%NBY%' AND IND3.M_COM_NBY_T = 2 
                THEN
                    CASE    WHEN  IND3.M_COM_FUT = IND6.M_COM_IND 
                            THEN 
                                CASE    WHEN IND6.M_COM_IND = CMIND6.M_REFERENCE 
                                        THEN
                                            CASE    WHEN CMIND6.M_PHYSICAL = PHYS6.M_REFERENCE 
                                                    THEN PHYS6.M_LABEL 
                                            END 
                                END 
                    END 
                ELSE
                    CASE    WHEN IND0.M_UNDRL = IND3.M_INDEX 
                            THEN
                                CASE    WHEN IND3.M_COM_IND = CMIND3.M_REFERENCE 
                                        THEN
                                            CASE    WHEN CMIND3.M_PHYSICAL = PHYS3.M_REFERENCE 
                                                    THEN PHYS3.M_LABEL 
                                            END 
                                END 
                            ELSE
                                CASE    WHEN IND0.M_IND_LAB= IND0.M_IND_LAB 
                                        THEN PHYS0.M_LABEL
                                END 
                            END 
                    END
                ELSE
                    CASE    WHEN   IND0.M_UNDRL = IND3.M_INDEX AND IND3.M_IND_LAB LIKE '%NBY%' AND IND3.M_COM_NBY_T = 0 
                            THEN 
                                CASE    WHEN  IND3.M_COM_FUT = FUT.M_REFERENCE 
                                        THEN 
                                            CASE    WHEN FUT.M_QUOT_FWD= QUOT.M_REFERENCE 
                                                    THEN
                                                        CASE    WHEN QUOT.M_INDEX = IND0.M_INDEX 
                                                                THEN PHYS0.M_LABEL 
                                                        END 
                                            END 
                                END 
                    END 
END AS PRODUCT,

Answer 2

试着这样开始吧。我不确定您在这里的逻辑是什么，但至少应该运行它，并根据您的代码给出我认为您正在寻找的结果。

CASE
  WHEN IND0.M_COM_FUT <> 0
   AND IND0.M_COM_FUT = IND5.M_COM_IND
   AND IND5.M_COM_IND = CMIND5.M_REFERENCE
   AND CMIND5.M_PHYSICAL = PHYS5.M_REFERENCE
    THEN PHYS5.M_LABEL  
  WHEN IND0.M_UNDRL = IND3.M_INDEX
   AND IND3.M_IND_LAB LIKE '%NBY%'
   AND IND3.M_COM_NBY_T = 2
   AND IND3.M_COM_FUT = IND6.M_COM_IND
   AND IND6.M_COM_IND = CMIND6.M_REFERENCETHEN
   AND CMIND6.M_PHYSICAL = PHYS6.M_REFERENCE
    THEN PHYS6.M_LABEL 
  WHEN IND0.M_UNDRL = IND3.M_INDEX
   AND IND3.M_COM_IND = CMIND3.M_REFERENCE
   AND CMIND3.M_PHYSICAL = PHYS3.M_REFERENCE
    THEN PHYS3.M_LABEL 
  WHEN IND0.M_UNDRL = IND3.M_INDEX
   AND IND3.M_IND_LAB LIKE '%NBY%'
   AND IND3.M_COM_NBY_T = 0 
   AND IND3.M_COM_FUT = FUT.M_REFERENCE
   AND FUT.M_QUOT_FWD= QUOT.M_REFERENCE
   AND QUOT.M_INDEX = IND0.M_INDEX
    THEN PHYS0.M_LABEL 
  WHEN IND0.M_IND_LAB= IND0.M_IND_LAB
    THEN PHYS0.M_LABEL
END

如果WHEN语句不匹配，它会自动转到下一个WHEN。

然后，匹配的第一个WHEN语句将忽略所有其他WHEN语句，即使它下面的一个或多个WHEN语句也将匹配。

如果没有匹配的WHEN语句，则可以在返回默认值的最后一次WHEN之后使用ELSE。