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

Question

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

What is that `**ELSE**` (assuming that's supposed to be highlighting where the error is reported) supposed to be the `ELSE` *of*? — Alex Poole, Jan 30 '19 at 17:13
Yes this is where i am getting the error. But if i delete that ELSE section itself, the error moves on to the next ELSE — Manny, Jan 30 '19 at 17:15
Yes - but what are they supposed to be doing? You have the outer `CASE` with one `WHEN` and four `ELSE`s, which isn't right. — Alex Poole, Jan 30 '19 at 17:16

score 1 · Accepted Answer · answered Jan 30 '19 at 17:19

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.

I was referring to this when i was creating the nested Case. Perhaps i misinterpreted it. https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server — Manny, Jan 30 '19 at 17:23

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

1 Answers1