CODEWITHSUNDEEP
sqlsql-servert-sql
Lucas_Santos
Lucas_Santos

Reputation: 4740

WHERE Clause with STUFF function

I want to do a query, that I get some ID an separate then with commas, and put it on my where clause.

But occurs the following error

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the nvarchar value '2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013,3482,3483,3484,3485,3486,3487,3488,3489,3490,3491,3492,3493,3494,3495,...

I tried some cast, but doesn't work.

Sample query:

SELECT A.ID, A.ID_PERIODO_GESTAO, A.ID_FILIAL, A.ID_RESPONSAVEL_AREA, A.ID_PARENT, A.ID_SOURCE, A.COD_AREA, A.DESC_AREA, A.ATIVO, A.USER_LOGIN_RESP, A.NOME_RESP, A.DESC_FILIAL, A.COD_AREA_SUP, A.FOTO_PATH, 
A.COLABORADOR_ID,
CASE WHEN (SELECT COUNT(B.ID_COLABORADOR) FROM COLABORADOR_AREA B WHERE B.ID_AREA = A.ID GROUP BY B.ID_AREA) IS NULL THEN 0 ELSE 
(SELECT COUNT(B.ID_COLABORADOR) FROM COLABORADOR_AREA B WHERE B.ID_AREA = A.ID GROUP BY B.ID_AREA) END AS QTD_COLABORADORES,
A.LEVEL_TREE,LEN(A.LEVEL_TREE),

STUFF(
           (SELECT ',' + CAST(VW.ID AS VARCHAR(10))
            FROM VW_AREA VW
            WHERE LEN(VW.LEVEL_TREE) > LEN(A.LEVEL_TREE) 
            AND VW.ID_PERIODO_GESTAO = 2                 
            FOR XML PATH('')), 1, 1, '')

FROM VW_AREA A
LEFT JOIN dbo.COLABORADOR_AREA ON dbo.COLABORADOR_AREA.ID_COLABORADOR = A.COLABORADOR_ID        
WHERE A.ID_FILIAL IN (9) AND A.ID_PERIODO_GESTAO = 2

UNION

SELECT A.ID, A.ID_PERIODO_GESTAO, A.ID_FILIAL, A.ID_RESPONSAVEL_AREA, A.ID_PARENT, A.ID_SOURCE, A.COD_AREA, A.DESC_AREA, A.ATIVO, A.USER_LOGIN_RESP, A.NOME_RESP, A.DESC_FILIAL, A.COD_AREA_SUP, A.FOTO_PATH, 
A.COLABORADOR_ID,
CASE WHEN (SELECT COUNT(B.ID_COLABORADOR) FROM COLABORADOR_AREA B WHERE B.ID_AREA = A.ID GROUP BY B.ID_AREA) IS NULL THEN 0 ELSE 
(SELECT COUNT(B.ID_COLABORADOR) FROM COLABORADOR_AREA B WHERE B.ID_AREA = A.ID GROUP BY B.ID_AREA) END AS QTD_COLABORADORES,
A.LEVEL_TREE, LEN(A.LEVEL_TREE),

    STUFF(
            (SELECT ',' + CAST(VW.ID AS VARCHAR(10))
             FROM VW_AREA VW 
             WHERE LEN(VW.LEVEL_TREE) > LEN(A.LEVEL_TREE) 
             AND VW.ID_PERIODO_GESTAO = 2           
             FOR XML PATH('')), 1, 1, '') AS 

FROM VW_AREA A
LEFT JOIN dbo.COLABORADOR_AREA ON dbo.COLABORADOR_AREA.ID_COLABORADOR = A.COLABORADOR_ID
JOIN CTE4 ON A.ID = CTE4.ID_PARENT
WHERE A.ID_PERIODO_GESTAO = 2 
AND (A.ID IN (CTE4.ID_PARENT))

RESULT

QTD_COLABORATOR    LEVEL_TREE     QTD_CARACTER_IN_LEVEL_TREE
1                  002            3

The last column, with the STUFF function, will contains data like that

2997,2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013,3014,3015,3016,3017

This is all ID that contains more then 3 caracter in LEVEL_TREE

Upvotes: 2

Views: 2871

Answers (2)

Aram
Aram

Reputation: 5705

Hi STUFF will make your values comma separated, and the IN Clause will expect a Result Set of INTs, so there it crashes, You might be able to do it with Dynamic String execution like this:

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

Upvotes: 0

Necreaux
Necreaux

Reputation: 9776

That will never work. You're trying to use an in comparator against a string. Hence the syntax error. Is there a reason you can't do this?

SELECT SUM(A.ID) 
 FROM COLABORATOR A 
 WHERE A.ID IN (SELECT VW.ID FROM VW_AREA VW)

Upvotes: 3

Related Questions