Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: [Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type

    Hi there, I hope your help.

    This is the error in my query:
    [Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.

    If tried [%TAX] DECIMAL (10, 2) I have the correct output, but I need 4 decimal in my output.

    Can you help me?
    Thanks in advance

    Code:
    MAT	NUMB-VER-EXE	NUMB-VER-RIC	%TAX	
    a	47642		7228	.15	15,17%
    b	92413		14323	.15	15,50%
    c	77815		10878	.14	13,98%
    d	74800		19601	.26	26,20%
    e	87761		8297	.09	9,45%
    f	54050		9234	.17	17,08%
    g	76739		20936	.27	27,28%
    h	30214		8695	.29	28,78%
    i	139038		13388	.10	9,63%
    l	71087		13514	.19	19,01%
    m	54992		10440	.19	18,98%
    Tot	806551		136534	2.00	16,93%
    
    
    CREATE TABLE TestTable (
    	[MAT] VARCHAR (100),
    	[NUMB-VER-EXE] INT,
    	[NUMB-VER-RIC] INT,
    	[%TAX] DECIMAL (10, 4)
    );
    
    INSERT INTO TestTable (
    	[MAT],
    	[NUMB-VER-EXE],
    	[NUMB-VER-RIC] INT,
    	[%TAX]
    ) SELECT
    	[MAT],
    	[NUMB-VER-EXE],	
    	[NUMB-VER-RIC],
    	[NUMB-VER-RIC] / (
    		CAST (
    			[NUMB-VER-EXE] AS DECIMAL (10, 4)
    		)
    	) AS [%TAX]
    FROM
    	(
    		SELECT MAT,
    		SUM (
    			CASE
    			WHEN (
    				[RISC-MOT] = '1'
    				OR [RISC-MOT] = '2'
    				OR [RISC-MOT] = '3'
    				OR [RISC-MOT] = '4'
    			)
    			AND [DATE-V] IS NOT NULL THEN
    				1
    			ELSE
    				0
    			END
    		) AS [NUMB-VER-EXE],		
    		SUM (
    			CASE
    			WHEN (
    				[COD] IN (
    					'A01',
    					'A02',
    					'A06',
    					'A07',
    					'A08',					
    					'A47'
    				)	
    			      )
    			)
    			AND [DATE-V] IS NOT NULL THEN
    				1
    			ELSE
    				0
    			END
    		) AS [NUMB-VER-RIC]
    	FROM
    		dbo_40
    	GROUP BY
    		MAT
    	) AS SubQs;
    
    
    SELECT
    		[MAT],
    		[NUMB-VER-EXE],
            	[NUMB-VER-RIC],
    		[%TAX]
    	FROM
    		(
    			SELECT
    				[MAT],
    				[NUMB-VER-EXE],
                            	[NUMB-VER-RIC],
    				[%TAX]
    			FROM
    				TestTable
    			UNION
    				SELECT
    					COALESCE ([MAT], 'Tot') AS [MAT],
    					SUM (
    						[NUMB-VER-EXE]
    					),	
    					SUM (
    						[NUMB-VER-RIC]
    					),				
    					SUM ([%TAX])
    				FROM
    					TestTable
    				GROUP BY
    					(mat) WITH ROLLUP
    		) q
    	ORDER BY
    		CASE (MAT)
    	WHEN 'Tot' THEN
    		1
    	END;
    
    DROP TABLE TestTable;

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try to use decimal(12, 4) instead of decimal(10, 4).

    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    30
    thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •