I have a problem where I am getting numeric or value number precision
too large for the last column in my (unit_cost) in my first query.
If I make that column just '0' then I get no errors.
Any Ideas? How does the optimizer determine the value of that column
based on the call to NVL? Does it imply no precision?
SELECT ai.item_id
AS item_id,
m.nomenclature
AS nomenclature,
m.item_type
AS item_type,
m.shelf_life_code
AS shelf_life_code,
ai.ammal_id
AS ammal_id,
w.account_name
AS wc_supply_name,
DECODE( mm_lib.get_bed_level,
'1000', ai.fos_1000_qty,
'500', ai.fos_500_qty,
'250', ai.fos_250_qty,
ai.local_qty )
AS local_qty,
NVL(mm_lib.calc_ammal_qob(ai.item_id, ai.ammal_id,
ai.account_seq),0) AS ammal_qob,
NVL(mm_lib.calc_qob(ai.item_id),0)
AS total_qob,
NVL(mm_lib.calc_sub_ammal_qob(ai.item_id, ai.ammal_id,
ai.account_seq),0) AS sub_ammal_qob,
NVL(mm_lib.calc_sub_qob(ai.item_id),0)
AS sub_total_qob,
NVL(mm_lib.calc_ammal_qoo(ai.item_id, ai.ammal_id,
ai.account_seq),0) AS on_order,
NVL(mm_lib.calc_sub_ammal_qoo(ai.item_id, ai.ammal_id,
ai.account_seq),0)
AS sub_on_order,
u.uoi_code
AS inv_uoi_code,
u.unit_price
AS unit_price,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id,
ai.item_id, 'SAMS')
AS sams_req_num_string,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id,
ai.item_id, 'SNAP')
AS snap_req_num_string,
NVL(ai.required_qty,0)
AS required_qty,
NVL(ai.low_order_qty,0)
AS low_order_qty,
NVL(mm_lib.get_totals(w.account_name, ai.item_id),0)
AS unit_cost
FROM mm_account w,
mm_ammal_item ai,
mm_item_master m,
mm_item_uoi u
WHERE ai.item_id = m.item_id
AND ai.account_seq = w.account_seq
AND m.item_id = u.item_id (+)
AND m.req_item_level = u.uoi_level (+)
UNION
SELECT ai.item_id,
m.nomenclature,
m.item_type,
m.shelf_life_code,
ai.ammal_id,
w.account_name,
DECODE( mm_lib.get_bed_level,
'1000', ai.fos_1000_qty,
'500', ai.fos_500_qty,
'250', ai.fos_250_qty,
ai.local_qty ),
NVL(mm_lib.calc_ammal_qob(ai.item_id, ai.ammal_id,
ai.account_seq),0),
NVL(mm_lib.calc_qob(ai.item_id),0),
NVL(mm_lib.calc_sub_ammal_qob(ai.item_id, ai.ammal_id,
ai.account_seq),0),
NVL(mm_lib.calc_sub_qob(ai.item_id),0),
NVL(mm_lib.calc_ammal_qoo(ai.item_id, ai.ammal_id,
ai.account_seq),0),
NVL(mm_lib.calc_sub_ammal_qoo(ai.item_id, ai.ammal_id,
ai.account_seq),0),
u.uoi_code,
u.unit_price,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id,
ai.item_id, 'SAMS'),
mm_lib.req_number_string(ai.account_seq, ai.ammal_id,
ai.item_id, 'SNAP'),
NVL(ai.required_qty,0),
NVL(ai.low_order_qty,0),
NVL(mm_lib.get_totals(w.account_name, ai.item_id),0)
FROM mm_account w,
mm_ammal_item ai,
mm_item_master m,
mm_item_uoi u,
mm_item_xref ix
WHERE ai.item_id = m.item_id
AND ai.account_seq = w.account_seq
AND m.item_id = u.item_id (+)
AND m.req_item_level = u.uoi_level (+)
AND m.item_id = ix.xref_item_id
AND ix.xref_type = 'S';
Received on Tue Dec 14 2004 - 08:36:16 CST
