Return null value and do not null

[escastilho]

Guys, good morning!

I have a table where in the same column has null and non-null values ??and I need to return to the same SQL, I tried this way but it returns only non-nulls.

Select all

select a.nome, a.status from tb_cliente a where a.status in (1,2,null)

In the system the status 1 = reactivated, 2 = canceled and null = normal

Na Query precise to return all customers in their respective status.

grateful
Erik

[geovani]

Good afternoon Erick!

You can try this:

Select all

 
select a.nome, NVL(a.status,'Normal') as status 
from tb_cliente a  
where a.status is null or a.status in (1,2)

I hope I have helped!
Hugs!

[escastilho]

Geovani, good morning!

I tried as you explained but it is giving error ORA-01722: invalid number

[geovani]

Is giving the error or-01722: invalid number because using NVL(a.status,'Normal') returns a text when it is null and the field expects a number.

Do this:

Select all

 
select a.nome, DECODE(a.status,1,'REATIVADO',2,'CANCELADO','NORMAL') as status 
from tb_cliente a  
where a.status is null or a.status in (1,2)