How to use CASE WHEN in an IN statement?

Question

In a query I am gathering data on representatives, customers and sellers.

Some representatives have sellers, who in turn have customers. These cases are exceptional as clients are directly linked to their representatives.

When trying to use a subquery with case:

SELECT
vw.regional,
vw.cod_gerente,
vw.nome_gerente,
vw.cod_representante,
vw.nome_representante,
vw.cod_vendedor,
vw.nome_vendedor,
SUM(t.valor_original) AS total_valor_original
FROM vw_regional_gerente_representante_vendedor vw
INNER JOIN vwTitulosPagos t ON t.cod_representante = vw.cod_representante
WHERE 1 = 1
AND t.cod_cliente IN (
    CASE WHEN vw.cod_representante IN (59,77,147) THEN
        (SELECT DISTINCT cod_cliente FROM vendedores_x_clientes WHERE cod_vendedor = vw.cod_vendedor) 
    ELSE 
        t.cod_cliente 
    END
)
AND t.saldo = 0
AND t.data_movto BETWEEN CONVERT(DATETIME, '01/08/2016',103) AND CONVERT(DATETIME, '31/08/2016',103)
GROUP BY
vw.regional,
vw.cod_gerente,
vw.nome_gerente,
vw.cod_representante,
vw.nome_representante,
vw.cod_vendedor,
vw.nome_vendedor
ORDER BY vw.regional, vw.nome_gerente, vw.nome_representante, vw.nome_vendedor

The following error occurs:

SQL Error [512] [S0001]: Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.
com.microsoft.sqlserver.jdbc.Sqlserverexception: Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.

In fact the subquery will return more than one result, but it is expected to be used within the clause IN, but this only occurs within the case.

I am trying this way and looking for other solutions that are not using UNION.

Answer 1

You will not get it this way because select returns the elements of select ONE to ONE and not a list of int, what you can do is circumvent its AND clause with two conditions by checking the vw.cod_representante IN and its condition t.cod_cliente IN and a OR denying the previous condition t.cod_cliente = t.cod_cliente and and vw.cod_representante NOT IN

Thus;

AND (  ((t.cod_cliente IN 
        (SELECT DISTINCT cod_cliente 
            FROM vendedores_x_clientes 
            WHERE cod_vendedor = vw.cod_vendedor) 
        and vw.cod_representante IN (59,77,147))
       )
or     ((t.cod_cliente  = t.cod_cliente) 
        and vw.cod_representante NOT IN (59,77,147))
    )