CODEWITHSUNDEEP
sqlsql-server
cdscivic
cdscivic

Reputation: 95

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

Upvotes: 3

Views: 90

Answers (2)

cdscivic
cdscivic

Reputation: 95

The error was not where I thought it was, it was at the top of the query in those sub queries. Even though there as no values present, it still needs to be told since I added a line to return the max value. Once I updated that all was well and worked.

Bottom line, check all your sub queries.

Upvotes: 0

S3S
S3S

Reputation: 25112

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.

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

Upvotes: 4

Related Questions