CODEWITHSUNDEEP
oracle-databaseoracle11g
user1468031
user1468031

Reputation: 39

Decode getting ORA-00938 error

My current SQL is as below:

CASE
    WHEN PARENT.ASLNO IS NULL THEN SUBCATEGORIES.SUBCATEGORY_LIST
    -- #5098
    ELSE DECODE(ASL.CATEGORY_NAME, null,
      NVL(SUBCATEGORIES.SUBCATEGORY_LIST,
        SUBCATEGORIES_PARENT.SUBCATEGORY_LIST),
      decode(SUBCATEGORIES.SUBCATEGORY_LIST,null,null,'N/A'))
    -- End #5098
END AS SUPSPD_SUBCATEGORY_LIST

I am trying to change the following sql 

DECODE(ASL.CATEGORY_NAME, null,
  NVL(SUBCATEGORIES.SUBCATEGORY_LIST,
    SUBCATEGORIES_PARENT.SUBCATEGORY_LIST),
  decode(SUBCATEGORIES.SUBCATEGORY_LIST,null,null,'N/A'))

to 

DECODE(ASL.CATEGORY_NAME, null,
  DECODE(SUBCATEGORIES.SUBCATEGORY_LIST,
    SUBCATEGORIES_PARENT.SUBCATEGORY_LIST),
  decode(SUBCATEGORIES.SUBCATEGORY_LIST,null,null,'N/A'))

but I am getting ORA-00938 not enough arguments for function?

Upvotes: 0

Views: 5031

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

This:

  NVL(SUBCATEGORIES.SUBCATEGORY_LIST,
    SUBCATEGORIES_PARENT.SUBCATEGORY_LIST)

returns the parent subcategory list if the child's is null.

Decode needs at least three argumentsm but you're only supplied two; you have the first search value but no result, and no default either, which means you don't always get a value back.

The equivalent for your NVL would be:

  DECODE(SUBCATEGORIES.SUBCATEGORY_LIST, NULL,
    SUBCATEGORIES_PARENT.SUBCATEGORY_LIST, SUBCATEGORIES.SUBCATEGORY_LIS),

I'm not really sure why you'd want to make the statement longer and a bit harder to understand... for some consistency I might even be tempted to change

  decode(SUBCATEGORIES.SUBCATEGORY_LIST,null,null,'N/A')

to

  NVL2(SUBCATEGORIES.SUBCATEGORY_LIST, 'N/A', null)

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132580

You have changed:

NVL(SUBCATEGORIES.SUBCATEGORY_LIST, SUBCATEGORIES_PARENT.SUBCATEGORY_LIST)

to:

DECODE(SUBCATEGORIES.SUBCATEGORY_LIST, SUBCATEGORIES_PARENT.SUBCATEGORY_LIST)

DECODE needs at least 3 arguments.

Aside

I find it much easier to debug complex expressions like this by laying them out nicely just like any other code:

DECODE( ASL.CATEGORY_NAME
      , null
      , DECODE ( SUBCATEGORIES.SUBCATEGORY_LIST
               , SUBCATEGORIES_PARENT.SUBCATEGORY_LIST
               )
      , decode ( SUBCATEGORIES.SUBCATEGORY_LIST
               , null
               , null
               , 'N/A'
               )
      )

Upvotes: 1

Related Questions