CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
lilo.jacob
lilo.jacob

Reputation: 2414

T-Sql. CTE and IN clause. Conversion failed

Assume we have next t-SQL code:

;WITH [CTE] AS
(
    SELECT 0 [A], 'FAULT' [B]
    UNION ALL
    SELECT 1 [A], '1' [B]
    UNION ALL
    SELECT 2 [A], '2' [B]
),
[CTE2] AS
(
    SELECT [B]
    FROM [CTE]
    WHERE [A] > 0
)
SELECT * FROM [CTE2]
WHERE 1 IN (SELECT [B] FROM [CTE2])

This code faults with an error:

"Conversion failed when converting the varchar value 'FAULT' to data type int."

CTE just prepares data. CTE2 filters data, so It should return two records, where [B] = '1' and [B] = '2'. And then CTE2 is used in the IN clause. 1 is an Integer. So results of (SELECT [Value] FROM [CTE2]) must be converted to Integer.

Why does it try to convert string 'FAULT' to Integer? CTE2 does not return this string.

Upvotes: 2

Views: 790

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

This is matter of type precedence:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

16.int

27.varchar

So int "wins". But FAULT is not valid int value. You could use explicit cast:

;WITH [CTE] AS
(
    SELECT 0 [A], 'FAULT' [B]
    UNION ALL
    SELECT 1 [A], '1' [B]
    UNION ALL
    SELECT 2 [A], '2' [B]
),
[CTE2] AS
(
    SELECT [B]
    FROM [CTE]
    WHERE [A] > 0
)
SELECT * FROM [CTE2]
WHERE CAST(1 AS VARCHAR(10)) IN (SELECT [B] FROM [CTE2]);

Rextester Demo

Please note that construct:

SELECT * FROM [CTE2]
WHERE 1 IN (SELECT [B] FROM [CTE2])

will return all rows if [B] is not null and 1 is in set.

Upvotes: 0

Andrey Belykh
Andrey Belykh

Reputation: 2654

You can use something like that:

WITH [CTE] AS
(
SELECT 0 [A], 'FAULT' [B]
UNION ALL
SELECT 1 [A], '1' [B]
UNION ALL
SELECT 2 [A], '2' [B]
),
[CTE2] AS
(
SELECT [B]
FROM [CTE]
WHERE [A] > '0'
)
SELECT * FROM [CTE2]
WHERE 1 IN (SELECT [B] FROM [CTE2] WHERE CASE WHEN ISNUMERIC([B]) = 1 THEN 1 ELSE 0 END = 1)

Upvotes: 0

Matt
Matt

Reputation: 15061

What about:

;WITH [CTE] AS
(
    SELECT 0 [A], 'FAULT' [B]
    UNION ALL
    SELECT 1 [A], '1' [B]
    UNION ALL
    SELECT 2 [A], '2' [B]
)
SELECT * FROM [CTE]
WHERE [A] > 0
AND [B] = '1'

Output

A   B
1   1

SQL Fiddle: http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/7630/0

Upvotes: 0

Related Questions