dividing decimal number by decimal number in SQL server

[sivi]

I am trying to divide a decimal number by another decimal number.
I keep getting 'Divide by zero error encountered.' message.
The following is the statement.
The variables 2 & 3 are of varchar types, hence I am trying to convert them to decimal before the calculation.

variable1= case when variable2='NULL' or cast(variable3 as decimal) =0.0 then ' ' else cast(variable2 as decimal)/cast(variable3 as decimal) end,

I would very much appreciate if someone could help me to solve this problem.

Many Thanks

 

[TLGsoft]

Syntax is: MyValue IS NULL, not MyValue <> NULL

 

[SQLBill]

DECIMAL datatype requires a precision identifier and a scale. For example: DECIMAL(4,2), so I think it should be:

variable1= case when variable2 IS NULL or cast(variable3 as decimal(5,2)) =0.0 then ' ' else cast(variable2 as decimal(5,2))/cast(variable3 as decimal(5,2)) end,


1. TLGSoft was correct about the IS NULL. The syntax ='NULL' is looking for the CHARACTERS NULL not for a NULL entry.

2. Change the decimal(5,2) to whatever precision and scale you really need it to be.

-SQLBill

 

[TLGsoft]

The problem was NULL. SQL does scalling automatically. He just could say MyRealValue = 0.
Even MyStringValue = 0 (not '0')

 

[sivi]

TLGsoft / SQLBill
Many thanks for your comments. IS NULL is very useful.

I am getting the following error message with the query statement:

variable1 = case when variable2 is NULL or cast(variable3 as decimal(6,5)) = 0.0 then ' ' else convert(decimal, variable2)/convert(decimal, variable3) end,


Error message: Divide by zero error encountered.

I am trying to avoid division by checking the value of variable3 for 0.0 in the case statement.

The value of variable3 could be:
0.050000, 10.00000, 0.0100000, 0.200000, 0.0001000, 0.00001250

Please help me to solve this problem

Many thanks in advance

Sivi

 

[TLGsoft]

You said Decimal / Decimal. See SQLBill's post. Maybe this will help you:

cast(0.01 as decimal) = 0
cast(0.01 as decimal(9,1)) = 0
cast(0.01 as decimal(9,2)) = 0.01

TLGsoft

&quot;If it works, don't fix it&quot;

 

[SQLBill]

Make all of your decimal entries to be:

decimal(10,9)

Does that work?

-SQLBill

 

[SQLBill]

By the way...I think this is currently the issue...

cast(variable3 as decimal(6,5)) = 0.0

You are CASTing the variable as a DECIMAL that is 6 digits long with 5 of those digits to the right of the decimal. For example: 0.00050.

That will NEVER equal 0.0.

-SQLBill

 

[sivi]

SQLBill Many thanks for your comments.

I tried the following:

variable1 = case when variable2 is NULL or cast(variable3 as decimal(10,9))=0.0 then ' ' else cast (variable2 as decimal(10,9)) / cast(variable3 as decimal(10,9)) end,

The error message is:
Arithmetic overflow error converting numeric to data type numeric.

Is there any other way I could do this.

I would be grateful for any help.

Sivi

 

[nikhilparchure]

Use (20 , 10) instead of (10,9)
check if helps
pls check help on Decimal datatype so u can get a clear answer ...

Regards
Nikhil