Replacing ZEROS! - 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

SQL Server Development (2000)

Replacing ZEROS!

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-25 : 14:27:38

How can I test if the value is going to be a ZERO so that I can replace it with a - ?

Thanks


CASE Percentages WHEN 0 THEN REPLACE(YTD *100 / (SELECT COUNT(*) FROM TheTracking as TT1 WHERE
        DATEPART(yy,TT1.Hitdate)=datePart(yy,GetDate())), 0, '-')

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-25 : 14:56:58

I think I'd need to see the entire query, but try something like this:


SELECT 
CASE WHEN
(YTD * 100 / (SELECT COUNT(*) FROM TheTracking as TT1 WHERE
        DATEPART(yy,TT1.Hitdate)=datePart(yy,GetDate())) = 0 
THEN '-' 
ELSE  
YTD * 100 / (SELECT COUNT(*) FROM TheTracking as TT1 WHERE
        DATEPART(yy,TT1.Hitdate)=datePart(yy,GetDate()))
END

Michael

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

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-25 : 16:00:23

I would make a pretty strong argument that this kind of logic belongs on the front end in most cases; transforming data in SQL server for presentation reasons often turns out to be a mistake.

Cheers
-b

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-25 : 16:01:05

quote:

I would make a pretty strong argument that this kind of logic belongs on the front end in most cases; transforming data in SQL server for presentation reasons often turns out to be a mistake.

Cheers
-b

No way it is in the correct tier

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-25 : 16:03:39

quote:

I would make a pretty strong argument that this kind of logic belongs on the front end in most cases; transforming data in SQL server for presentation reasons often turns out to be a mistake.

Cheers
-b

No way it is in the correct tier

r937
Posting Yak Master

112 Posts

Posted - 2002-10-25 : 16:20:00

jesus4u, if you're going to worrying about zeroes, you should handle situations where COUNT(*) is zero, because those won't show at all

although i love sql gymnastics, i must agree that presentation belongs in the front end

rudy

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-25 : 16:21:00

quote:

jesus4u, if you're going to worrying about zeroes, you should handle situations where COUNT(*) is zero, because those won't show at all

although i love sql gymnastics, i must agree that presentation belongs in the front end

rudy

what fromt end? vb? asp? Stored Procs are fast!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-25 : 17:45:07


COALESCE(CAST(NULLIF(YTD *100 / (SELECT COUNT(*) FROM TheTracking as TT1 WHERE
  DATEPART(yy,TT1.Hitdate)=datePart(yy,GetDate())), 0) AS varchar(20)), '-')

Cuts down on the repetition, but it's bloody ugly.
BTW, SELECT CASE WHEN 1=0 THEN 42 ELSE '-' END returns 0.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources