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 :

Answer 1

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,
        )
    )