when i run the below query i get error
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
-- alternative version
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = 1)
OR (agent_id = 2 AND product_code = 3)
OR (agent_id = 3 AND product_code = 2)
OR (agent_id = 4 AND product_code = 1)
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4
ChrisM@Work (3/20/2013)
Shanmuga Raj (3/20/2013)
Getting error :Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
ChrisM@Work (3/20/2013)
Depending upon existing indexes, this could be a significant improvement:
DROP TABLE #fact_sales
CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))
INSERT INTO #fact_sales (agent_id, product_code, product_id)
VALUES
(1,1,'1'),
(1,1,'2'),
(1,1,'3'),
(1,1,'4'),
(1,1,'5'),
(2,3,'1'),
(2,3,'1'),
(3,2,'1'),
(3,2,'1'),
(4,1,'1'),
(4,1,'2')
CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)
-- existing version
SELECT product_id
FROM #fact_sales
WHERE (agent_id=1 AND product_code in (1))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=2 AND product_code in (3))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=3 AND product_code in (2))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=4 AND product_code in (1))
)
)
)
-- alternative version
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = 1)
OR (agent_id = 2 AND product_code = 3)
OR (agent_id = 3 AND product_code = 2)
OR (agent_id = 4 AND product_code = 1)
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4
What query results in this error message? Your post is vague and unhelpful.