Using subquery to return a field

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

Using subquery to return a field

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2011-10-11 : 12:33:34

I put together the following script to export data. I wanted to use the subquery to return the results as a field. i am trying to find the first completion date for each record.

select cr.id,
cr.prog_id as [CertType],
cr.item,
cr.description,
cr.status,
cr.grade_text as [Test Score],
cr.ENROLLED_DATE as [Date test was taken],
cr.COMPLETION_DATE as [Date test was taken],
cr.good_thru_date as [Expiration Date],
(select Min(completion_date) as [Initital CertDate]
from Cert_Register where completion_date is not NULL
group by student_id) as t
from cert_register cr
where registration_item in ('Enrolled', 'passed')
order by registration_item

When I run this it returns the following error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please help?

Thanks!
(0 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 12:40:23

not sure you want this



select cr.id,
cr.prog_id as [CertType], 
cr.item,
cr.description,
cr.status,
cr.grade_text as [Test Score],
cr.ENROLLED_DATE as [Date test was taken],
cr.COMPLETION_DATE as [Date test was taken],
cr.good_thru_date as [Expiration Date],
(select Min(completion_date) 
from Cert_Register where completion_date is not NULL) as [Initital CertDate]
from cert_register cr
where registration_item in ('Enrolled', 'passed')
order by registration_item

or this


select cr.id,
cr.prog_id as [CertType], 
cr.item,
cr.description,
cr.status,
cr.grade_text as [Test Score],
cr.ENROLLED_DATE as [Date test was taken],
cr.COMPLETION_DATE as [Date test was taken],
cr.good_thru_date as [Expiration Date]
from cert_register cr
inner join (select Min(completion_date) as [Initital CertDate],student_id
from Cert_Register where completion_date is not NULL
group by student_id) as t
on cr.student_id = t.student_id
and cr.completion_date = t.[Initital CertDate]
where cr.registration_item in ('Enrolled', 'passed')
order by cr.registration_item

try both and post if it fulfills your need.

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

rajarajan
Starting Member

48 Posts

Posted - 2011-10-11 : 12:50:48

visakh idea works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:06:58

welcome

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

dzabor
Posting Yak Master

138 Posts

Posted - 2011-10-11 : 13:53:19

Thanks - the second one worked! Amazing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:58:21

np

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources