ORA-00909: invalid number of arguments

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!

3 Answers

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

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, ' ')

you must have only two arguments for

nvl function

like in NVL(es3.last_name,'Özhan') returns Özhan if es3.last_name is null.

i.e. problem stems from having more than two arguments.

In the question problematic part is ..' ' ,.. in

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