Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql to return 0 when no row found

Posted on 2009-04-02
2
Medium Priority
?
2,740 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
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

824 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