Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

division in MS SQL

trying to divide 8518.36 by 22130 and getting null as the value.

It should be 0.384923.....

Need help..

thanks
0
rlking12
Asked:
rlking12
  • 6
  • 3
  • 2
  • +1
3 Solutions
 
Patrick MatthewsCommented:
Please post the exact SQL statement that you are using
0
 
wdosanjosCommented:
Humm... That's strange.  You should not get NULL.  Please post your query.


Please try:

Select 8518.36 / cast(22130 as float)
0
 
rlking12Author Commented:
((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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
BrandonGalderisiCommented:
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
 
wdosanjosCommented:
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
 
Patrick MatthewsCommented:
Long time no see, Brandon :)
0
 
rlking12Author Commented:
((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
 
rlking12Author Commented:
IF the values show up independant of the division... shouldn't the division work?
0
 
rlking12Author Commented:
Select 8518.36 / cast(22130 as float)   still get null value
0
 
BrandonGalderisiCommented:
What version and service pack level of SQL Server?
0
 
rlking12Author Commented:
SQL Server 2005
Microsoft SQL Server Management Studio      9.00.3042.00
0
 
wdosanjosCommented:
It does not make sense to me.  Select 8518.36 / cast(22130 as float) is not supposed to return NULL at all.
0
 
rlking12Author Commented:
thanks....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now