Conversion Failed Converting VARCHAR to INT

Question

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.

VARCHAR. But I did a CAST to INT. The second subquery, right above the X, includes that and that subquery works. — Concerned_Citizen
– Concerned_Citizen, Commented Feb 27, 2015 at 1:10
Doesn't matter. You have a '1 12:57:39 3168.68 485 H5555' in your CLAIM_ID, you might want to filter that first in a subquery. — Felix Pamittan
– Felix Pamittan, Commented Feb 27, 2015 at 1:12
You're casting CLAIM_ID to int for both A and B in the subquery aliased X, without saying to ignore '1 12:57:39 3168.68 485 H5555' — JohnLBevan
– JohnLBevan, Commented Feb 27, 2015 at 1:12
I did filter that out (as I mentioned) and it still grumbles about this. — Concerned_Citizen
– Concerned_Citizen, Commented Feb 27, 2015 at 1:13

Felix Pamittan · Accepted Answer · 2015-02-27 01:23:58Z

3

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]%'

edited Feb 27, 2015 at 1:23

answered Feb 27, 2015 at 1:18

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Concerned_Citizen Over a year ago

There's no way to filter it out without using CTE?

Felix Pamittan Over a year ago

Use a subquery. Remember you must filter it out first, not together with the CAST. The optimizer may evaluate the CAST first.

Felix Pamittan Over a year ago

You could also insert them in a temp table or table variable first.

Concerned_Citizen Over a year ago

How can I use a subquery to filter it out?

Felix Pamittan Over a year ago

Replace all FROM TestSQL, JOIN TestSQL with the subquery. Why can't you use CTE?

|

JohnLBevan · Accepted Answer · 2015-02-27 01:31:14Z

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

Collectives™ on Stack Overflow

Conversion Failed Converting VARCHAR to INT

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related