Can someone help me understand this error? What exactly am I not allowed to do here?

 SELECT
     CASE
         WHEN tagged_id != ' ' 
             THEN (SELECT tagged_id FROM stock) 
             ELSE ps_id 
     END AS 'Tag'
FROM
    stock

The full error is:

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

CodePudding user response：

Your idea of using a case expression was correct, but you don't need another subquery, since both these columns belong to the same row anyway:

SELECT
     CASE
         WHEN tagged_id != ' ' 
             THEN tagged_id
             ELSE ps_id 
     END AS 'Tag'
FROM
    stock

CodePudding user response：

You are using a case expression - this expression must result in a single value. The error message is very clear as to the issue.

Your else part returns the single value ps_id, and the result of your case expression is a column named Tag.

The statement (select tagged_id from stock) selects a column from a table, and to comply with the expected result of the case expression, this must return a single value; even if you know a table contained a single row (I bet it doesn't), SQL server doesn't know this until run time, however the query is evaluated to be valid in all cases at compile time. After all, returning n values for a single column makes no sense, and SQL Server will not allow this to be possible.

SQL Server is expecting you to explicitely return a single value, for example using an aggregate function or top (1) clause.

Do you perhaps just mean to do

CASE WHEN tagged_id != ' ' THEN tagged_id ELSE ps_id END AS 'Tag'

CodePudding user response：

This line return more than 1 records

THEN (SELECT tagged_id FROM stock)

To avoid error your query must return only one record. You can add top 1

THEN (SELECT top 1 tagged_id FROM stock)

CodePudding user response：

This error occurs when we try using subquery that returns more than one value to the calling query. I usually Try each of the following steps.

1. If using a SQL job then launch the SQL Server Profiler to trace the exact location of the issue
2. Insert a TOP 1 condition on your SELECT statement --this is also filtering data to get unique value.
3. Try to use the WHERE clause in the SQL subquery, to filter for a unique value.

SELECT CASE WHEN tagged_id != ' ' THEN (SELECT TOP 1 tagged_id FROM stock) ELSE ps_id END AS 'Tag' FROM stock