Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
"Arithmetic overflow error converting expression to data type nvarchar" Error on String

Answered "Arithmetic overflow error converting expression to data type nvarchar" Error on String

  • Monday, February 04, 2013 8:37 AM
     
     
    Sign In to Vote
    0

    Hi there, I am confused with the error "Arithmetic overflow error converting expression to data type nvarchar" in the following case:

    I got a view named "concatBtContactPair", it has two fields: DutyCycleID int, OrderedNodePair nvarchar(25). However I got error when trying to execute following query:

    SELECT *

    FROM dbo.concatBtContactPair

    WHERE OrderedNodePair = '12345'

    And the error is just "Arithmetic overflow error converting expression to data type nvarchar"

    I thought '12345' could be contained even within nvarchar(5), how could this error come?

    Thanks in advance...


    • Edited by QuelThalas Monday, February 04, 2013 8:37 AM typo
    •  
     

All Replies

  • Monday, February 04, 2013 8:39 AM
     
     
    Sign In to Vote
    1
    The error should be within the view. Requet you to share the view definition to suggest further.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, February 04, 2013 8:41 AM
     
     
    Sign In to Vote
    0

    Try

    SELECT *

    FROM dbo.concatBtContactPair

    WHERE cast(OrderedNodePair as int) = 12345;

    Many Thanks & Best Regards, Hua Min


     
  • Monday, February 04, 2013 8:52 AM
     
     
    Sign In to Vote
    0

    Hi,

    It's a common mistake ,see below site where you can get the answer with example .

    Varies case are discussed here nicely .

    Arithmetic overflow error converting expression to data type datetime

    http://go4answers.webhost4life.com/Example/arithmetic-overflow-error-converting-205443.aspx

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

     
  • Monday, February 04, 2013 9:15 AM
     
     
    Sign In to Vote
    1
    Please post Definition of your view and sample data.

    Thanks & Regards Prasad DVR

     
  • Monday, February 04, 2013 9:17 AM
     
     
    Sign In to Vote
    0

    try like this :

    SELECT *

    FROM dbo.concatBtContactPair

    WHERE OrderedNodePair = N'12345'

    Thanks and regards, Rishabh K

     
  • Monday, February 04, 2013 3:39 PM
     
     
    Sign In to Vote
    0

    I think the problem is the definition of VIEW.

    After I created a table with definition "DutyCycleID int, OrderedNodePair nvarchar(25)" and insert the result of VIEW into that table, the error just disappeared...

     
  • Monday, February 04, 2013 3:48 PM
     
      Has Code
    Sign In to Vote
    0
    The error should be within the view. Requet you to share the view definition to suggest further.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thanks, here is the definition of the view, the main idea of the view is to combine two column (which are all representing node1 have contacted with node2)  into one column in a deterministic way –– it will put the smaller one first, following by '_' as a separator, then the larger one.

    Maybe there is a better way to do this...

    CREATE VIEW concatBtContactPair (DutyCycleID, OrderedNodePair)
AS
	WITH ParticipantList
	AS (
		SELECT distinct CreatorIdentifier
		FROM iEpi.dbo.battery 
	)
	SELECT distinct
		tb1.DutyCycleID,
		CASE WHEN tb1.CreatorIdentifier < tb1.MAC
			THEN
				CAST( tb1.CreatorIdentifier AS nvarchar(12) ) + '_' + CAST( tb1.MAC AS nvarchar(12))
			ELSE
				CAST( tb1.MAC AS nvarchar(12)) + '_' + CAST( tb1.CreatorIdentifier AS nvarchar(12) )
		END	AS OrderedNodePair
	FROM iEpi.dbo.BluetoothProx AS tb1
	WHERE
		tb1.CreatorIdentifier IN ( 
			SELECT * FROM ParticipantList		
		)
		AND
		tb1.MAC IN ( 
			SELECT * FROM ParticipantList
		)


     
  • Monday, February 04, 2013 9:30 PM
     
      Has Code
    Sign In to Vote
    0

    '12345' can be contained within nvarchar(5). A more interesting question is, whether all CreatorIdentifier values and all MAC values fit in nvarchar(12).

    An easy way to find that out is using this:

    SELECT MAX(LEN(CAST(CreatorIdentifier AS nvarchar(100))))
,      MAX(LEN(CAST(MAC AS nvarchar(100))))
FROM iEpi.dbo.BlueToothProx
    If any of these two values exceeds 12, you have some cleaning up to do (or some rewriting)

    Gert-Jan

     
  • Tuesday, February 05, 2013 2:08 AM
     
     Answered Has Code
    Sign In to Vote
    0
    Maybe there is a better way to do this... 
    CREATE VIEW concatBtContactPair (DutyCycleID, OrderedNodePair)
AS
	WITH ParticipantList
	AS (
		SELECT distinct CreatorIdentifier
		FROM iEpi.dbo.battery 
	)
	SELECT distinct
		tb1.DutyCycleID,
		CASE WHEN tb1.CreatorIdentifier < tb1.MAC
			THEN
				CAST( tb1.CreatorIdentifier AS nvarchar(12) ) + '_' + CAST( tb1.MAC AS nvarchar(12))
			ELSE
				CAST( tb1.MAC AS nvarchar(12)) + '_' + CAST( tb1.CreatorIdentifier AS nvarchar(12) )
		END	AS OrderedNodePair
	FROM iEpi.dbo.BluetoothProx AS tb1
	WHERE
		tb1.CreatorIdentifier IN ( 
			SELECT * FROM ParticipantList		
		)
		AND
		tb1.MAC IN ( 
			SELECT * FROM ParticipantList
		)


    For this part in your view, you have to remove '_' for this column "OrderedNodePair" and also ensure no alphabets inside.
        SELECT distinct
            tb1.DutyCycleID,
            CASE WHEN tb1.CreatorIdentifier < tb1.MAC
                THEN
                    CAST( tb1.CreatorIdentifier AS nvarchar(12) ) + '_' + CAST( tb1.MAC AS nvarchar(12))
                ELSE
                    CAST( tb1.MAC AS nvarchar(12)) + '_' + CAST( tb1.CreatorIdentifier AS nvarchar(12) )
            END    AS OrderedNodePair

    Many Thanks & Best Regards, Hua Min