Creating new field inSQL Query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating new field inSQL Query

Author

Topic

parrot
Posting Yak Master

132 Posts

Posted - 2008-09-24 : 12:58:25

I want to be able to list the contents of a database field based on a selection test. For example, this is a sample query which I know is not right but it will explain what I am trying to do. I want to list 2 rate fields, the contents of which are determined by a test on another field. In this example, I want rate1 to contain the value based on when code is equal to 001 and rate2 to contain the value based on when code is equal to 002.

SELECT EMPLOYEE, NAME, CODE,
(SELECT RATE AS RATE1 FROM TABLE1 WHERE CODE = '001'),
(SELECT RATE AS RATE2 FROM TABLE1 WHERE CODE = '002')
FROM TABLE1 WHERE CODE = '001' OR CODE = '002'

When I run the query I get the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Basically, the error says that I cannot extract more than one value from a subquery expression which doesn't make sense.

Does anyone have a solution to accomplish the above?
Dave

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-24 : 13:02:10

SELECT EMPLOYEE, NAME, CODE,
CASE WHEN CODE = '001' THEN Rate ELSE null END as RATE1
,CASE WHEN CODE = '002' THEN Rate ELSE null END as RATE2

FROM
TABLE1 WHERE CODE IN ('001','002')

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 13:08:20

or may be this?

SELECT EMPLOYEE, NAME, CODE,
MAX(CASE WHEN CODE = '001' THEN Rate ELSE null END) as RATE1
,MAX(CASE WHEN CODE = '002' THEN Rate ELSE null END) as RATE2

FROM 
TABLE1 WHERE CODE IN ('001','002')
GROUP BY EMPLOYEE, NAME, CODE

parrot
Posting Yak Master

132 Posts

Posted - 2008-09-24 : 14:53:03

Thanks to both respondents. The last solution worked the best as it displays the results on one line. Thank God for these forums. As an Ex-COBOL programmer I am still discovering the nuances of SQL and trying to make SQL do what I can do with COBOL.
Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 14:58:08

quote:
Originally posted by parrot

Thanks to both respondents. The last solution worked the best as it displays the results on one line. Thank God for these forums. As an Ex-COBOL programmer I am still discovering the nuances of SQL and trying to make SQL do what I can do with COBOL.
Dave

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources