Do I use case or coalesce or something else?

Hi all!
I'm runnnig the following query:
declare @.Action int
set @.Action = 2
SELECT * FROM estates
WHERE
((@.Action!=1)OR(
est_ZipCode BETWEEN 12000 AND 12999 OR
est_ZipCode BETWEEN 14000 AND 14999
))AND
((@.Action!=2)OR(
est_ZipCode BETWEEN 16000 AND 16999
))AND
((@.Action!=3)OR(
est_ZipCode BETWEEN 11000 AND 11999 OR
est_ZipCode BETWEEN 13000 AND 13999 OR
est_ZipCode BETWEEN 15000 AND 15999
))AND
((@.Action!=4)OR(
est_ZipCode BETWEEN 17000 AND 19999
))
and it runs much, much slower then:
SELECT * FROM estates
WHERE est_ZipCode BETWEEN 16000 AND 16999
/*equivalent to action 2*/
Why is that? Is there a better way of solving this, perhaps with case
or coalesce?
Any help appreciated,
NiclasIn order to get the best plan you will either need to use dynamic sql or a
number of if else statements.
Your problem stems from the optimiser not knowing the value of @.Action so it
must optimiser the whole query.
Have you tried UNION ALL, not experimented with that; the dynamic sql would
be the neatest in my opinion but has some requirements for security on the
base tables rather than just exec permission on the stored proc.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
>|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
I agree with Tony in part.
I had problems with one query (that contained a few ANDs and ORs) that was
solved by spliting it in 2 (or more) and using Union (or Union All). Try
this first. If this doesn't work, try writing 4 separate queries handling
each value of @.Action and use a series of IFs.
I would not go the dynamic SQL way.|||Thanks for your responses,
since the query contains about 20 actions and some other conditions
also, I think dynamic SQL sounds like the best solution.
Thanks again,
Niclas
Is there no way to build a switch statement inside the where-clause?|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> Thanks for your responses,
> since the query contains about 20 actions and some other conditions
> also, I think dynamic SQL sounds like the best solution.
> Thanks again,
> Niclas
> Is there no way to build a switch statement inside the where-clause?
No control-of-flow inside a Select statement.
CASE is possible, of course.|||It's a common problem with UI that have a filter option, you need to change
your query according to the parameters the user has filtered on.
Doing it in one big SELECT with CASE or OR's won't give a very good query
plan, often very poor and general.
This is where we need to use either IF ELSE or dynamic SQL, the IF ELSE
route has the problem of duplicate code and if there are lots of filters
then you could end up with 20, 30 or more IF ELSE's which is a lot to
maintain.
There is help at hand with the old dynamic SQL security on base table
requirement in SQL Server 2005 now.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eU7tHJsGGHA.1312@.TK2MSFTNGP09.phx.gbl...
> "tonicvodka" <tonicvodka@.hotmail.com> wrote in message
> news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> No control-of-flow inside a Select statement.
> CASE is possible, of course.
>|||The standard syntax is <> instead of the stolen C syntax != for
non-equal. Your code and the sample you gave do not quite match. I
think that you want somethign like this.
SELECT * -- production code should have column names
FROM Estates
WHERE CASE @.action
WHEN 1
THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
THEN 'T'
WHEN est_zipcode BETWEEN 14000 AND 14999
THEN 'T' ELSE 'F' END
WHEN 2
THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
THEN 'T' ELSE 'F' END
WHEN 3
THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
THEN 'T'
WHEN est_zipcode BETWEEN 13000 AND 13999
THEN 'T'
WHEN est_zipcode BETWEEN 15000 AND 15999
THEN 'T' ELSE 'F' END
WHEN 4
THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
THEN 'T' ELSE 'F' END
etc.
ELSE 'F' END = 'T';
The CASE expression checks the WHEN clauses in order, so put the most
likely case first. This applies to the intermost WHENs also. I have
used both forms of the CASE expression and nested them, but you might
want to look up the syntax so you feel comfortable with it. The 'T'
and 'F' stand for True and False. It is a common SQL porgramming trick
for complex logic. You might also want to look at Logic Gem or another
decision table tool that will help with this kind of problem.
This lets the optimizer do its job on a single query, without having to
kludge dynamic SQL, use UNIONs or resort to procedural code.|||>> In order to get the best plan you will either need to use dynamic SQL or
a number of if else statements. <<
You really are trapped in an OO and 3GL mindset and cannot think
declaratively!
I used to tell students that it takes a year of SQL programming to have
the"declarative revelation" so that you stop thinking in terms of
dynamic on-the-fly coding, IF-THEN_ELSE control flows, etc. and just
write a single query.
I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
a fine job.|||> I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
> a fine job.
No it does not, and you just don't get it. In the real world "one size
fits all" approach (just write a single query) may perform very poorly.
Just think: consider a simple query
select <some columns> from some_table where some_date between
@.date_from and @.date_to
If there is a ( non-clustered for SQL Server) index on some_date, which
plan should the optimizer choose? It could scan the table (tablespace
scan in DB2 parlance), or it could access the table through the index.
Neither plan is the best for all the cases. Recompiling the query makes
perfect sense if teh table is big.
just think: consider a search form with several search conditions for
the user to fill, like zip, last name, fisrt name, whatever. Dynamic
SQL is most likely to run much better that generic stufff like
where zip=@.zip or @.zip is null
and last_name=@.last_name or @.last_name is null
and the reason is simple: all real life major RDBMS are well capable of
choosing different plans for diffrent parameter values, DB2 included.
For big tables the performance price of choosing only one plan for all
the cases may be way too steep.
So yes, in simpler cases we have if statements, in more complex ones we
just go for dynamic SQL.|||On 16 Jan 2006 10:31:21 -0800, --CELKO-- wrote:

>The standard syntax is <> instead of the stolen C syntax != for
>non-equal. Your code and the sample you gave do not quite match. I
>think that you want somethign like this.
>SELECT * -- production code should have column names
> FROM Estates
> WHERE CASE @.action
> WHEN 1
> THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
> THEN 'T'
> WHEN est_zipcode BETWEEN 14000 AND 14999
> THEN 'T' ELSE 'F' END
> WHEN 2
> THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
> THEN 'T' ELSE 'F' END
> WHEN 3
> THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
> THEN 'T'
> WHEN est_zipcode BETWEEN 13000 AND 13999
> THEN 'T'
> WHEN est_zipcode BETWEEN 15000 AND 15999
> THEN 'T' ELSE 'F' END
> WHEN 4
> THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
> THEN 'T' ELSE 'F' END
> etc.
> ELSE 'F' END = 'T';
>The CASE expression checks the WHEN clauses in order, so put the most
>likely case first. This applies to the intermost WHENs also. I have
>used both forms of the CASE expression and nested them, but you might
>want to look up the syntax so you feel comfortable with it. The 'T'
>and 'F' stand for True and False. It is a common SQL porgramming trick
>for complex logic. You might also want to look at Logic Gem or another
>decision table tool that will help with this kind of problem.
>This lets the optimizer do its job on a single query, without having to
>kludge dynamic SQL, use UNIONs or resort to procedural code.
Hi Joe,
But the single query tha the optimizer gets to do its job on doesn't
have any comparison predicate of the form
"<column name> <comp op> <expression>"
or "<expression> <comp op> <column name>".
The only thing the optimizer can do is to sigh and settle for a complete
table scan.
Using 4 SELECT statements and an IF ELSE tree or (shudder) dynamic SQL
would enable the optimizer to check if an index on est_zipcode can be
sed. That would result in faster execution.
Hugo Kornelis, SQL Server MVPsql

do I need to pay for support cases to help MS fix bugs?

I've filed a few bugs on connect.microsoft.com, and I am being asked for 2nd time to open a support case to make it easier to work on the bug.

The reason is that the bug needs to be reproduced, and working with a support person will help MS setup the same database, cube, etc.

However, I am wondering why am I as a customer required to pay for the support incidents to help Microsoft fix software bugs?

In my view that type of practice might serve as a deterrent for bug filing and follow-ups. I am sure this is not the desired effect.

I have filed many bugs on connect without any demands of opening a support case.

The response is usually rapid if you participate in beta testing of service packs.

Probably you pay for a quick delivery of hot-fixes.

HTH

Thomas Ivarsson

|||

No, you pay only if you support case wasn't MS error.

If you file an issue that is a bug, you pay nothing. For the hotfix you pay nothing. But the way from bug support case to the hotfix is some time long and difficult.

Do a CASE with a expression

I know i can do a "IF" using expressions, the question is, can i do a CASE?

Thanks!

Albertoim,

It doesn't appear that there is a case statement in the expression syntax. Note, however, that depending on what you would like to do the Conditional split can be used in such a fashion (where you would then have another derived column for each of your cases and then a union all). And, of course, you can nest the if / else statements to mimic the case statement (although it looks awful and begins to be a bit difficult to maintain if you start to get to many cases).

If you do a bit of a search around the forums...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=72088&SiteID=1|||

I would rather do my CASE expressions in SQL if there are many of them. The Dervied Column expression box cannot be expanded and it can get confusing even for a moderately complex logic.

DML error logging

Oracle 10g2 offers DML error logging, which enables to load data with traditional SQL without having a complete roll-back in case one record is refused. http://orafaq.com/node/76

This is almost similar to loading capabilities of ETL-tools (at least in the error-handling department)

Does anyone have a clue whether Microsoft is going to add such functionality to SQL Server 2005?

I think you have that in SSIS, (almost 99% sure, so ask over there in their forum too http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1) but depending on the situation, you can do this easily in an instead of trigger, if you know the criteria to check for. Just something like the following in the insert trigger will do:

insert into exceptions (columns)
select columns
from inserted
where <bad data check>

insert into real table (columns)
from inserted
where not <bad data check>

This might be a solution for a table where the user is doing heads down keying of data.

Another alternative is to do the same thing in a procedure, if you are doing single row edits by using a TRY...CATCH block and inserting into the exception table on error.

Will they add something like this to 2005, no. But the next version? Perhaps, go here: https://connect.microsoft.com/SQLServer/Feedback and voice your opinion/idea for solution. If you do, post back here with the URL and request votes.

Division by zero

Hi, I have a field in which I use a division. Sometimes the denominator can
be zero. In such case, I want to return 0 instead of evaluating the division
giving me an error.
Here is what I attempted but without success. It appears that both
expressions are evaluated all the time regardless of the result of the
condition.
=iif( Fields!Denominator.Value = 0, 0, Fields!Numerator.Value/
Fields!Denominator.Value)
I would like to know if someone knows a work around to this.
Thanks,
JoeI finally found a way to do it:
=((Fields!Numerator.Value / iif( Fields!Denominator.Value = 0, 1,
Fields!Denominator.Value)) * iif( Fields!Denominator.Value = 0, 0, 1))|||Thats the way I was doing it. But if you have a bunch of these its
gets tedious and I would recommend writing a custom assembly that does
this instead.
Abe
joerage wrote:
> I finally found a way to do it:
> =((Fields!Numerator.Value / iif( Fields!Denominator.Value = 0, 1,
> Fields!Denominator.Value)) * iif( Fields!Denominator.Value = 0, 0,
1))

Divide by zero error trapping

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacrifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

Realistically an error such as this could happen at a few points in
the expression (or one of many others), so I need to find a way of
catching any error in the expression and setting the return value to
0. I thought of using a CASE statement, but wondered if there was a
better way of looking at this as the case statement would have to
check each variation where it could throw an error.

Any ideas ?

Thanks

RyanRyan (ryanofford@.hotmail.com) writes:
> I have the following line in a select statement which comes up with a
> divide by zero error.
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> (m.Gross+m.Sacrifice)
> ELSE 0
> END
> END AS Float)AS Bond2,
> The error happens on the section (m.Gross + m.Sacrifice) as this can
> equal zero and throws out the part of the calc that divides by it. It
> is correct in some instances that it does so. The full SQL statement
> has a large number of these expressions so I need a method I can apply
> to any line if possible.
> I know that it is mathmatically correct to error where this value is
> zero, but what I want to do is set the output of the entire expression
> to zero if there is an error.

SQL Server does happen to other some alternatives in this case, but I would
strongly recomment that you have something like:

ELSE CASE WHEN Sacrifice>=1 AND m.Gross+m.Sacrifice <> 0
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacrifice)
ELSE 0
END

The alternatives is to set ANSI_WARNINGS OFF, ARITHABORT OFF and
ARITHIGNORE ON. In this case, SQL Server will silenly set the result to
NULL, which you then would have to apply coalesce to get a 0. But since
these settings are not compatible with indexed views and indexed
computed columns, you can get other problems, and overall it is, in
my opinion, an obscure way of doing things.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Probably the easiest way to accomplish what is you is:

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
3*m.Premium/100)/
NULLIF(m.Gross+m.Sacrifice,0) ),0)
ELSE 0
END
END AS Float)AS Bond2,

Hope this helps,
Gert-Jan

Ryan wrote:
> I have the following line in a select statement which comes up with a
> divide by zero error.
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> (m.Gross+m.Sacrifice)
> ELSE 0
> END
> END AS Float)AS Bond2,
> The error happens on the section (m.Gross + m.Sacrifice) as this can
> equal zero and throws out the part of the calc that divides by it. It
> is correct in some instances that it does so. The full SQL statement
> has a large number of these expressions so I need a method I can apply
> to any line if possible.
> I know that it is mathmatically correct to error where this value is
> zero, but what I want to do is set the output of the entire expression
> to zero if there is an error.
> Realistically an error such as this could happen at a few points in
> the expression (or one of many others), so I need to find a way of
> catching any error in the expression and setting the return value to
> 0. I thought of using a CASE statement, but wondered if there was a
> better way of looking at this as the case statement would have to
> check each variation where it could throw an error.
> Any ideas ?
> Thanks
> Ryan

--
(Please reply only to the newsgroup)|||Works perfectly thank you !

R

Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<41251949.4D0CF7B3@.toomuchspamalready.nl>...
> Probably the easiest way to accomplish what is you is:
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
> 3*m.Premium/100)/
> NULLIF(m.Gross+m.Sacrifice,0) ),0)
> ELSE 0
> END
> END AS Float)AS Bond2,
> Hope this helps,
> Gert-Jan
>
> Ryan wrote:
> > I have the following line in a select statement which comes up with a
> > divide by zero error.
> > CAST(CASE Splinter_Status
> > WHEN 'SUR' THEN 0
> > ELSE CASE WHEN Sacrifice>=1
> > THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> > (m.Gross+m.Sacrifice)
> > ELSE 0
> > END
> > END AS Float)AS Bond2,
> > The error happens on the section (m.Gross + m.Sacrifice) as this can
> > equal zero and throws out the part of the calc that divides by it. It
> > is correct in some instances that it does so. The full SQL statement
> > has a large number of these expressions so I need a method I can apply
> > to any line if possible.
> > I know that it is mathmatically correct to error where this value is
> > zero, but what I want to do is set the output of the entire expression
> > to zero if there is an error.
> > Realistically an error such as this could happen at a few points in
> > the expression (or one of many others), so I need to find a way of
> > catching any error in the expression and setting the return value to
> > 0. I thought of using a CASE statement, but wondered if there was a
> > better way of looking at this as the case statement would have to
> > check each variation where it could throw an error.
> > Any ideas ?
> > Thanks
> > Ryan

Divide by Zero error

With the following sql I get a divide by zero error. Can somebody help me
with the syntax to fix this?
Thanks in advance
SUM(CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) < (m.SecondaryEERSpec -
(-1 * (@.LowerSpec * m.SecondaryEERSpec))) OR
(p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@.UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END) as SecondaryEERFailure
sHow about checking for p.SecondaryWatts first?
SUM(
CASE WHEN (p.SecondaryWatts =0) THEN -1
WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
(m.SecondaryEERSpec - (-1 * (@.LowerSpec * m.SecondaryEERSpec)))
OR
(p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@.UpperSpec * m.SecondaryEERSpec))
THEN 1 Else 0 END
) as SecondaryEERFailures
"StvJston" wrote:

> With the following sql I get a divide by zero error. Can somebody help me
> with the syntax to fix this?
>
> Thanks in advance
> SUM(CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) < (m.SecondaryEERSpec
-
> (-1 * (@.LowerSpec * m.SecondaryEERSpec))) OR
> (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
> (@.UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END) as SecondaryEERFailu
res
>|||Kevin,
Thanks for your reply. I just got it figured out and did just what you
suggested.
SUM(CASE WHEN p.SecondayWatts = 0 THEN 0
ELSE CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
(m.SecondaryEERSpec - (-1 * (@.LowerSpec * m.SecondaryEERSpec))) OR
(p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@.UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END END) as
SecondaryEERFailures,
"Kevin Bowker" wrote:
> How about checking for p.SecondaryWatts first?
> SUM(
> CASE WHEN (p.SecondaryWatts =0) THEN -1
> WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
> (m.SecondaryEERSpec - (-1 * (@.LowerSpec * m.SecondaryEERSpec)))
> OR
> (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
> (@.UpperSpec * m.SecondaryEERSpec))
> THEN 1 Else 0 END
> ) as SecondaryEERFailures
> "StvJston" wrote:
>

Divide by zero

In the following query I sometimes have DIVIDE BY ZERO issues. How can I
put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query so
that I don't get the error.
SUM(MedExp) is sometimes = 0
SUM(TotalMonths) is sometimes = 0
SUM(TotalMem) is sometimes = 0
I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
by" (one of the three above) is zero?
Here is the query. Any help would be great.
SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmpm,
(SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem descTry,
SELECT
MCO,
SUM([Pharmacy Exp]) AS phexp,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(MedExp), 0)), 0) as percentof,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMonths), 0)), 0) AS pmpm,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMem), 0)), 0) AS permem
from
sheet1$ where medexp <> ''
and totalmem <> ''
and period = '3Q04'
AND (
domicile LIKE '%ct%'
OR domicile LIKE '%ma%'
OR domicile LIKE '%nh%'
OR domicile LIKE '%me%'
OR domicile LIKE '%ri%'
OR domicile LIKE '%vt%'
OR domicile LIKE '%ny%'
OR domicile LIKE '%nj%'
)
GROUP BY
MCO
order by
permem desc;
AMB
"William" wrote:

> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query s
o
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmp
m,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>
>|||Try this
SELECT MCO, SUM([Pharmacy Exp]) AS phexp,
(CASE WHEN SUM(MedExp) > 0 THEN SUM([Pharmacy Exp]) /SUM(MedExp) ELSE
SUM([Pharmacy Exp]) END ) as percentof,
(CASE WHEN SUM(TotalMonths) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMonths)
ELSE SUM([Pharmacy Exp]) END ) AS pmpm,
(CASE WHEN SUM(TotalMem) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMem) ELSE
SUM([Pharmacy Exp]) END ) AS permem,
from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem desc
Thank you
Baiju
"William" <da@.northernit.net> wrote in message
news:VPb4e.1793$uw2.417@.twister.nyroc.rr.com...
> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query
so
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS
pmpm,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>

divide by 0

dear all,
hi, i got this problem - Divide by zero error encountered.

can someone please help me and this is the code

--cast(
Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)
--as numeric(3,2))

thank you very much indeed.

regards,
Catcycyou should drop a Case clause in which will execute in place of these when the value you're using is zero, OR you should filter the data so you don't get zeroes in the first place.|||Or if you just want to suppress the error, use

SET ARITHABORT ON | OFF|||dear Dutch,
i've use the codes that you recommended but still cannot generate the thing that i want.

this is my first time to generate report that calculate percentage by using sql statement & also the hard part is to check how many pending case within certain period as below code :

Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)

i was thinking can i use ifelse statement to check before the above code to prevent this error. But i'm not really familiar with the usage of if else statement in sql.

so, can you teach me how to use or anyone can show me.

regards,
Catcyc