Re: numeric or value error precision to large.

g3000 wrote:
> 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,

[snip]

I can only guess, that mm_lib.get_totals is wrong, or returns something wrongly, or is the actual cause of the error (i.e. is called with out-of-bound variable values).

Could you at least post the actual error stack next time you have such a question?

-- 
Regards,
Frank van Bortel