ORA-00909: invalid number of arguments

Question

Maybe I am putting the parenthesis wrong or a wrong comma? I know this is a fairly novice question so I apologize in advance. I originally had this code:

es3.last_name || ', ' ||SUBSTR(es3.first_name,1,1)

But es3 was a Left Outer Join so all the blanks were showing the ', ' so I tried this below and am getting the ERROR

ORA-00909: invalid number of arguments

NVL(es3.last_name, ' ' , es3.last_name || ', ' ||SUBSTR(es3.first_name,1,1))

Thanks!

Answer 1

From the documentation:

The syntax for the NVL function in Oracle/PLSQL is:

NVL( string1, replace_with )

You are feeding it 3 arguments, hence the error message. I believe you want this instead:

CASE WHEN es3.last_name IS NULL
     THEN ''
     ELSE es3.last_name || ', ' ||SUBSTR(es3.first_name,1,1)
END

Answer 2

NVL can have only two arguments

NVL(String, Value )

So the above function will return 'Value' when string is null. And 'String' itself when string is not null.

Use NVL2. NVL2 can have three arguments

NVL2(String, Value_when_not_null, Value_When_Null)

So when 'String' is not null, it returns second parameter. When String is null it returns third paramater.

So your function should be

NVL2(es3.last_name||es3.first_name , es3.last_name||','||es3.first_name, ' ')