Division not working

The following yields strange results
select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
([FieldB]>=1)
sample output is
FieldA FieldB FieldC
4 11 0
3 4 0
3 4 0
5 12 0
5 12 0
4 5 0
Addition, subtraction and multiplication all work properly, but division
isn't. Is there a flag somewhere in SQL that needs to be set for division to
work?
ThanksInteger division yield integer result. try casting one of the columns
involved in the expression to a datatype with greater precedence.
select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
TableA
where ([FieldB]>=1)
AMB
"mike" wrote:

> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
to
> work?
> Thanks|||Hi
Results are fine if the columns are integer datatype. 4/11 = 0 in integer
division
select
FieldA,
FieldB,
(CONVERT(DECIMAL(18,2), FieldA) / CONVERT(DECIMAL(18,2), FieldB)) as
FieldC
from
TableA
where
FieldB>=1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:95F2FAF7-1282-410B-8E00-831EA32921A6@.microsoft.com...
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA
> where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
> to
> work?
> Thanks|||D'oh!
Thanks, I'd tried casting the entire result, but not each field prior to
division.
"Alejandro Mesa" wrote:
> Integer division yield integer result. try casting one of the columns
> involved in the expression to a datatype with greater precedence.
> select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
> TableA
> where ([FieldB]>=1)
>
> AMB
> "mike" wrote:
>|||Mike,
If you cast the entire result, then you will be casting the integer result
and this will give you the same problem. It is not the same casting the
variables / columns involved in the expression to yield a higher precedence
datatype as casting the result.
Example:
select cast(1 as numeric(5, 3)) / 2, cast(1 / 2 as numeric(5, 3))
AMB
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>|||Yes, unfortunately Casting the result is too late.. The Integer division has
already generated the wrong (zero) result...
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>

Division by zero problem

I've got the following SQL

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / b.divider 0)

The b.divider value can be 0, but still I get the division by zero
error message.
I guess the reason is that the database performs the where statements
one at the time?

So when performing the division a.number / b.divider it could get 0 in
the divider even if b.divider 0 is also part of the where
statement??

My question is then.. How can I work around this problem? Changing the
database to set b.divider always 0 is not an optionMaybe this?

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / NULLIF(b.divider,0) 0)|||Thank you :)