CODEWITHSUNDEEP
sqldatabasescalar-subquery
Data Engineer
Data Engineer

Reputation: 33

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

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**

Upvotes: 0

Views: 934

Answers (3)

philipxy
philipxy

Reputation: 15148

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.

Upvotes: 1

JotaBe
JotaBe

Reputation: 39045

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.

Upvotes: 1

Matt
Matt

Reputation: 15061

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

Upvotes: 0

Related Questions