return null if no rows found oracle query with IN clause

0
votes

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

2
oracleplsql
Use Decode function or show us your query we ll help you. – LifeOfPi
@Prathyush I tried decode also but for the value which is not there in table is ignored. select col1,decode(col1, NULL,'zero', col1) from table1 where col4 = 2 and col1 in (1,2,3) – Gopi
what is col1 here? is it primary key? – LifeOfPi
table 1 has col1, col2, col3, col4 and col1,col2, col3 combination is primary key in the table – Gopi
Then you must try to use subquery. have something like select decode((subquery from table1),null,'test',(subquery from table1)) from table 1. I know this will make your query very slow – LifeOfPi

2 Answers

1
votes

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
0
votes

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.