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.
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 NewMemberTypefrom name ) as 'Member Type Code','' as 'Primary Group Code',n.Last_Name as 'Last Name',n.First_Name as 'First Name'from name nI 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] |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|