Skip to Main Content
  • Questions
  • ORA-01476: divisor is equal to zero error

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: September 14, 2017 - 5:43 am UTC

Last updated: September 19, 2017 - 2:07 am UTC

Version: 12.1.0

Viewed 10K+ times! This question is

You Asked

Hi,

I have below query when I run without giving any quote_revision_id value its giving "ORA-01476: divisor is equal to zero" error but if I will pass quote_revision_id value its not giving above error,

Please suggest what needs to be changed.


SELECT
a.item_class,
a.quote_revision_id,
SUM(a.pn_quoted) pn_quoted,
round(
SUM(a.total_cost),
2
) total_cost,
round(
b.total_cost,
2
) main_cost,
SUM(a.total_resale) total_resale,
round(
SUM(a.parts_margin),
2
) parts_margin,
round(
(
CASE
WHEN b.total_cost = 0 THEN 1
WHEN b.total_cost <> 0 THEN SUM(a.total_cost) / b.total_cost * 100
END
),
2
) pct_of_total_cost
FROM
(
SELECT
cqp.calc_abc item_class,
cqp.quote_revision_id,
CASE
WHEN
cqp.cost_used_for_quote IS NOT NULL
AND
cqp.resale IS NOT NULL
THEN COUNT(cqp.customer_pn)
ELSE 0
END
pn_quoted,
SUM(cqp.cost_used_for_quote * cqp.total_qty_len_contract) total_cost,
SUM(cqp.resale * cqp.total_qty_len_contract) total_resale,
( SUM(cqp.resale * cqp.total_qty_len_contract) - SUM(cqp.cost_used_for_quote * cqp.total_qty_len_contract) ) / ( ( SUM(cqp.resale * cqp.total_qty_len_contract
) * 100 ) ) parts_margin
FROM
cct_quote_parts cqp
GROUP BY
cqp.calc_abc,
cqp.quote_revision_id,
cqp.cost_used_for_quote,
cqp.resale,
cqp.total_qty_len_contract
ORDER BY cqp.calc_abc
) a,
(
SELECT
cqp.quote_revision_id,
SUM(cqp.cost_used_for_quote * cqp.total_qty_len_contract) total_cost
FROM
cct_quote_parts cqp
GROUP BY
cqp.quote_revision_id
) b
WHERE
a.quote_revision_id = b.quote_revision_id
-- and a.quote_revision_id = 1142
GROUP BY
a.item_class,
a.quote_revision_id,
b.total_cost;

please let me know what I can change in the query.

Thanks

and Connor said...

This instructional video should help



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 15, 2017 - 3:10 am UTC

thanks a lot...it helps.
Connor McDonald
September 19, 2017 - 2:07 am UTC

glad we could help

Perfect

A reader, July 13, 2018 - 6:07 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library