If no row found return NULL - 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

General SQL Server Forums

New to SQL Server Programming

If no row found return NULL

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 12:05:29

Hi friends,

I have a table named as rating,

i am using the below Query

select Points from rating where refid=4 and userid=6

So if there is no row found i need to return the points as 0(Zero)

Please help me to get this result..

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:07:53

select sum(Points) from rating where refid=4 and userid=6

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 12:09:08

Thanks for your reply

If it has value i have to return the actual value of the POINTs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:11:26

no problem. but will there be a chance that there can be more than 1 row existing for passed values? in such cases wht you want? individual points or sum of points?

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 12:13:19

there is no possible for having more than one 1 row visakh, So if there is no row i need to return the o , if it has any value i need to return the value..

Thanks for everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:21:10

quote:
Originally posted by dhinasql

there is no possible for having more than one 1 row visakh, So if there is no row i need to return the o , if it has any value i need to return the value..

Thanks for everything

then my previous query will do

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 12:24:27

quote:
Originally posted by dhinasql

Hi friends,

I have a table named as rating,

i am using the below Query

select Points from rating where refid=4 and userid=6

So if there is no row found i need to return the points as 0(Zero)

Please help me to get this result..

Thanks in Advance

Visakh thanks,

if no row found i need to return 0 , not NULL

Sorry in the title i have asked wrongly..

But i need Zero 0 , if no row found

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:28:35

select coalesce(points,0)
from
(select points from rating where refid=4 and userid=6)t

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-14 : 12:31:39

select isnull(Points,0) from rating where refid=4 and userid=6

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 13:33:59

Hi Friends,

I tried with your query but it does not returning any value..

When no rows found i need to display 0 for points, else i have to display the value of points.

Sample data

refid , userid , points
4 16 3

For this expected output is

Points
3

Suppose the table have the value like

refid , userid , points
4 20 3

The expected output is

Points
0

Please help me

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-14 : 14:09:58

They seem to work just fine, is there some other requirement?

DECLARE @Yak TABLE (refid int, userid int, points int)

INSERT @Yak
SELECT 4, 6, 3

SELECT COALESCE(SUM(points), 0) AS Points
FROM @Yak
WHERE refid = 4 AND userid = 99

SELECT COALESCE(points, 0) AS points
FROM
(
	SELECT SUM(points) AS points
	FROM @Yak
	WHERE refid = 4 AND userid = 99
) AS T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 23:41:15

quote:
Originally posted by dhinasql

Hi Friends,

I tried with your query but it does not returning any value..

When no rows found i need to display 0 for points, else i have to display the value of points.

Sample data

refid , userid , points
4 16 3

For this expected output is

Points
3

Suppose the table have the value like

refid , userid , points
4 20 3

The expected output is

Points
0

Please help me

show your actual query please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources