Search code examples
sqloracle-databaseplsql

DECODE with multiple equal conditions?

Is there any way to simplify this DECODE function? Note the variables are being compared to a NULL to validate if they are or not.

CREATE FUNCTION FNC_001
    (VAR_1 IN OUT VARCHAR, VAR_2 IN OUT VARCHAR, VAR_3 IN OUT VARCHAR) RETURN VARCHAR IS
 ERR_ERRFLG CHAR(1);
 BEGIN

    VAR_1 := TRIM(VAR_1);
    VAR_2 := TRIM(VAR_2);
    VAR_3 := TRIM(VAR_3);

    SELECT DECODE(NULL, VAR_1, 'Y', VAR_2, 'Y', VAR_3, 'Y', 'N') INTO ERR_ERRFLG
       FROM DUAL;

 RETURN ERR_ERRFLG;

 END;
Solution

  • As you want to return null if any of parameters is null, then one option is to use e.g.

    return case when greatest(var_1, var_2, var_3) is null then 'Y' 
            else 'N'
       end;

    Another might be

    return case when var_1 is null or var_2 is null or var_3 is null then 'Y'
            else 'N'
       end;

    but it is questionable if any of these is simpler than what you already have. Why don't you like it?