?
Solved

division in MS SQL

Posted on 2011-09-28
13
Medium Priority
?
277 Views
Last Modified: 2012-05-12
trying to divide 8518.36 by 22130 and getting null as the value.

It should be 0.384923.....

Need help..

thanks
0
Comment
Question by:rlking12
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 664 total points
ID: 36719920
Please post the exact SQL statement that you are using
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 668 total points
ID: 36719923
Humm... That's strange.  You should not get NULL.  Please post your query.


Please try:

Select 8518.36 / cast(22130 as float)
0
 

Author Comment

by:rlking12
ID: 36719969
((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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 668 total points
ID: 36719986
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

by:wdosanjos
ID: 36720072
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 93

Expert Comment

by:Patrick Matthews
ID: 36720260
Long time no see, Brandon :)
0
 

Author Comment

by:rlking12
ID: 36720419
((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

by:rlking12
ID: 36720427
IF the values show up independant of the division... shouldn't the division work?
0
 

Author Comment

by:rlking12
ID: 36720445
Select 8518.36 / cast(22130 as float)   still get null value
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36775234
What version and service pack level of SQL Server?
0
 

Author Comment

by:rlking12
ID: 36815731
SQL Server 2005
Microsoft SQL Server Management Studio      9.00.3042.00
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36815780
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

by:rlking12
ID: 36817515
thanks....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question