We are experiencing network problems with the site. Some functions may be temporarily unavailable. Please bear with us while we work to resolve these issues.
All Forums Enterprise
deagle 17 posts Joined 11/08
14 Sep 2009
ROW_NUMBER() - numeric overflow error

Hi,

I am trying to run the following SQL but I get a 2616: Numeric overflow occured during computation error.

Can someone explain why this happens and maybe suggest a solution.

Thanks,
Karen

SELECT (CAST(((100000 * 100000000) + Ms_handler_key_id) AS NUMERIC(18))) lw_ms_tr_key_id,
a.Ms_handler_id
FROM MSt_lu_handler_mgmt_key a
WHERE a.ms_handler_active_from_date_id < :v_start_of_week_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.Ms_handler_id
ORDER BY a.ms_handler_active_from_date_id desc ,
a.ms_handler_eff_date_id desc, a.ms_handler_seq_id DESC) = 1)

Jimm 165 posts Joined 09/07
14 Sep 2009

You are only casting the final result as decimal(18). Assuming Ms_Handler_Key_Id is an integer or smallint, it will do the arithmetic as integers.
So try:

SELECT (CAST(Ms_handler_key_id As Decimal(18))) + (CAST(100000 AS Decimal(18)) * CAST(100000000 AS Decimal(18)))
AS lw_ms_tr_key_id,
a.Ms_handler_id
FROM MSt_lu_handler_mgmt_key a
WHERE a.ms_handler_active_from_date_id < :v_start_of_week_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.Ms_handler_id
ORDER BY a.ms_handler_active_from_date_id desc ,
a.ms_handler_eff_date_id desc, a.ms_handler_seq_id DESC) = 1)

Should work now!

deagle 17 posts Joined 11/08
15 Sep 2009

Hi Jimm,

Thanks so much for your help. That worked a treat!!!

You must sign in to leave a comment.