All Forums UDA
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,KarenSELECT (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 aWHERE a.ms_handler_active_from_date_id < :v_start_of_week_dateQUALIFY 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 298 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 aWHERE a.ms_handler_active_from_date_id < :v_start_of_week_dateQUALIFY 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.