We help IT Professionals succeed at work.

SQL help: if not found return default type

rmtdev
rmtdev asked
on
Is there a way to do this, if the query does not return anything with person_type = 2, return the record where "person_type is null" instead.
               
               select * from person where person_type is null
               not exists
               select * from person where person_type = 2
Comment
Watch Question

nishant joshiTechnology Development Consultant
BRONZE EXPERT

Commented:
try this..
Declare @sqlquery NVARCHAR(100)
set @sqlquery=case When (select COUNT(*) from PERSON where person_type =2) > 0 
Then 'select * from PERSON where person_type =2'
ELSE 'select * from PERSON where person_type IS NULL'
END
EXECUTE sp_executesql @sqlquery;

Open in new window

Top Expert 2011

Commented:
- not elegant but try the following:

SELECT * FROM person WHERE person_type =
(
SELECT CASE WHEN (SELECT count(*) FROM person WHERE person_type = '2') > '0' then
  '2'
ELSE
  null
END  
FROM dual
);

Author

Commented:
Thanks OP_Zaharin, that is more of what i'm after, but the = null would not work.
select * from person where person_type = 2  
Union
(select * from person where person_type is null and not exists (select 1 from from person where person_type = 2 ))

Query 1 will return the matching rows if person_type = 2 exists.
Query 2 will return null records if no record exists for person_type=2
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
See if this will get what you need.  It will only hit the table once.
drop table tab1 purge;
create table tab1(col1 char(1), person_type number);

insert into tab1 values('a',2);
insert into tab1 values('b',null);
commit;


select col1, person_type
from
(
	select col1, person_type, row_number() over(order by person_type) myrownum
	from tab1
	where person_type=2 or nvl(person_type,1)=1
)
where myrownum=1;

delete from tab1 where person_type =2;

select col1, person_type
from
(
	select col1, person_type, row_number() over(order by person_type) myrownum
	from tab1
	where person_type=2 or nvl(person_type,1)=1
)
where myrownum=1;

Open in new window

select * from person where person_type = (select (case when ((select count(*) from person_type is null) > 0) then null else 2 end) from dual)
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
udaykumar22,

I don't think your syntax is valid for Oracle.

"select count(*) from person_type is null"

Author

Commented:
I like it. Thank-you for you help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.