ROW_NUMBER() - numeric overflow error

UDA
Enthusiast

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)
2 REPLIES
Enthusiast

Re: ROW_NUMBER() - numeric overflow error

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!
Enthusiast

Re: ROW_NUMBER() - numeric overflow error

Hi Jimm,

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