asked on

SQL help: if not found return default type

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

nishant joshi

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

OP_Zaharin

- 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
);

rmtdev

ASKER

Thanks OP_Zaharin, that is more of what i'm after, but the = null would not work.

jaiminpsoni

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

udaykumar22

select * from person where person_type = (select (case when ((select count(*) from person_type is null) > 0) then null else 2 end) from dual)

slightwv (䄆 Netminder)

udaykumar22,

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

"select count(*) from person_type is null"

rmtdev

ASKER

I like it. Thank-you for you help.