zero value - 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

zero value

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 06:19:26

When the results is zero, it shows as blank. Is there a way to make it show as zero ("0")?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 06:24:23

Post sample data and expected ouput.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 06:25:59

WHERE is the result displayed as blank?

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 06:42:40

that's when running sql. example sum(data) - sum(data)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 06:52:45

Can never be displayed as BLANK in either QA nor SSMS.
Please specify what you mean by BLANK. Is it NULL?

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 06:54:58

It's really blank because the result is zero "0"

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 06:55:31

i think, this can be used...
case when sum(data) - sum(data)='0' then 0
else sum(data) - sum(data) end

but even without this case statement, the answer should be 0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 06:57:43

Where the result is displayed as blank? In Front-End application? In Report? Which tool you are using to show the output of this query?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 06:59:48

I'm using Dreamweaver. That's the case if the result is blank, an error occurs in DW.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 07:02:38

What is the output of the same query if you run it from Query Analyzer or Management studio?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 07:10:01

it's blank

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 07:12:52

Post your table sample data and full query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 07:18:21

SELECT SUM(CalibScoreAVGCOACH) / COUNT(CalibScoreAVGCOACH) AS OverallAverage
FROM dbo.TechPhonesCalibQACoachAverage
WHERE (Reviewdate >= '5 /4/2008') AND (Reviewdate <= ' 5 / 10 / 2008 ')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 07:23:57

Try this:

SELECT coalesce(SUM(CalibScoreAVGCOACH) / COUNT(CalibScoreAVGCOACH), 0) AS OverallAverage
FROM dbo.TechPhonesCalibQACoachAverage
WHERE (Reviewdate >= '5 /4/2008') AND (Reviewdate <= ' 5 / 10 / 2008 ')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 07:39:24

I think the problem is with DW. Everytime the result is 0 i always get this error message: An error occurred on the server when processing the URL.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 07:42:44

SELECT AVG(CalibScoreAVGCOACH) AS OverallAverage
FROM dbo.TechPhonesCalibQACoachAverage
WHERE Reviewdate >= '20080504' AND Reviewdate < '20080511'

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 07:43:56

Just now, you said that you are getting result as BLANK in DW as well as Query Analyzer. Then how do you know the result is 0?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 07:47:22

It's blank and not zero. I tried your formula and I got the result of zero but still the error occurs in DW.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 08:20:23

Please post relevant sample data for table TechPhonesCalibQACoachAverage.
Also post your expected output.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-08 : 04:13:48