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