Return null when 0 row is selected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return null when 0 row is selected

Author

Topic

unicorn23
Starting Member

3 Posts

Posted - 2008-04-01 : 18:54:52

Hi all,
I have the situation below. I can't use dual. Thank you for your time.

Example Query:
select name
from employee
where id=1;

Result:
0 rows selected

Wanted Result:
1 row selected

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 18:55:58

IF NOT EXISTS (select * from employee where id=1)
..put something here

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

unicorn23
Starting Member

3 Posts

Posted - 2008-04-01 : 19:48:33

Hi

Thanks for the reply. :)

I can only use simple sql(result should be from one select query).I do not think I can use IF EXISTS. Sorry for not clarifying earlier.

I can the idea of using count, so it count is 0, assign the value to null. However, the syntax is wrong as I cant put count in decode.

SQL Error: ORA-00937: not a single-group group function

Any pointers are appreciated

select decode(count(employee_id), 0, null, employee_id) from
(select employee_id
from employees
where employee_id=1);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 20:02:26

Looks like you are using Oracle, so you'd be better off posting your question in an Oracle forum such as the one over at dbforums.com

This site is for Microsoft SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

unicorn23
Starting Member

3 Posts

Posted - 2008-04-01 : 20:10:36

Hi tkizer,
Thanks for the fast replies! :)

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 22:38:56

Think you want a single row with a null entry if nothing is found
select coalesce(a.name, b.name)
from
(select name = convert(varchar(100),null)) as a
left join
(select name from employee where id=1) as b
on 1=1

Will have to change that a bit for oracle but the concept should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources