CODEWITHSUNDEEP
sqloracle-database
Riddler
Riddler

Reputation: 15

Query regarding NVL in SQL

Write a query to display department Id, department name, manager id of all departments. If manager id is not available then display 'NA' and give alias name as 'MANAGER_ID'. Sort the result based on department Id. The type of manager_id is number.

select
    department_id, department_name, manager_id, NVL(manager_id, 'na') "BONUS_AMT"
from
    departments
order by 1;

is my query but the error is ERROR at line 1:

ORA-01722: invalid number

NEED HELP!

Upvotes: 0

Views: 75

Answers (2)

Siddharth Jain
Siddharth Jain

Reputation: 336

select
    department_id, department_name, manager_id,
    NVL(cast(manager_id as varchar2(10)), 'na') "BONUS_AMT"
from
    departments
order by 1;

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You are using an NVL to get a string when a NUMBER is null. If you need a string result, you need to convert your number to string; for example:

SQL> select nvl(x, 'a') from (select 1 x from dual);
select nvl(x, 'a') from (select 1 x from dual)
               *
ERROR at line 1:
ORA-01722: invalid 



SQL> select nvl(to_char(x, '999'), 'a') from (select 1 x from dual);

NVL(TO_CHAR(X,'999'),'A')
--------------------------------------------------------------------------------
   1

Upvotes: 2

Related Questions