SQL Server : ISNULL not returning a value [duplicate]

Question

I'm trying to write a SQL Statement (in Microsoft SQL Server) that will always return a value of 0 if there is nothing to be found.

SELECT 
    ISNULL([ID], 0) AS ID 
FROM 
    ecdb.dbo.tbl_units 
WHERE 
    ([GENESYS_NAME] = 'EDRD');

The statement above returns an empty value (nothing), see screenshot.

Now if I enter a true statement that returns a value (replace 'EDRD' with 'ERD'), it returns a value of 1, which is correct.

The reason why I need it to return a value of zero is that I plan to use this statement as a sub query of a master statement where I need the ID (not the GENESYS_NAME) and my table has set aside 0 to be an unknown entry.

Any help?

You are mistaken. Your query returns no rows. Therefore, there is no NULL value on which ISNULL can operate. If you plan to use it as a subquery, you simply wrap the subquery in ISNULL. The problem with your approach is that you focus on the wrong part - show a complete query that more closely resembles your actual goal to get better suggestions. — SMor
– SMor, Commented May 15, 2020 at 20:03

forpas · Accepted Answer · 2020-05-15 19:39:03Z

2

If you are expecting only 1 row as result you can use an aggregate function like MAX():

SELECT ISNULL(MAX([ID]), 0) AS ID 
FROM ecdb.dbo.tbl_units 
WHERE ([GENESYS_NAME]='EDRD');

Another way to do it, which will work also if you expect more than 1 rows in the results is with UNION ALL:

SELECT [ID]
FROM ecdb.dbo.tbl_units 
WHERE ([GENESYS_NAME]='EDRD')
UNION ALL
SELECT 0
WHERE NOT EXISTS (
  SELECT 1 FROM ecdb.dbo.tbl_units 
  WHERE ([GENESYS_NAME]='EDRD') 
)

edited May 15, 2020 at 19:39

answered May 15, 2020 at 19:30

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sanya Over a year ago

Interesting. So your code returned NULL but when I wrap it is an ISNULL, then it actually returns a 0, which is what I need.

forpas Over a year ago

Correct. MAX(id) should be wrapped inside is null.

spencer7593 Over a year ago

the trick is that with the aggregate function, we will get a row back, consider return from SELECT COUNT(*) FROM ... when no rows satisfy the conditions, we get a row back with zero. Contrast this with SELECT id FROM ... when no rows satisfy the conditions, we get an empty resultset, zero rows returned.

forpas Over a year ago

@spencer7593 this is the requirement: to get 1 row.

Collectives™ on Stack Overflow

SQL Server : ISNULL not returning a value [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related