Specific column -- Error converting data type nvarchar to bigint

Question

This is part of the T-SQL.I am getting below error. Can anyone guide me.The issue is because of the value column which is of nvarchar datatype

SELECT RuleID, SourceID, DataFileID, ChildCount, DP_State 
FROM        
(SELECT DP_State.RuleID, CAST(DP_State.SourceID AS VARCHAR(20)) AS SourceID, CAST(DP_State.DataFileID AS VARCHAR(20)) AS DataFileID, ChildCount, DP_State
FROM (
        SELECT  RuleID ,
                RuleResultID ,
                CASE WHEN ISNUMERIC(ISNULL([ResultValue], 0)) = 1 THEN                      
                CAST(ISNULL([Value], 0) AS BIGINT)
ELSE
                    -1
                END AS ChildCount,

Error I am getting :

`ISNUMERIC()` [is unreliable](http://stackoverflow.com/questions/312054/efficient-isnumeric-replacements-on-sql-server), `SELECT ISNUMERIC(ISNULL([ResultValue], 0)), ResultValue` and see where there is an unexpected `1` — Alex K., Mar 22 '17 at 12:44
Hi @AlexK.,I have executed SELECT ISNUMERIC(ISNULL([ResultValue], 0)), ResultValue and there are many 1 in a column along with ResultValue. — Learning_Learning, Mar 22 '17 at 12:49
You can use TRY_CAST() instead of CAST() to see where you get a NULL VALUE (Thats the one vlaue TRY_CAST coudn't cast). — InD, Mar 22 '17 at 12:50
See the link in my comment, `ISNUMERIC` thinks a value like `'.'` *is* numeric but casting it to a numeric type will fail with the error you encounter. — Alex K., Mar 22 '17 at 12:51
@AlexK., that's correct, ISNUMERIC considers +- also as numeric — Gouri Shankar Aechoor, Mar 22 '17 at 12:55

score 2 · Accepted Answer · answered Mar 22 '17 at 12:51

Try this if you are using SQL Server 2012 or later

SELECT RuleID,
    SourceID,
    DataFileID,
    ChildCount,
    DP_State
FROM (
    SELECT DP_State.RuleID,
        CAST(DP_State.SourceID AS VARCHAR(20)) AS SourceID,
        CAST(DP_State.DataFileID AS VARCHAR(20)) AS DataFileID,
        ChildCount,
        DP_State
    FROM (
        SELECT RuleID,
            RuleResultID,
            CASE 
                WHEN TRY_CONVERT(INT, ISNULL([ResultValue],0)) IS NOT NULL
                    THEN CAST(ISNULL([Value], 0) AS BIGINT)
                ELSE - 1
                END AS ChildCount,
        )
    )

@Thanks for your help.It fixed my issue. – Learning_Learning Mar 22 '17 at 13:53 — Learning_Learning, Mar 22 '17 at 13:53
Glad I could assist.@SQLBoy – Gouri Shankar Aechoor Mar 23 '17 at 07:15 — Gouri Shankar Aechoor, Mar 23 '17 at 07:15

score 1 · Answer 2 · answered Mar 22 '17 at 13:13

1

You are implicitly casting to an integer by first checking [value] against 0 in ISNULL(). Only after that you are casting to an integer, instead try the following:

Evaluate against a string:

CAST(ISNULL([Value], '0') AS BIGINT)

Or first cast to an integer:

ISNULL(CAST([Value] AS BIGINT), 0)

answered Mar 22 '17 at 13:13

Menno

12,175
14
56
88

Thanks for your response @Aquillo,it help me to realize my issue. – Learning_Learning Mar 22 '17 at 13:54

Specific column -- Error converting data type nvarchar to bigint

2 Answers2