0

I have the following table which you can also find in the SQL Fiddle here:

CREATE TABLE Flows (
    Flow_Date DATE,
    Product TEXT,
    FlowType TEXT,
    Country TEXT,
    Quantity VARCHAR(255)
);

INSERT INTO Flows
(Flow_Date, Product, FlowType, Country, Quantity)
VALUES 
("2019-05-23", "Product A", "Inbound", "DE", "400"),
("2019-05-23", "Product A", "Inbound", "DE", "400"),
("2019-05-23", "Product B", "Inbound", "NL", "500"),
("2019-05-23", "Product B", "Inbound", "NL", "500"),
("2019-05-23", "Product A", "Outbound", "FR", "300"),
("2019-05-23", "Product A", "Outbound", "FR", "300"),
("2019-05-23", "Product B", "Outbound", "US", "200"),
("2019-05-23", "Product B", "Outbound", "US", "200"),
("2019-05-24", "Product A", "Inbound", "DE", "900"),
("2019-05-24", "Product A", "Inbound", "DE", "900"),
("2019-05-24", "Product B", "Inbound", "NL", "800"),
("2019-05-24", "Product B", "Inbound", "NL", "800"),
("2019-05-24", "Product A", "Outbound", "FR", "650"),
("2019-05-24", "Product A", "Outbound", "FR", "650"),
("2019-05-24", "Product B", "Outbound", "US", "450"),
("2019-05-24", "Product B", "Outbound", "US", "450"),
("2019-05-25", "Product A", "Inbound", "DE", "900"),
("2019-05-25", "Product A", "Inbound", "DE", "900"),
("2019-05-25", "Product B", "Inbound", "NL", "800"),
("2019-05-25", "Product B", "Inbound", "NL", "800"),
("2019-05-25", "Product A", "Outbound", "FR", "650"),
("2019-05-25", "Product A", "Outbound", "FR", "650"),
("2019-05-25", "Product B", "Outbound", "US", "450"),
("2019-05-25", "Product B", "Outbound", "US", "450"),
("2019-05-26", "Product A", "Inbound", "DE", "900"),
("2019-05-26", "Product A", "Inbound", "DE", "900"),
("2019-05-26", "Product B", "Inbound", "NL", "800"),
("2019-05-26", "Product B", "Inbound", "NL", "800"),
("2019-05-26", "Product A", "Outbound", "FR", "650"),
("2019-05-26", "Product A", "Outbound", "FR", "650"),
("2019-05-26", "Product B", "Outbound", "US", "450"),
("2019-05-26", "Product B", "Outbound", "US", "450");

I use the following query to get data from this table:

SELECT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 23:59:59")
AND Country IN ("DE","NL")
GROUP BY 1,2,3,4;

All this works perfectly so far.

However, now I want to set a variable for the IN condition in the WHERE clause of the statement.
Therefore, I tried to go with the following:

SET @country = ("DE", "NL");
SELECT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 23:59:59")
AND Country IN @country
GROUP BY 1,2,3,4;

However, with this I get error Operand should contain 1 column(s).

What do I need to change in my code to make it work?

Michi
  • 4,663
  • 6
  • 33
  • 83
  • check solution here: https://stackoverflow.com/questions/4723100/passing-parameters-to-in-clause-in-sql-server – probin anand Feb 06 '20 at 08:21
  • *with this I get error `Operand should contain 1 column(s)`* This is an error for `SET @country = ("DE", "NL");` statement. – Akina Feb 06 '20 at 08:24
  • *I use the following query to get data from this table:* GROUP BY by all output fieldlist? Remove GROUP BY clause at all, but add DISTINCT. Or add some aggregate action, for example, `SUM(Quantity)`... – Akina Feb 06 '20 at 08:25

1 Answers1

0
SET @country = "DE,NL";

SELECT DISTINCT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 00:00:00")
  AND FIND_IN_SET(Country, @country);

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25