• Shanmuga Raj (3/20/2013)


    when i run the below query i get error

    Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.

    ...

    Ah, that's because I used INT constants for product_code, as you did. This query sets up the constants as character type:

    SELECT Product_ID

    FROM (

    SELECT f.Product_ID, agent_id, product_code

    FROM #fact_sales f

    WHERE (agent_id = 1 AND product_code = '1') -- changed constant from INT to character

    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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden