Solved

sql to return 0 when no row found

Posted on 2009-04-02
2
2,730 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 150 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 350 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do identify a "break in coveage" 8 174
Guide to DB2 SQL pl programming 3 209
iSeries DB2 - Query with Sub Query? 7 103
Best cheaper way to convert to not editable document 6 41
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 (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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now