How can I test for divide by zero without using CASE?

Since the query builder (not the query analyzer) in enterprise manager does
not support case statments, I was wondering if there's a way to structure
the following in a different way (without case statements) so that the query
builder works. I thought there might be a way to do soemthing with NULLIF
and/or COALESCE but I got stuck.
CASE WHEN x = 0 THEN 0 ELSE y/x END AS AvgCustBalance
Thanks,
Keith> Since the query builder (not the query analyzer) in enterprise manager
> does
> not support case statments, I was wondering if there's a way to structure
> the following in a different way (without case statements) so that the
> query
> builder works.
Sure, don't use the query builder. Why do you want to force yourself to use
a tricycle to commute to work, when there's a Ferrari in your driveway?|||> Since the query builder (not the query analyzer) in enterprise manager
> does
> not support case statments, I was wondering if there's a way to structure
> the following in a different way (without case statements) so that the
> query
> builder works. I thought there might be a way to do soemthing with NULLIF
> and/or COALESCE but I got stuck.
> CASE WHEN x = 0 THEN 0 ELSE y/x END AS AvgCustBalance
How about :
WHERE x <> 0
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Well, that will leave rows out. I wonder if something like this will work:
SELECT COALESCE(y/NULLIF(x,0),0)
FROM
(SELECT x = 1,y=5
UNION ALL SELECT x=2,y=5
UNION ALL SELECT x=0,y=5) z
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uDUnLsoyFHA.904@.tk2msftngp13.phx.gbl...
> How about :
> WHERE x <> 0
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||Could you tell me what tool you are referring to as a Ferrari?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ue40XqoyFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Since the query builder (not the query analyzer) in enterprise manager
> does
> not support case statments, I was wondering if there's a way to structure
> the following in a different way (without case statements) so that the
> query
> builder works.
Sure, don't use the query builder. Why do you want to force yourself to use
a tricycle to commute to work, when there's a Ferrari in your driveway?|||If one is a todler, then it makes perfect sense. ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ue40XqoyFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Sure, don't use the query builder. Why do you want to force yourself to
> use a tricycle to commute to work, when there's a Ferrari in your
> driveway?
>|||Query Analyzer, or a host of third party tools. Enterprise Manager is
definitely not the place to write code, view data or manage objects.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e3R06DpyFHA.2960@.tk2msftngp13.phx.gbl...
> Could you tell me what tool you are referring to as a Ferrari?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:ue40XqoyFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Sure, don't use the query builder. Why do you want to force yourself to
> use
> a tricycle to commute to work, when there's a Ferrari in your driveway?
>
>|||Keith,
Try (as test):
--test for /0 error without using case
CREATE TABLE YX
(Y INT NOT NULL,
X INT NOT NULL)
INSERT YX
VALUES(10,1)
INSERT YX
VALUES(10,0)
INSERT YX
VALUES(10,2)
SELECT Y,X,COALESCE(Y/NULLIF(X,0),0) AS 'Y/X'
FROM YX
HTH
Jerry
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:exTFjnoyFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Since the query builder (not the query analyzer) in enterprise manager
> does
> not support case statments, I was wondering if there's a way to structure
> the following in a different way (without case statements) so that the
> query
> builder works. I thought there might be a way to do soemthing with NULLIF
> and/or COALESCE but I got stuck.
> CASE WHEN x = 0 THEN 0 ELSE y/x END AS AvgCustBalance
> Thanks,
> Keith
>|||This is good, thanks. I didn't realize that 5/null = null. Guess that should
have been obvious.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uREka%23oyFHA.3812@.TK2MSFTNGP09.phx.gbl...
SELECT COALESCE(y/NULLIF(x,0),0)
FROM
(SELECT x = 1,y=5
UNION ALL SELECT x=2,y=5
UNION ALL SELECT x=0,y=5) z|||As far as I understand QA does not have a feature that lets you build
queries in a visual way like you can in EM. I'm a very visually oriented
person and prefer to work in that type of an environment when I can. I
generally use Database workbench for most of my SQL work and I'm very happy
with that. But the vsiual query builder is far from complete. If you don't
mind listing some of the "host of third party tools" I'd find that a bit
more helpful.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsN1zGpyFHA.2076@.TK2MSFTNGP14.phx.gbl...
Query Analyzer, or a host of third party tools. Enterprise Manager is
definitely not the place to write code, view data or manage objects.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e3R06DpyFHA.2960@.tk2msftngp13.phx.gbl...
> Could you tell me what tool you are referring to as a Ferrari?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:ue40XqoyFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Sure, don't use the query builder. Why do you want to force yourself to
> use
> a tricycle to commute to work, when there's a Ferrari in your driveway?
>
>