Solved

# division in MS SQL

Posted on 2011-09-28
226 Views
trying to divide 8518.36 by 22130 and getting null as the value.

It should be 0.384923.....

Need help..

thanks
0
Question by:rlking12
• 6
• 3
• 2
• +1

LVL 92

Assisted Solution

Patrick Matthews earned 166 total points
Please post the exact SQL statement that you are using
0

LVL 23

Accepted Solution

wdosanjos earned 167 total points
Humm... That's strange.  You should not get NULL.  Please post your query.

Select 8518.36 / cast(22130 as float)
0

Author Comment

((Case when orderdtl.ordernum is null Then (case project.number02 when 0 then 1 else project.number02 end)
ELSE ((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0)
END)) - isnull(EstMaterial.Act_Cost,0)) / (Case when orderdtl.ordernum is null Then (case project.number02 when 0 then 1 else project.number02 end)
ELSE  (case((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0) when 0 then 1 end)
END)) as Actual_GPPerc,
0

LVL 39

Assisted Solution

BrandonGalderisi earned 167 total points
Well rlk it's going to be very difficult with that statement to decipher where the problem is without sample data because there's no way for us to know what value will come from each componenent.
0

LVL 23

Expert Comment

There are a couple of conditions that can resolve to NULL:

1) If orderdtl.ordernum is null and project.number02 <> 0 and project.number02 is null

(Case when orderdtl.ordernum is null Then (case project.number02 when 0 then 1 else project.number02 end)

2) If orderdtl.ordernum is not null and (((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0)) <> 0

(case((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0) when 0 then 1 end)  -- The CASE has no ELSE
0

LVL 92

Expert Comment

Long time no see, Brandon :)
0

Author Comment

((Case when orderdtl.ordernum is null Then (case project.number02 when 0 then 1 else project.number02 end)
ELSE ((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0)
END)) - isnull(EstMaterial.Act_Cost,0))

the top part evalutes to:  8518.36

/ (Case when orderdtl.ordernum is null Then (case project.number02 when 0 then 1 else project.number02 end)
ELSE  (case((isnull(orderdtl.docunitprice,0) * isnull(orderdtl.orderqty,0)) - isnull(tblmisc.MiscAmt,0)) - isnull(orderdtl.discount,0) when 0 then 1 end)
END)) as Actual_GPPerc

The bottom evalutes to: 22130

I've placed them in seperate fields and it gives me a vaue separately.  So if you manula divide them on a caluculator you get: 0.384923

0

Author Comment

IF the values show up independant of the division... shouldn't the division work?
0

Author Comment

Select 8518.36 / cast(22130 as float)   still get null value
0

LVL 39

Expert Comment

What version and service pack level of SQL Server?
0

Author Comment

SQL Server 2005
Microsoft SQL Server Management Studio      9.00.3042.00
0

LVL 23

Expert Comment

It does not make sense to me.  Select 8518.36 / cast(22130 as float) is not supposed to return NULL at all.
0

Author Closing Comment

thanks....
0

## Featured Post

### Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.