asked on

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

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rlking12

ASKER

((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,

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wdosanjos

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

Patrick Matthews

Long time no see, Brandon :)

rlking12

ASKER

((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

rlking12

ASKER

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

rlking12

ASKER

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

BrandonGalderisi

What version and service pack level of SQL Server?

rlking12

ASKER

SQL Server 2005
Microsoft SQL Server Management Studio 9.00.3042.00

wdosanjos

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

rlking12

ASKER

thanks....