PLEASE Help with correct syntax

Question

PLEASE Help with correct syntax

Bosco2

Ten Centuries

Points: 1240
More actions
June 22, 2009 at 7:22 pm

#135524

I am writing a query and it current looks like this:
select
cast(convert(decimal,a.totaltransactions) / convert(decimal,b.totaltransactions) as decimal (8,8))
from table1
I need to be able to add a 'NULLIF' because I currently get a "Divide by zero error encountered."
I think I just need help in how to add the nullif into my query. I am stuck, please help!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

allenjasonbrown (6/22/2009)
I am writing a query and it current looks like this:
select
cast(convert(decimal,a.totaltransactions) / convert(decimal,b.totaltransactions) as decimal (8,8))
from table1
I need to be able to add a 'NULLIF' because I currently get a "Divide by zero error encountered."
I think I just need help in how to add the nullif into my query. I am stuck, please help!!

Divide by Zero error cannot be trapped by nullif. You may have 0 in the denominator.

I've added a case statement, whenever u encounter a 0, it'll translate it to 1... you may change it..

select

convert(decimal,a.totaltransactions) / cast((case b.totaltransactions when 0 then 1 else b.totaltransactions end) as decimal(20,8))

from table1 a, table1 b

Pradeep Singh

Bosco2 Ten Centuries Points: 1240 More actions · Answer 2

Thanks for the quick reply. My question is that if I make the value 1, then the table (which will have almost 700k calculations) will be invalid. I cannot change the data. I was hoping to just force the value to null, so I can identify those when I have the rseults.

For example, the nullif worked originally but then I had to add the cast and convert and not sure how to incorporate the nullif with that.

Maybe I am making it to complicated. I have 2 types of values (int and money).

I am just trying to divide int/int and money/money with the result in decimal format with 8 digits (.00000000).

Help PLEASE!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

select

cast(convert(decimal,a.totaltransactions) / nullif(convert(decimal,b.totaltransactions),0.00) as decimal (8,8))

from

table1

Last time I tested, dividing by null returns null. Give this a try.

Bosco2 Ten Centuries Points: 1240 More actions · Answer 4

Now I am getting this error "Arithmetic overflow error converting numeric to data type numeric"

Both fields are defined as "money".

Thanks for the help...I am pulling my hair out, I thought this would be easy. All I am trying to do is divide a value from table1 by the value from table2. (all the 8 columns).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

That is because decimal(8,8) means this: .00000000. You need to define your decimal value more like this: decimal(32,8).

Bosco2 Ten Centuries Points: 1240 More actions · Answer 6

Bosco2

Ten Centuries

Points: 1240

June 22, 2009 at 9:46 pm

#1013704

THANK YOU, THANK YOU, THANK YOU!!!