locked
Using a variable with IN RRS feed

  • Question

  • I have a query where I need to evaluate the contents of one field and return results based off the contents of a column in another table.

    Because of what we are pulling I need to do that evaluation with in a case statement in the select statement.

    The path that I've started down is to take the results I needed and put them into a variable in a comma delimited string and use the IN keyword. 

    I need to sum the results and so using a subquery doesn't work and another approach I tried was using a derived table but that I couldn't seem to get that to work either.

    For simplicity, I've put together some sample data and have a query that isn't the complete solution but provides generally what I'm trying to do.

    Is there a way to use a variable within the IN keyword or maybe someone else has a different approach that may work.

    Here is the query:

    DECLARE @AdjustmentName VARCHAR(1000)  
    SET @AdjustmentName = '' 
       
    SELECT @AdjustmentName = COALESCE(@AdjustmentName + ', ','') + '''' + CAST(AdjustmentName AS VARCHAR) + '''' FROM #PointPartners WHERE AdjustmentName LIKE 'PP_%'  
    SELECT @AdjustmentName = SUBSTRING(@AdjustmentName, 2, Len(@AdjustmentName))  
     
    /*Query for the results.*/  
    SELECT   
     DATEPART(m, AdjustDateTime) MonthNum,  
     DATENAME(m, AdjustDateTime) MonthName,   
     DATEPART(yyyy, AdjustDateTime) YearNum,  
     SUM(CASE WHEN comments IN (@AdjustmentName) THEN PtsAdjusted END) AS PointPartnershipPtsAdjusted  
    FROM #Adjustment  
    GROUP BY    
     DATEPART(m, AdjustDateTime),  
     DATENAME(m, AdjustDateTime),   
     DATEPART(yyyy, AdjustDateTime) 

    Here is the table structure I used with some sample data:

    IF OBJECT_ID('TempDB..#PointPartners') IS NOT NULL  
     DROP TABLE #PointPartners  
     
    CREATE TABLE #PointPartners  
    (  
     ID smallint IDENTITY(1,1) NOT NULL,  
     RedemptionName varchar(40),  
     AdjustmentName varchar(30),  
     PartnerCode varchar(30)  
    ) ON [PRIMARY]  
     
    INSERT INTO #PointPartners VALUES ('Theisen''s Home Farm Auto', 'PP_Theisens', 'Theisen')  
    INSERT INTO #PointPartners VALUES ('Oky Doky Foods', 'PP_Oky_Doky', 'Oky')  
    INSERT INTO #PointPartners VALUES ('Floor Show', 'PP_Floor_Show', 'Floor')  
    INSERT INTO #PointPartners VALUES ('Ellis Appliance', 'PP_Ellis_Appliance', 'Ellis')  
    INSERT INTO #PointPartners VALUES ('Shamrock Jewelers', 'PP_Shamrock_Jewelers', 'Shamrock')  
    INSERT INTO #PointPartners VALUES ('Great Sounds Audio Video', 'PP_Great_Sounds', 'Great')  
    INSERT INTO #PointPartners VALUES ('Graham''s Style Shop', 'PP_Grahms', 'Grahm')  
    INSERT INTO #PointPartners VALUES ('Generic Comment', 'Positive Points Adjustment', 'Generic')  
     
    IF OBJECT_ID('TempDB..#Adjustment') IS NOT NULL  
     DROP TABLE #Adjustment  
     
    CREATE TABLE #Adjustment  
    (  
     Adjustment_ID int IDENTITY(1,1) NOT NULL,  
     AdjustDateTime datetime NOT NULL,  
     PtsAdjusted int NOT NULL,  
     Comments varchar(30)   
    ) ON [PRIMARY]  
     
    INSERT INTO #Adjustment VALUES ('2/3/2009', 115, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 35, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('1/12/2009', 32, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('1/12/2009', 10, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('2/13/2009', 43, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('12/23/2008', 22, 'PP_Theisens')  
    INSERT INTO #Adjustment VALUES ('12/23/2008', 16, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('1/3/2009', 14, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('12/31/2008', 10, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('1/5/2009', 6, 'PP_Oky_Doky')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 143, 'PP_Floor_Show')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Shamrock_Jewelers')  
    INSERT INTO #Adjustment VALUES ('2/18/2009', 426, 'PP_Floor_Show')  
    INSERT INTO #Adjustment VALUES ('2/18/2009', 1354, 'PP_Grahams')  
    INSERT INTO #Adjustment VALUES ('1/18/2009', 1073, 'PP_Ellis_Appliance')  
    INSERT INTO #Adjustment VALUES ('12/11/2008', 4556, 'PP_Ellis_Appliance')  
    INSERT INTO #Adjustment VALUES ('12/17/2008', 50000, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('12/17/2008', 500, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('1/16/2009', 127, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('1/17/2009', 81, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('2/17/2009', 8, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('2/17/2009', 90, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('2/3/2009', 12500, 'Pt Adj')  
    INSERT INTO #Adjustment VALUES ('1/10/2009', 25, 'Pt Adj')  
     


    Monday, March 2, 2009 3:10 AM

Answers

  • Thanks for your help.  Given the example this works:

     

    SELECT   
          DATEPART(m, AdjustDateTime) MonthNum,  
          DATENAME(m, AdjustDateTime) MonthName,   
          DATEPART(yyyy, AdjustDateTime) YearNum,  
          SUM(CASE WHEN p.ID IS NULL THEN 0 ELSE PtsAdjusted END) AS PointPartnershipPtsAdjusted  
    FROM #Adjustment a LEFT JOIN (SELECT ID, AdjustmentName FROM #PointPartners WHERE AdjustmentName Like 'PP_%') p ON a.comments = p.AdjustmentName  
    GROUP BY      
     
          DATEPART(m, AdjustDateTime),  
          DATENAME(m, AdjustDateTime),   
          DATEPART(yyyy, AdjustDateTime)  
     
     
     

    I'm going to try and incorporate it with the actual data and structure tomorrow to see if I get the expected results.  Thanks again.

    • Marked as answer by cehligner Monday, March 2, 2009 10:13 PM
    Monday, March 2, 2009 5:57 AM

All replies

  • You can not use variable IN. There are multiple approaches to solve your problem and in fact this problem is one of the most commonly asked on asp.net/forums website.

    One simple approach would be to use some split function such as 
    http://www.berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split
    for example and then do something like

    select * from myTable inner join dbo.ufn_alines(@myInCondition,',') Items on myTable.Field = Items.Item

    That's the idea, let me know if you are comfortable with this approach.

    Monday, March 2, 2009 3:34 AM
  •  

    I understand what you are doing but I put the items into the variable as a comma separated list so that I'd have them in a format that would work with the IN keyword.  I already have them split out in a table.

    I ran into trouble using the table because I need to SUM the results within the CASE statement.  When using the original table that doesn't work because you can't perform an aggregate function on an expression containing subquery.

    I tried getting around that by trying to use a derived table:

    SUM(CASE WHEN comments IN (SELECT AdjustmentName FROM #PointPartners) AS SUB THEN PtsAdjusted END) AS PointPartnershipDollarsAdjusted

    That, in theory, could work, but I can't seem to get the syntax right.  So that would be another viable approach if the original path I posted is a dead end.

    Monday, March 2, 2009 4:18 AM
  • I'm not sure about the whole purpose and idea of your query. Anyway, you may use LEFT JOIN, then your sum could be

    SUM(CASE when myRightTable.PK IS NULL then 0 ELSE myField end) as SumAdjustments
    • Proposed as answer by Naomi N Tuesday, March 3, 2009 3:45 AM
    Monday, March 2, 2009 4:24 AM
  • Thanks for your help.  Given the example this works:

     

    SELECT   
          DATEPART(m, AdjustDateTime) MonthNum,  
          DATENAME(m, AdjustDateTime) MonthName,   
          DATEPART(yyyy, AdjustDateTime) YearNum,  
          SUM(CASE WHEN p.ID IS NULL THEN 0 ELSE PtsAdjusted END) AS PointPartnershipPtsAdjusted  
    FROM #Adjustment a LEFT JOIN (SELECT ID, AdjustmentName FROM #PointPartners WHERE AdjustmentName Like 'PP_%') p ON a.comments = p.AdjustmentName  
    GROUP BY      
     
          DATEPART(m, AdjustDateTime),  
          DATENAME(m, AdjustDateTime),   
          DATEPART(yyyy, AdjustDateTime)  
     
     
     

    I'm going to try and incorporate it with the actual data and structure tomorrow to see if I get the expected results.  Thanks again.

    • Marked as answer by cehligner Monday, March 2, 2009 10:13 PM
    Monday, March 2, 2009 5:57 AM
  • You're welcome. BTW, you can use MONTH and YEAR functions instead of DATEPART. For me it's a bit easier to read, but it's just a personal preference.
    Monday, March 2, 2009 9:05 AM
  • Thanks for the pointer.  I like it.  Thanks again.
    Monday, March 2, 2009 10:13 PM