select subquery inside then of case when statement? or how to do otherwise?

Question

it's just an example. (My point is how can I do when I'm getting more then one result in the CASE statement):

SELECT 
,@PARAM AS id 
,Date=( 
SELECT distinct CASE
    WHEN P.DATE1 <= 05 and P.DATE2 <= 10
    THEN 'X'
    WHEN P.DATE1 > 05 and P.DATE2 <= 10
    THEN 'Y
    WHEN P.DATE2 > 10
    THEN 'Z' 
 END )
 FROM Prod AS P

The error is as follows:

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

You only have 1 query in your question, where/what is the subquery. You need to provide more context if you want anyone to try and help you. — Rhys Jones, Jan 27 '15 at 14:38
If you give an example and/or explain words what you want then we might know what you *want* your query to be. — philipxy, Jan 27 '15 at 15:10

score 1 · Answer 1 · answered Jan 27 '15 at 15:03

Your subquery is returning several rows. You need to modity it to returning a single row, with a single column, either by filtering, or by adding a TOP 1 to it. For example:

,Date=( 
SELECT distinct CASE
  WHEN P.DATE1 <= 05 and P.DATE2 <= 10
  THEN 'X'
  WHEN P.DATE1 > 05 and P.DATE2 <= 10
  THEN 'Y
  WHEN P.DATE2 > 10
  THEN 'Z' 
END 
WHERE -- >> your filter here <<
)

or

,Date=( 
SELECT TOP 1 distinct CASE
  WHEN P.DATE1 <= 05 and P.DATE2 <= 10
  THEN 'X'
  WHEN P.DATE1 > 05 and P.DATE2 <= 10
  THEN 'Y
  WHEN P.DATE2 > 10
  THEN 'Z' 
END )

The case when is only defining what value to retunr in each row, not replacing a number of rows with a single value.

philipxy · Answer 2 · 2015-01-27T16:05:07.477

You probably don't want a subselect/subquery, you proabably just want a case:

SELECT 
,@PARAM AS id 
,(CASE
    WHEN P.DATE1 <= 05 and P.DATE2 <= 10
    THEN 'X'
    WHEN P.DATE1 > 05 and P.DATE2 <= 10
    THEN 'Y
    WHEN P.DATE2 > 10
    THEN 'Z' 
 END) AS Date
 FROM Prod AS P

Your error was because Date = (SELECT ...) does a query (per row of P) and must have exactly one column and row returned to be set to Date.

score 0 · Answer 3 · edited Jan 27 '15 at 15:00

0

Currently wouldn't work if date was 10 as it would have matched all three of your conditions, you need to remove the = on your < and >.

SELECT DISTINCT CASE 
                WHEN Date < 10 
                THEN 'X'
                WHEN Date = 10 
                THEN 'Y'
                WHEN Date > 10 
                THEN 'Z'
                END

edited Jan 27 '15 at 15:00

philipxy

14,867
6
39
83

answered Jan 27 '15 at 14:39

Matt

14,906
27
99
149

It would work, just that it would always evaluate to `X` for the value *10* (See [SQLFiddle](http://sqlfiddle.com/#!3/d41d8/42851)). – Radu Gheorghiu Jan 27 '15 at 14:40
1

SQL Server short-circuits `case` statements, so the first matching expression will be selected – paul Jan 27 '15 at 14:41
That means the query essentially wouldn't work as it wouldn't return the required value for 10, which is `Y` – Matt Jan 27 '15 at 14:41
this is the error that the query generate : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Data Engineer Jan 27 '15 at 14:42
@BIProgrammer The subquery you are talking about is missing from the question. Update the question with the correct query. – Radu Gheorghiu Jan 27 '15 at 14:43
1

@BIProgrammer you need to include all of your code in your question – Matt Jan 27 '15 at 14:43
@Matt Your answer is not related to the question. Your answer solves another "problem" that the OP might or might not have (if he considers it important). – Radu Gheorghiu Jan 27 '15 at 14:44
@RaduGheorghiu *Not a mind reader* can only answer what is shown in the question – Matt Jan 27 '15 at 14:45
@Matt True, but the query he posted is not related to what the text of the question is: *"that returns more then 1 result"*. Anyway, the OP can take note of your answer, but I don't think he even knows he has this "problem" you're fixing for him. – Radu Gheorghiu Jan 27 '15 at 14:46
@RaduGheorghiu I went with the assumption that it meant *returns wrong result* i.e. returing X instead of Y when date is 10. Yeah lets see what the update brings :) .... – Matt Jan 27 '15 at 14:47
its just an example (My point is how can I do when i'm getting more then 1result in the Case statement): SELECT ,@PARAM AS id ,Date = ( SELECT distinct CASE WHEN P.DATE1 <= 05 and P.DATE2 <= 10 THEN 'X' WHEN P.DATE1 > 05 and P.DATE2 <= 10 THEN 'Y' WHEN P.DATE2 > 10 THEN 'Z' END ) FROM Prod AS P the error is as follow : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Data Engineer Jan 27 '15 at 14:51
@BIProgrammer Please edit a comment like this into your question (but we did it for you). – philipxy Jan 27 '15 at 16:12

select subquery inside then of case when statement? or how to do otherwise?

3 Answers3