Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have a table like

id=number phno=number
1 9999
2 8888

select id from table where phno=7777

if the phone number is not available in table it will show empty
id

but i want
0

thankx
Posted
Updated 4-Mar-15 18:15pm
v3

If I understand the question correctly, you would like the query to return a single row having ID equal to zero if a record is not found.

However, this is not how SQL is designed. In case the query doesn't find any matches the result set is returned as empty, which is actually good information. This way you know that no rows satisfied the condition.

So instead of using 'magic numbers' in the result data check if the query returns data or not.

Or perhaps I misunderstood the question incorrectly?
 
Share this answer
 
Mikas answer is the correct one but probably not what you wanted.

There's a slight problem though, you have stated in your tags that you're using SQL Server 2008, while in the subject line it says Oracle.

And normally for Oracle you'd use the NVL() function, but for SQL Server the same functionality would be achieved with ISNULL().

So to be generic I will answer with COALESCE().
SQL
select COALESCE(id,0) from table where phno=7777


But please do consider doing it the proper way instead, it might make a big difference in the future when you need to refactor the code you're writing at the moment
 
Share this answer
 
select count(id) where phno=7777
 
Share this answer
 
Comments
Jörgen Andersson 5-Mar-15 1:48am    
And what result would you get for select count(id) from table where phno=8888?
Parazival 5-Mar-15 4:08am    
1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900