SQL Math (newbie)

ehart12
ehart12 used Ask the Experts™
on
Hi Experts,

I am attempting to write a Oracle statement that takes two existing database objects and performs some simple math to create dynamic variable.

select
wdj.attribute14,
wdj.attribute15,
decode(wdj.attribute14 / wdj.attribute15, 0, 0) cpr
from wip.wip_discrete_jobs wdj

I have been playing with the above code and cannot seem to figure it out.

I would like to divide ATTRIBUTE14 and ATTRIBUTE15 and name the new column CPR...

Thank you!


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
hi ehart12, try this instead:

select
wdj.attribute14,
wdj.attribute15,
case when wdj.attribute15 = 0 then 0
    else wdj.attribute14 / wdj.attribute15
    end cpr
from wip.wip_discrete_jobs wdj

Author

Commented:
Hi ViaTom,

Thank you very much for the CASE example.  Since both ATTRUBITE14 and ATTRIBUTE15 could be ZERO, how would you add another "when wdj.attribute14 = 0 then 0" to the statement?

Thanks again,

Eric


Commented:
hi eric,

if the numerator (the first variable in a division operation, in this case attribute14) is zero, the function will return zero.  you only need the case for attribute15 = 0 to avoid a divide-by-zero error.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Oh ya, I see what you mean!  Thank you very much...

Author

Commented:
awesome!

Commented:
welcome, and thanks for the points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial