TSQL: SELECT CASE WHEN THEN Subquery: Error: Subquery returns more than 1 Value

Question

im getting the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

in the query below

select case
        when serial_number like ('550092%') then

             (select distinct  left(product, 6) from dbo.QS_WIP_Errors2 err
              where err.SERIAL_NUMBER = serial_number)
                --err.SERIAL_NUMBER in (SERIAL_NUMBER)) 

        else left(SERIAL_NUMBER,6) 

        end as Identnummer

from dbo.QS_defects def
 where INSPECT_TIME >= '2015-08-01' and INSPECT_TIME <= '2015-08-10'

(I also tried the code in the comment) I really can´t explain it as i thought by selecting a distinct value not more than one row can be returned. What am I doing wrong??

The error says it all - you are trying to project more than one value out of the `then` of the `case when` - you can limit the select with `TOP 1` but you should investigate your data as to why it isn't as you expect it. — StuartLC, Aug 28 '15 at 08:56
Thanks a lot - the Top 1 is a nice workaround i haven´t thought about. As far as I understand the tables it is impossible that the same SERIAL_NUMBER is assigned to more than one Product. I´ll mark it as solved anyway as the problem is not in the code above — Tobi89, Aug 28 '15 at 09:06

score 0 · Answer 1 · answered Aug 28 '15 at 12:32

If serial_number is in fact unique per product as you thought, the reason you are getting more than one row even though Serial_Number is unique is because you have not aliased your columns properly. In summary you have:

SELECT  (   select distinct  left(product, 6) 
            from dbo.QS_WIP_Errors2 err
            where err.SERIAL_NUMBER = serial_number) -- NO ALIAS ON serial_number
FROM    dbo.QS_defects def

In your correlated subquery you have no alias on the second reference to serial_number, which means it is ambiguous as to whether it refers to the outer table (def) or the inner table (err). In the case of a name collision SQL Server will assume that you mean the inner table, so you essentially have:

WHERE err.serial_number = err.serial_number

Which will of course return all rows because everything equals itself. A quick demonstation of the issue:

DECLARE @T TABLE (ID INT, Name VARCHAR(20));
INSERT @T (ID, Name) VALUES (1, 'Test 1'), (2, 'Test 2');

SELECT  *
FROM    @T AS T1
        OUTER APPLY
        (   SELECT  CorrelatedName = Name
            FROM    @T AS T2
            WHERE   T2.ID = ID
        ) AS T3
WHERE   T1.ID = 1;

The output of this shows that both "Test 1" and "Test 2" are returned as the correlated name:

ID  Name        CorrelatedName
----------------------------------
1   Test 1      Test 1
1   Test 1      Test 2

With the correct alias, you only get a single row:

DECLARE @T TABLE (ID INT, Name VARCHAR(20));
INSERT @T (ID, Name) VALUES (1, 'Test 1'), (2, 'Test 2');

SELECT  *
FROM    @T AS T1
        OUTER APPLY
        (   SELECT  CorrelatedName = Name
            FROM    @T AS T2
            WHERE   T2.ID = T1.ID
        ) AS T3
WHERE   T1.ID = 1;

ID  Name        CorrelatedName
----------------------------------
1   Test 1      Test 1

So your final query should probably be:

SELECT  CASE WHEN def.serial_number LIKE '550092%' THEN
                (   SELECT  LEFT(err.Product, 6)
                    FROM    dbo.QS_WIP_Errors2 AS err
                    WHERE   err.serial_number = def.serial_number
                )
            ELSE LEFT(def.serial_number, 6)
        END AS IdentNumber
FROM    dbo.QS_defects AS def
WHERE   def.inspect_time >= '2015-08-01' 
AND     def.inspect_time <= '2015-08-10';

It is a little more effort, but when I am writing queries I will qualify everything¹, this way it is immediately clear exactly what table each column is from, and errors like this become much easier to spot.

_{1. If it is a simple select from one table I may not bother with an alias, or qualifying column references, although even then I still try to do it, just in case I need to add tables at a later date}

TSQL: SELECT CASE WHEN THEN Subquery: Error: Subquery returns more than 1 Value

1 Answers1