Version 11.4. The requirement is select/return the rows if column value present in the table or else return the default value. The column value is passed as the input to the query. Here is the test data
create table test (id int,c1 varchar(10));
insert into test values (1,'A');
insert into test values (1,'B');
insert into test values (1,'C');
insert into test values (1,'D');
insert into test values (1,'E');
insert into test values (2,'F');
insert into test values (2,'G');
insert into test values (2,'H');
insert into test values (0,'Default');
What I tried?
select * from test where id = nvl((select distinct id from test where id = 1),0);
It will return the all the id = 1 rows in the table.
select * from test where id = nvl((select distinct id from test where id = 11),0);
It will return the id = 0 Default row in the table since id = 11 is not there in the table.
Is there better way to do this? Because I query the table twice to get the result and it may degrade the performance. is there any efficient way to do this?