CODEWITHSUNDEEP
sql-server
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Conversion Failed Converting VARCHAR to INT

Conversion failed when converting the varchar value '1 12:57:39 3168.68 485 H5555' to data type int. This occurred in SQL Server 2012 for the following code.

SELECT 
MEMBER_ID
,STUFF
(
    (
        SELECT ',[' + C1 + '-' + C2 + ']' AS [text()]
        FROM 
        (
            SELECT DISTINCT A.MEMBER_ID
            ,A.CLAIM_ID AS C1
            ,A.PLAN_ID AS PC1
            ,B.CLAIM_ID C2
            ,B.PLAN_ID AS PC2
            ,A.AMOUNT AS AMT1
            ,B.AMOUNT AS AMT2
            FROM TestSQL A 
            JOIN TestSQL B ON A.MEMBER_ID = B.MEMBER_ID 
            WHERE A.CLAIM_ID <> '1 12:57:39 3168.68 485 H5555' 
            AND YEAR(A.DATE) > 2000 
            AND B.DATE > A.DATE 
            AND B.PLAN_ID <> A.PLAN_ID 
            AND (CAST(B.CLAIM_ID AS INT)-CAST(A.CLAIM_ID AS INT))=2
        ) Y             
        WHERE MEMBER_ID = X.MEMBER_ID
        FOR XML PATH('')
    ),1,1,''
) AS CLAIM_ID_LIST
FROM 
(
    SELECT DISTINCT A.MEMBER_ID
    ,A.CLAIM_ID AS C1
    ,A.PLAN_ID AS PC1
    ,B.CLAIM_ID C2
    ,B.PLAN_ID AS PC2
    ,A.AMOUNT AS AMT1
    ,B.AMOUNT AS AMT2
    FROM TestSQL A 
    JOIN TestSQL B ON A.MEMBER_ID = B.MEMBER_ID
    WHERE YEAR(A.DATE) > 2000  
    AND B.DATE > A.DATE 
    AND B.PLAN_ID <> A.PLAN_ID 
    AND (CAST(B.CLAIM_ID AS INT)-CAST(A.CLAIM_ID AS INT))=2 
    AND A.CLAIM_ID IN (SELECT CLAIM_ID FROM TestSQL WHERE MEMBER_ID<>'')
) X

I have tried to filter out 1 12:57:39 3168.68 485 H5555 and still gives me this error. This is a CLAIM_ID. I suspect it's the 

WHERE MEMBER_ID = X.MEMBER_ID

clause right above the FOR XML PATH but I don't know why. The second subquery works. I appreciate any insights on this.

Upvotes: 0

Views: 672

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24430

Try this:

--use cte to create a copy of the table minus the dodgy row
--or simply set that claim to null and cast remaining values
--use this anywhere we need to cast claim_id to int
;with cte as 
(
    select *
    , case 
        when CLAIM_ID = '1 12:57:39 3168.68 485 H5555' 
            then null 
        else 
            cast(CLAIM_ID as int) 
    end CLAIM_ID_INT
    from TestSQL
)
SELECT 
MEMBER_ID
,STUFF
(
    (
        SELECT ',[' + C1 + '-' + C2 + ']' AS [text()]
        FROM 
        (
            SELECT DISTINCT A.MEMBER_ID
            ,A.CLAIM_ID AS C1
            ,A.PLAN_ID AS PC1
            ,B.CLAIM_ID C2
            ,B.PLAN_ID AS PC2
            ,A.AMOUNT AS AMT1
            ,B.AMOUNT AS AMT2
            FROM cte A 
            JOIN cte B ON A.MEMBER_ID = B.MEMBER_ID 
            WHERE A.CLAIM_ID <> '1 12:57:39 3168.68 485 H5555' 
            AND YEAR(A.DATE) > 2000 
            AND B.DATE > A.DATE 
            AND B.PLAN_ID <> A.PLAN_ID 
            AND (B.CLAIM_ID_INT - A.CLAIM_ID_INT)=2
        ) Y             
        WHERE Y.MEMBER_ID = X.MEMBER_ID
        FOR XML PATH('')
    ),1,1,''
) AS CLAIM_ID_LIST
FROM 
(
    SELECT DISTINCT A.MEMBER_ID
    ,A.CLAIM_ID AS C1
    ,A.PLAN_ID AS PC1
    ,B.CLAIM_ID C2
    ,B.PLAN_ID AS PC2
    ,A.AMOUNT AS AMT1
    ,B.AMOUNT AS AMT2
    FROM cte A 
    JOIN cte B ON A.MEMBER_ID = B.MEMBER_ID
    WHERE YEAR(A.DATE) > 2000  
    AND B.DATE > A.DATE 
    AND B.PLAN_ID <> A.PLAN_ID 
    AND B.CLAIM_ID_INT-A.CLAIM_ID_INT = 2 
    AND A.CLAIM_ID IN (SELECT CLAIM_ID FROM cte WHERE MEMBER_ID<>'')
) X

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is where the error came from:

(CAST(B.CLAIM_ID AS INT)-CAST(A.CLAIM_ID AS INT)) = 2

Casting 1 12:57:39 3168.68 485 H5555 to INT will produce an error. Though you said you've filtered it out in WHERE A.CLAIM_ID <> '1 12:57:39 3168.68 485 H5555'. The optimizer may opt to evaluate the CAST first and thus result an error.

You may want to filter that out first in a CTE. Then use that CTE instead:

;WITH CTE AS(
    SELECT
        *
    FROM TestSQL
    WHERE
        CLAIM_ID <> '1 12:57:39 3168.68 485 H5555'
)
SELECT 
MEMBER_ID
,STUFF
(
    (
        SELECT ',[' + C1 + '-' + C2 + ']' AS [text()]
        FROM 
        (
            SELECT DISTINCT A.MEMBER_ID
            ,A.CLAIM_ID AS C1
            ,A.PLAN_ID AS PC1
            ,B.CLAIM_ID C2
            ,B.PLAN_ID AS PC2
            ,A.AMOUNT AS AMT1
            ,B.AMOUNT AS AMT2
            FROM CTE A 
            JOIN CTE B ON A.MEMBER_ID = B.MEMBER_ID 
            WHERE A.CLAIM_ID <> '1 12:57:39 3168.68 485 H5555' 
            AND YEAR(A.DATE) > 2000 
            AND B.DATE > A.DATE 
            AND B.PLAN_ID <> A.PLAN_ID 
            AND (CAST(B.CLAIM_ID AS INT)-CAST(A.CLAIM_ID AS INT))=2
        ) Y             
        WHERE MEMBER_ID = X.MEMBER_ID
        FOR XML PATH('')
    ),1,1,''
) AS CLAIM_ID_LIST
FROM 
(
    SELECT DISTINCT A.MEMBER_ID
    ,A.CLAIM_ID AS C1
    ,A.PLAN_ID AS PC1
    ,B.CLAIM_ID C2
    ,B.PLAN_ID AS PC2
    ,A.AMOUNT AS AMT1
    ,B.AMOUNT AS AMT2
    FROM CTE A 
    JOIN CTE B ON A.MEMBER_ID = B.MEMBER_ID
    WHERE YEAR(A.DATE) > 2000  
    AND B.DATE > A.DATE 
    AND B.PLAN_ID <> A.PLAN_ID 
    AND (CAST(B.CLAIM_ID AS INT)-CAST(A.CLAIM_ID AS INT))=2 
    AND A.CLAIM_ID IN (SELECT CLAIM_ID FROM CTE WHERE MEMBER_ID<>'')
) X

To make sure only numeric CLAIM_ID will be returned, you may use this inside the CTE:

SELECT *
FROM TestSQL
WHERE CLAIM_ID NOT LIKE '%[^0-9]%'

Upvotes: 3

Related Questions