Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql to return 0 when no row found

Posted on 2009-04-02
2
Medium Priority
?
2,738 Views
Last Modified: 2012-05-06
I like the following select to return 0 when no row found.  Thanks.
select emp_id from emp where emp_id =30;

emp_id  
10
20
0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Assisted Solution

by:cyberstalker
cyberstalker earned 600 total points
ID: 24051399
If you want to only ever return one row, you can do it like this:

Using MAX will return the maximum value in a set (which will be the same as your original query, or null if no rows are found. IFNULL returns the first non-null value. Which is the result of MAX, or the 0 passed as second parameter.
SELECT IFNULL(MAX(emp_id), 0) AS emp_id FROM emp WHERE emp_id =30;

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1400 total points
ID: 24051720
what are you actually trying to do?

Select coalesce(emp_id,0) as empid
 from sysibm.sysduymmy1 as x
 left outer join (Select 'Y' as Y,emp_id from emp where emp_id=30) as Y
   on X.Y=Y.Y

will return 0 when the emp_id doesn't exist...

are you really looking for the WHERE EXISTS / NOT EXISTS syntax?


0

Featured Post

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question