Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kbearhome
Starting Member
36 Posts |
 Posted - 2003-09-17 : 17:29:20
|
I have a table that looks like this:ProjectNo TotalHours ObsPart AKNSC000 NULL 109901alaos000 NULL 261213ALCGR000 14532 96708ALCWP000 214866 266653alfrd000 NULL 281146AMPKP020 32178 58320apfbo000 123522 509852APNBE000 9342 74319APTBE000 44634 530430I want to do simple division:select projectno, totalhours/obspartfrom dhbThis is what I get:AKNSC000 NULLalaos000 NULLALCGR000 0ALCWP000 0alfrd000 NULLAMPKP020 0apfbo000 0APNBE000 0APTBE000 0Why won't this calculate to anything but zero? Thanks |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-09-17 : 17:50:42
|
quote:
Originally posted by kbearhomeWhy won't this calculate to anything but zero?
It sees that you are operating with integers, and tries to give you an answer as an integer... Try something like: select projectno, cast(totalhours as decimal)/cast (obspart as decimal)from dhbor select projectno, (1.0*totalhours)/obspartfrom dhb |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-17 : 17:53:27
|
| What would you like NULL / 109901 to return ?- Jeff |
|
|
|
kbearhome
Starting Member
36 Posts |
Posted - 2003-09-17 : 17:59:01
|
| I'm happy if null returns null (just showing what the data look like).Thanks for the info. I was trying to convert the result to decimal but I see that it would need the basic elements converted. |
|
|
|
dsdeming
479 Posts |
Posted - 2003-09-19 : 08:37:35
|
quote:
select projectno, (1.0*totalhours)/obspartfrom dhb
That's a neat trick. Apparently the multiplication implicitly converts the totalhours integer to a decimal, and there's no need to worry about the denominator at all.Dennis |
|
|
|
|
|
|
Simple calculation