CODEWITHSUNDEEP
oracle-databaseplsql
Gopi
Gopi

Reputation: 27

return null if no rows found oracle query with IN clause

I have a table with three columns. I query that table with IN clause.

select column1 from table1 where column1 in (1,2,3) order by column2, column3

The table1 contains only values 1 and 2 in column1. I want to return the not available value also in my result, and that should be sorted in the bottom.

example data

column1 column 2 column 3

1   100 11

2   101 50

output, the not available values should be in the last.

column1 column 2 column 3

1   100 11

2   101 50

3 null null

I tried with subquery with NVL, like select nvl((select.. in(1,2,3)),null) from dual, due to IN Clause, I am getting single row subquery returns more than one row issue, which is expected.

Also tried with the union but nothing works. Great if any help. Thanks

Upvotes: 0

Views: 3406

Answers (2)

Adrián Domínguez
Adrián Domínguez

Reputation: 11

I think you can do it with a union all:

select column1 from table1 where column1 in (1,2,3) order by column2, column3 
union all 
select null from table1 where column1 not in (1,2,3) order by column2, column3

Upvotes: 1

Francesco Serra
Francesco Serra

Reputation: 813

If you can't take 1,2,3 values from another table you can try with:

with t1 as (
  select col1,col2,col3
  from tab1
  where cod_flusso in ('1','2','3')),
t2 as (
  select '1' as col1,null,null
  from dual
  union
  select '2',null,null
  from dual
  union
  select '3',null,null
  from dual)
select t2.col1,col2,col3
from t2
left outer join t1
on t1.col1= t2.col1

It's better if you can store 1,2,3 values in a second table, then use left outer join.

Upvotes: 0

Related Questions