Divide by zero

1. Member
Join Date
Dec 1969
Posts
70

## Divide by zero

How do I rap around a CASE statement to prevent x/0 to happen:<BR><BR>CASE WHEN T.Visits = 0 OR YE.Visits = 0 THEN 0 ELSE<BR>SELECT<BR>SUM(T.Nights)/SUM(T.Visits) as [ALOS QTR],<BR>SUM(YE.Nights)/SUM(YE.Visits) as [ALOS YE]<BR>FROM.......<BR><BR>If T.Visits = 0 OR YE.Visits = 0 is true then I want to select the values 0 and 0.<BR><BR><BR>It is something like:<BR><BR>CASE WHEN T.Visits = 0 OR YE.Visits = 0 THEN 0,0<BR>ELSE<BR>SUM(T.Nights)/SUM(T.Visits) as [ALOS QTR],<BR>SUM(YE.Nights)/SUM(YE.Visits) as [ALOS YE]<BR>END

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## No, EACH element...

...of the SELECT has to be complete in and of itself.<BR><BR>You have to realize that, despite is funny format, CASE WHEN is *really* just a FUNCTION that can return one and only one value.<BR><BR>SO:<BR><BR>SELECT ... <BR> CASE THEN T.Visits=0 THEN 0 ELSE SUM(T.Nights)/SUM(T.Visits) END AS [ALOS QTR],<BR> CASE THEN YE.Visits=0 THEN 0 ELSE SUM(YE.Nights)/SUM(YE.Visits) END AS [ALOS YE],<BR> ...<BR><BR>Do you see that? The *RESULT* of using CASE WHEN must be a single value (a number, string, datetime, etc.). As you wrote the code, you&#039;d be trying to return *two* values. Nope.<BR><BR>

3. Member
Join Date
Dec 1969
Posts
70

## Thank you kindly. <eom>

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•