Getting “Subquery returned more than 1 value error message ” while using Case statement inside Where Not In

If I do

SELECT * FROM cte1 c
    WHERE c.GrantNumber NOT IN
    (

      SELECT t1.nai_grant_number FROM #temp t1

    ) 

This works fine.

  • How to do a case sensitive GROUP BY?
  • Windows application - Just Starting
  • Inserting multiple child elements from XML into SQL Server database
  • Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?
  • SQL sum or subtracted based on a column (transaction type)
  • Language difference in full text search for numeric strings
  • But if I do

    SELECT * FROM cte1 c
        WHERE c.GrantNumber NOT IN
        (
          CASE WHEN 
                    @AutoRenewalChk = 1 THEN
                     (
    
                         SELECT t1.nai_grant_number FROM #temp t1
                     ) END          
        )
    

    getting error

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

    What is the reason? Cannot we use a case statement like the above?

    Thanks

    3 Solutions collect form web for “Getting “Subquery returned more than 1 value error message ” while using Case statement inside Where Not In”

    SELECT * FROM cte1 AS c
    WHERE NOT EXISTS
    (
      SELECT 1 FROM #temp AS t1 
      WHERE t1.nai_grant_number = CASE @AutoRenewalChk 
        WHEN 1 THEN c.GrantNumber END
    );
    

    Here is why NOT IN will not work here. Try these two queries. Would you expect the same result in each case?

    SELECT x FROM (SELECT x = 1) AS x WHERE x NOT IN 
        (SELECT y = 2);
    
    SELECT x FROM (SELECT x = 1) AS x WHERE x NOT IN 
        (SELECT y = 2 UNION ALL SELECT NULL);
    

    Adding a possible NULL here (which Dan P suggested as an ELSE in his answer, or if #temp can contain even a single row where nai_grant_number is NULL) completely changes the semantics of NOT IN. Therefore just about any time you are thinking about writing a NOT IN query, you should re-think it as a NOT EXISTS (or LEFT OUTER JOIN, or other struct).

    Just like the error says, your query iS returning more than one row. If you change your subs select to select top 1… It will work

    Try this instead as others have mentioned the CASE When works on a single column or value not a rowset. Assuming c.GrantNumber is never null this should work for you.

    SELECT * FROM cte1 c 
    WHERE c.GrantNumber NOT IN 
    ( 
        SELECT DISTINCT
            CASE WHEN @AutoRenewalChk = 1
                THEN t1.nai_grant_number
                ELSE NULL
            END
        FROM #temp t1
    )
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.