multiple values for a column in Where

Question

multiple values for a column in Where

Navigation

#1 by (5 votes)
#2 by (2 votes)
#3 by (2 votes)

3

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

For example product A has been replaced by product B,

product 2933 by 4044, the product 2599 by the 7845, and the 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 that I have 30 days of sale of the product 2933 but on the tenth day it changes its number code to 4044. When I put in a table to analyze the sales result, I have half in the 2933 and half in the 4044. I give the two results together and Product in ('2933', '4044') and I have complete.

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

sql sql-server

asked by anonymous 20.06.2017 / 19:35

3 answers

2

The in clause accepts an unlimited number 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')

20.06.2017 / 19:39

2

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 did not understand the question of

For example product A has been replaced by product B,

20.06.2017 / 19:43

Query value within a variable how to use $ (this) + checked?

score 5 · Accepted Answer

The original query problem:

First, your condition would always be false, you would need a OR and ( ) if you were to keep 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, AND would imply that all IN would result in true, which is impossible since each list has different values.

In the case, as already mentioned, you could use a IN only with several items, nor even use OR .

What may happen is that you need IIF (2012+ version) or CASE .. WHEN to toggle values, if you want only the most current to be displayed.

Replacing in output:

Depending on the situation, this would be 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 it.

If you want the changed values, 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 the ones in 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 to show the correct syntax, in the second case it would not occur.