How to use CASE WHEN in an IN statement?

Question

How to use CASE WHEN in an IN statement?

Navigation

#1 by (1 votes)

1

In a query I am collecting data on representatives, customers and vendors.

Some representatives have salespeople, who in turn have customers. These cases are exceptional because the clients are linked directly to their representatives.

When attempting 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 allowed when the subquery follows =,! =, & lt ;, < =, & gt ;, > = 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 =,

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

I'm trying this way and looking for solutions other than using UNION .

sql-server select case

asked by anonymous 08.09.2016 / 14:47

1 answer

Eclipse maven | lambda | use -source 8 or higher to enable lambda expressions Eclipse Error: Intializing Java Tooling

score 1 · Accepted Answer

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

So;

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))
    )