How to fix Nested CASE Syntax Error “Invalid Number of Arguments” in Oracle SQL

Im running a query in Oracle and am trying to create a new column that is defined by the nested CASE statement below. I get an error on the highlighted “Else” statement saying “Invalid Number of Arguments”.

I looked it over many times and it seems to have the correct number of arguments. Am i missing something here?

SELECT 
CASE
    WHEN TRD_TYP IN ('Swaption', 'IRG Floor', 'IRG Cap') 
    THEN 
        CASE
            WHEN BUY_SELL = 'BUY' 
            THEN CURR_NOTNL_CUR 
            ELSE -CURR_NOTNL_CUR
        END
    ELSE
        CASE 
            WHEN TRD_TYP = 'IRSWAP' 
            THEN CURR_NOTNL_CUR                 
        END
    **ELSE**
        CASE 
            WHEN TRD_TYP = 'EQSWAP' 
            THEN CURR_NOTNL_UNIT
        END
    ELSE
        CASE 
            WHEN TRD_TYP = 'FUTURE' 
            THEN
                CASE
                    WHEN BUY/SELL = 'BUY' 
                    THEN CURR_NOTNL_CUR / PRC 
                    ELSE -CURR_NOTNL_CUR / PRC
                END
        END
    ELSE
        CASE 
            WHEN BUY_SELL = 'BUY' 
            THEN CURR_NOTNL_UNIT 
            ELSE -CURR_NOTNL_UNIT
        END
END AS UNITS_OF_UNDERLYING
FROM ACTLANN.HDG_ASST_DTLS

Answer

It seems like you want:

SELECT 
CASE
    WHEN TRD_TYP IN ('Swaption', 'IRG Floor', 'IRG Cap')
    THEN
        CASE
            WHEN BUY_SELL = 'BUY' 
            THEN CURR_NOTNL_CUR 
            ELSE -CURR_NOTNL_CUR
        END
    WHEN TRD_TYP = 'IRSWAP'
    THEN CURR_NOTNL_CUR                 
    WHEN TRD_TYP = 'EQSWAP'
    THEN CURR_NOTNL_UNIT
    WHEN TRD_TYP = 'FUTURE'
    THEN
        CASE
            WHEN BUY/SELL = 'BUY' 
            THEN CURR_NOTNL_CUR / PRC 
            ELSE -CURR_NOTNL_CUR / PRC
        END
    ELSE
        CASE 
            WHEN BUY_SELL = 'BUY' 
            THEN CURR_NOTNL_UNIT 
            ELSE -CURR_NOTNL_UNIT
        END
END AS UNITS_OF_UNDERLYING
FROM ACTLANN.HDG_ASST_DTLS

Your code has multiple ELSE clauses against the outer CASE expression, and there can only be one. The first three each have an inner CASE which doesn’t need to be there; the WHEN ... THEN for those can be ‘promoted’ to the outer expression.