Correlated subquery in THEN clause of CASE statement

Question

Can I relate datasets from WHEN & THEN clauses of a Case Statement.

 

Answer 1

yup1 wrote:

Can I relate datasets from WHEN & THEN clauses of a Case Statement.

Case is an expression used to return a value based on the logic of the expression.  With that said, you can relate the values of two or more tables that participate in some type of join to the main query.  You can also use a case expression to evaluate different column in the same table.  And lastly you can compare table values to sub queried values.

 

The answer is yes, but I discourage the use of correlated subqueries, as they will be evaluated for every row returned in the main query.

Code Snippet

declare @t table(

id int,

col char(1)

)

insert into @t values (1,'a');

insert into @t values (1,'b');

insert into @t values (2,'c');

select a.id,

case when a.col = 'a' then (select max(b.col) from @t b where b.id = a.id) else col end

from @t a

 

 

Better solution:

Code Snippet

select a.id,

case when a.col = 'a' then b.maxcol else a.col end

from @t a

inner join(

select id, max(col) maxcol

from @t

group by id

) as b

on a.id = b.id

 

 

Answer 2

Please try this..

 

create table test1(col1 int,col2 varchar(10))

create table test2(col1 int,col2 varchar(10))

 

 

insert into test1

select 1,'Amy'

union all

select 2,'Bob'

union all

select 3,'Cat'

 

insert into test2

select 1,'Amy'

union all

select 6,'Bob'

union all

select 7,'Cat'

union

select 8,'Dolly'

 

select *,col2 =

case when col2='Bob' then 'Bobby'

when col2='Cat' then 'Catherin'

end

from test1 t1

where exists( select * from test2 t2

where t1.col1=t2.col1 )

 

Result:

col1  col2     col2

1       Amy    NULL

 

Answer 3

Well the answer "yes" depends on the interpretation of the question.  The post's subject line asks if it is possible to use a correlated subquery in the then clause of a case expression.  This answer is yes.  The other question asked is "can I relate datasets in the when then clause of a case expression".  The answer to this is yes. You can relate the data by joining it together and comparing the values against one another.  Any column from any table, whether it be a base table, view or derived table can be compared against the another table's value, a subquery value, or an expression value. 

 

As you stated ealier, you cannot compare all the data in a subquery vs another subquery with a case statement.  The expression has to be a logical value to value expression.  For my interpretation the answer is yes, but for your interpretation the answer is no.  I do not know which question specifcally the OP is asking.

Answer 4

This posting makes no sense at all!  

There is no CASE Statement in SQL! We have a CASE expression. The WHEN clauses hold predicates. The THEN clauses hold scalar expressions. The CASE expression returns a scalar  value.  There is no data set anywhere. 

Have you ever read even one book on SQL? Please do so. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL