Subquery Returns Muiltiple Values, but I am using “IN” as my operator

Long story short, I have a Crosswalk table that has a column name MgrFilterRacf. There is a single line in the table I use to reference various things across a few queries, this method has worked fine for me. However, when I had to reference more than one value it gives me the error below. I am really at a loss as to why it is not working. I see a TON of topics/posts on this, but most are solutions stating to use the IN operator, which I already am. The mildly infuriating thing is that it works when I only call one value and delete the second line on the crosswalk table regardless of “In” or “=” being used.

Error for reference:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Crosswalk table format:

Id  | AttCeilingScore | AttFloorScore  | MgrFilterRacf    
----+-----------------+----------------+---------------
 1  |    100          |      75        |    Value1    
 2  |    NULL         |     NULL       |    Value2

Query:

--\*Perform calculation For Attendance Score Sa*\
Select
    MgrRacf,
    MgrName,
    Ctc.racf as EmpRacf,
    Ctc.EmpName,
    Case 
       when AttCntSegment is null then 0
       else AttCntSegment 
    end as AttCntSegment,
    Case 
       when AttSumDuration is null then 0
       else AttSumDuration 
    end as AttSumDuration,
    case
       when AttCntSegment > 12 
          then (Select AttFloorScore from tblAvs1Scoring)
       when AttCntSegment is null 
          then (Select AttCeilingScore from tblAvs1Scoring)
       when 100 - ((AttCntSegment) * 2 + PercentReduction) < (Select AttFloorScore from tblAvs1Scoring) 
          then (Select AttFloorScore from tblAvs1Scoring)
       else 100 - ((AttCntSegment)*2+PercentReduction)
    end As AttScore,
    Case
       when AttCntSegment is null then 100
       else 100 - ((AttCntSegment)*2+PercentReduction) 
    end as AttScoreRaw,
    'RollSixSum' as ReportTag
From
    (--\*Get Total Occurrences from Rolling 6 months per advocate*\
     SELECT 
         EMP_ID,
         COUNT(SEG_CODE) AS AttCntSegment, 
         SUM(DURATION) AS AttSumDuration
     FROM   
         tblAttendance AS Att
     WHERE  
         (START_DATE >= Getdate() - 180) 
         AND (SEG_CODE NOT IN ('FLEX2', 'FMLA'))
         AND (DURATION > 7)
         AND START_DATE IS NOT NULL
     GROUP BY 
         EMP_ID) As Totals
INNER JOIN 
    tblCrosswalkAttendanceTime AS Time ON AttSumDuration BETWEEN Time.BegTotalTime AND Time.EndTotalTime
RIGHT JOIN 
    tblContactListFull AS Ctc ON Ctc.employeeID = Totals.EMP_ID
WHERE
    --Ctc.Mgr2racf IN ('Value1','Value2')   --This works
    Ctc.Mgr2racf IN (SELECT MgrFilterRacf FROM tblAvs1Scoring) --This returns the same 2 values but doesn't work, note works with only 1 value present
    AND (title LIKE '%IV%' OR Title LIKE '%III%' OR title LIKE '%Cust Relat%')  --Going to apply same logic here once I have a solution
    AND employeestatus2  <> 'Inactive'

Specific offending lines of code:

 Ctc.Mgr2racf IN (Select MgrFilterRacf from tblAvs1Scoring) --This returns the same 2 values but doesnt work, note works with only 1 value present
        AND (title LIKE '%IV%' or Title like '%III%' or title LIKE '%Cust Relat%')  --Going to apply same logic here once I have a solution

Answer

It’s not the IN clause that’s causing the error, since IN doesn’t have to return only 1 value. Instead it’s likely here:

CASE ... < (Select AttFloorScore from tblAvs1Scoring)

Try select count(AttFloorScore) from tblAvs1Scoring and see if it’s > 1. I am sure it is.

This can be mitigated by what ever method is appropriate for your data.

Using TOP 1
Using an aggregate like MAX()
Correlating the sub-query to limit the rows returned.
Using the appropriate WHERE clause

Advertisement

Answer