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

1

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

2

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.