ORA-00939: too many arguments for function in CASE Statement

Question

I'm getting the ORA-00939: too many arguments for function error from my case statement.

I have tried splitting it up into multiple CASE statements but still get the same error.

CASE WHEN l.fridge_door_modela_id = 'II-SH' THEN 'IW' 
     WHEN l.fridge_door_modela_id = 'IIC-SH' THEN 'IW' 
     WHEN l.fridge_door_modela_id = 'CD' THEN 'RPFX' 
     WHEN l.fridge_door_modela_id LIKE 'EXCR%' THEN SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, 'EXCR'),1)
     WHEN l.fridge_door_modela_id LIKE 'EX%' THEN SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, 'EX'),1) 
     WHEN l.fridge_door_modela_id LIKE '%-%' THEN SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, '-'),1) 
     ELSE 'CHECK CODE' 
     END AS Division

Answer 1

SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, 'EXCR'),1)

• The above syntax for SUBSTR is incorrect. The correct syntax is:

  SUBSTR( string, start_position [, length ] )

• Also, the index of substr starts from 1 and not 0.

Executing it in SQL*Plus shows the exact error clearly, see the following error:

SQL> WITH DATA AS(
  2  SELECT 'abcdEXCRijkl' fridge_door_modela_id FROM dual
  3  )
  4  SELECT SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, 'EXCR'),1)
  5  FROM DATA;
SELECT SUBSTR(fridge_door_modela_id, 0, INSTR(fridge_door_modela_id, 'EXCR'),1)
                                                                             *
ERROR at line 4:
ORA-00939: too many arguments for function


SQL>

Using proper syntax:

SQL> WITH DATA AS(
  2  SELECT 'abcdEXCRijkl' fridge_door_modela_id FROM dual
  3  )
  4  SELECT SUBSTR(fridge_door_modela_id, 1, INSTR(fridge_door_modela_id, 'EXCR', 1, 1)-1)
  5  FROM DATA;

SUBS
----
abcd

SQL>