Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case in subquery

Author  Topic 

alex weber
Starting Member

4 Posts

Posted - 2011-06-04 : 01:14:05
I am trying to use a subquery to pull a field in a script.

select n.id,
n.Last_Updated as 'Last Updated',
(SELECT Case http://www.papalocksmith.com/

WHEN member_Type in('OWNER','EXEC','AFF','STU','HLM') THEN 'Member'
else 'Guest Member'
END as NewMemberType
from name ) as 'Member Type Code',
'' as 'Primary Group Code',
n.Last_Name as 'Last Name',
n.First_Name as 'First Name'
from name n

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

I tried to use Top 1, but it returns the same values for all, 'Member'.

Any idea how I can get it to return that field value as 'Member' for the first set specified and 'Guest Member' for the rest?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-04 : 01:54:31
[code]
select n.id,
n.Last_Updated as 'Last Updated',
Case WHEN member_Type in('OWNER','EXEC','AFF','STU','HLM') THEN 'Member'
ELSE 'Guest Member'
END as NewMemberType as 'Member Type Code',
'' as 'Primary Group Code',
n.Last_Name as 'Last Name',
n.First_Name as 'First Name'
from name n
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-05 : 03:37:53
better to avoid '' for aliases as it gets confused with constant string values returned

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-06 : 23:30:06
You keep doing bad postings. Columns are not fields. We do not keep audit information in tables being audited. We do not format display headers in aliases. We do not use blank columns as spacers for display. Tables are plural or collective names because they model sets and an alias is in double quotes, not single ones. You do not understand that CASE is an expression.

SELECT member_id,
CASE WHEN member_type
IN ('OWNER', 'EXEC', 'AFF', 'STU', 'HLM')
THEN 'Member' ELSE 'Guest Member' END
AS member_type,
last_name, first_name
FROM Membership;



--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
Go to Top of Page
   

- Advertisement -