several values for a column in Where

Question

Is there any way to put several values for a column in WHERE?

For example product A was replaced by product B,

product 2933 for the 4044, product 2599 for 7845, and product 5987 by 9432.

Example:

where DepartureDate between '20161120' and '20161120' 
        AND datediff(day, DataVersao, DepartureDate) between 0 and 100
        and Segment = 'VCPCNF' 
        and Produto in ('2933','4044')
        and Produto in ('5987','9432')
        and Produto in ('2599','7845')

What happens is I have 30 days of product sale 2933 But on the tenth day he changes his number code to 4044. When I put it on a chart to analyze the sales results, I have half on 2933 and half on 4044. Give me together the two results and Product in ('2933','4044') and I have complete.

But in my case, I want it to bring the result of product 2933 that was replaced by product 4044. Then bring the result of the product 5987 which was replaced by the result of the number 9432. Then bring the product result 2599 which was replaced by the result of the number 7845. Bring at once without having to run around one by one. So I separated as ('2933','4044'), ('5987','9432') and ('2599','7845')

Answer 1

The original query problem:

First, your condition would always be false, you’d need a OR and ( ) if it were to maintain the same structure:

 where DepartureDate between '20161120' and '20161120' 
    AND datediff(day, DataVersao, DepartureDate) between 0 and 100
    and Segment = 'VCPCNF' 
    and ( Produto in ('2933','4044')
          or Produto in ('5987','9432')
          or  Produto in ('2599','7845') )

In your case, the AND would imply that all IN result in true, which is impossible and each list has different values.

In case, as already mentioned, you could use a IN only with multiple items, and not using the OR.

What can happen is you need to IIF (version 2012+) or CASE .. WHEN to toggle values, in case you want only the most current one in the view.

Replacing on the way out:

Depending on the situation, it would be the case of a REPLACE in the original table, but in many situations, you need traceability, or the preservation of the original information. In this case, you can change the output data only when you need.

If you want the values changed, you can do something like this:

SELECT
  CASE  
    WHEN campo='2933' THEN '4044'
    WHEN campo='5987' THEN '9432'
    WHEN campo='2599' THEN '7845'
    ELSE campo
  END
  AS campo_substituido

If you want ONLY those on the list:

SELECT
  CASE  
    WHEN campo='2933' THEN '4044'
    WHEN campo='5987' THEN '9432'
    WHEN campo='2599' THEN '7845'
    ELSE '?'
  END
  AS campo_substituido
WHERE campo IN ( '2933', '4044', '5987', '2599', '2599', '7845' )

The ELSE is there more to show the correct syntax, in the second case it would not occur.

Answer 2

The clause in accepts an unlimited amount of parameters. So, you can do:

--SELECT * FROM foo WHERE bar IN ('fulano', 'beltrano', 'cricano', 'Palmeiras não tem mundial', 'etc')

In your specific case:

SELECT * FROM suaTabela
where DepartureDate between '20161120' and '20161120' 
        AND datediff(day, DataVersao, DepartureDate) between 0 and 100
        and Segment = 'VCPCNF' 
        and Produto in ('2933','4044', '5987','9432', '2599','7845')

Answer 3

and Produto in ('2933','4044,'5987','9432','2599','7845')

or

and ( Produto in ('2933','4044') or
      Produto in ('5987','9432') or
      Produto in ('2599','7845') )

but I didn’t understand the question of

For example product A was replaced by product B,