Easy Question but I'm stuck - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Easy Question but I'm stuck

Author

Topic

Ned
Starting Member

16 Posts

Posted - 2003-12-11 : 19:01:35

Hi,
I want to return a "0" value from a table when the record does not exist, but I'm stuck on how to do it.


SELECT 
	CASE 
		WHEN itemValue IS NULL THEN 0
		ELSE itemValue
	END
FROM 
	MyTable
WHERE 
	ItemID = 'NoIDWithThisValue'

All I get is 0 rows affected. I want it to return the value 0

Ned
Starting Member

16 Posts

Posted - 2003-12-11 : 19:17:56

OK, this works but why do I need to put the orginal question in a sub query? I'm a little confused about NULL.


SELECT 
	CASE 
		WHEN DT.itemValue IS NULL THEN 0
		ELSE DT.itemValue
	END AS ItemValue
FROM 
(
    SELECT
        (SELECT itemValue FROM MyTable WHERE ItemID = 'NoIDWithThisValue') AS itemValue

) AS DT

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-11 : 19:24:15

Your original query couldn't display anything because your set returned zero rows.
You are usually better off doing something like this in the display layer. Nice solution though.

Damian

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-11 : 19:24:25

I think you'll need to use COALESCE() to handle this.
How about this:



SELECT COALESCE(itemValue, 0) AS itemValue
FROM MyTable
WHERE ItemID = 'NoIDWithThisValue'

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Ned
Starting Member

16 Posts

Posted - 2003-12-11 : 19:32:52

Thanks for the help but coalesce doesn't work either (that was my second attempt at the problem).

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-11 : 19:50:25

Michael is onto something, it needs a little hacking though. You can also use IsNull in place of coalesce.

SELECT IsNull(Max(itemValue), 0) as itemValue
FROM MyTable
WHERE ItemID = 'NoIDWithThisValue'

Damian

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-11 : 20:03:33

Another Method:

IF EXISTS (SELECT 1 FROM MyTable WHERE ItemID = 'NoIDWithThisValue') 
SELECT 1
ELSE
SELECT 0

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-11 : 20:08:16

quote:

Another Method:

IF EXISTS (SELECT 1 FROM MyTable WHERE ItemID = 'NoIDWithThisValue')
SELECT 1
ELSE
SELECT 0

That won't return the value though, just a 1

You would need to do :

IF EXISTS (SELECT 1 FROM MyTable WHERE ItemID = 'NoIDWithThisValue')
SELECT itemValue FROM MyTable WHERE ItemID = 'NoIDWithThisValue'
ELSE
SELECT 0

Which seems kinda unnecessary.

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-11 : 20:22:39

[code]
SELECT CASE COUNT(*) WHEN 0 THEN '0' ELSE MAX(itemValue) END AS itemValue
FROM MyTable
WHERE ItemID = 'NoIDWithThisValue'
[/code]

EDIT: Casting data types...

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-11 : 20:24:44

Are we not concerned with the possibility of multi-row matches or is it assumed that itemid is unique??

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-11 : 20:29:10

I assumed that ItemID would be unique in a table storing "ItemValue"
But you are right, assumpumtions make an ass out of you and umption