Returning more from the sub 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 2000 Forums

Transact-SQL (2000)

Returning more from the sub query !

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-06 : 13:02:14

Hi,

I am having problems in returning multiple records from the subquery. My complete query is written below: but the problem lies in over here:
StartDate = CASE
WHEN e.IsEditable = 1 THEN
(
SELECT edcc.ExamStartDate FROM test_ExamDatesClassCodes edcc
JOIN test_ExamDates ed ON ed.ExamDateID = edcc.ExamDateID
WHERE edcc.ClassCode = @ClassCode

Now, sometimees the subquery can return multiple items so how can I check for them.

DECLARE @ClassCode varchar(10)
SET @ClassCode = '9999'

DECLARE @ExamTypeID int
SELECT @ExamTypeID = ExamTypeID FROM
UserClassCodeCategories uccc WHERE uccc.ClassCode = @ClassCode

SELECT
e.ExamID,
e.Title,
e.[Description],
e.Duration,
ed.ExamDateID,
e.TotalQuestions,
e.IsEditable,
<b>
StartDate = // This has to be IN operator but how
CASE
WHEN e.IsEditable = 1 THEN
(

SELECT edcc.ExamStartDate FROM test_ExamDatesClassCodes edcc
JOIN test_ExamDates ed ON ed.ExamDateID = edcc.ExamDateID
WHERE edcc.ClassCode = @ClassCode

)
ELSE ed.StartDate
END,
</b>
EndDate =
CASE
WHEN e.IsEditable = 1 THEN
(
SELECT edcc.ExamEndDate FROM test_ExamDatesClassCodes edcc
JOIN test_ExamDates ed ON ed.ExamDateID = edcc.ExamDateID
WHERE edcc.ClassCode = @ClassCode
)
ELSE ed.EndDate
END

FROM test_Exams e
INNER JOIN test_ExamDates ed
ON ed.ExamID = e.ExamID
WHERE
e.ExamStatusID = 1 AND
e.ExamTypeID = @ExamTypeID

Mohammad Azam
www.azamsharp.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 14:29:47

Or you could use TOP 1 in your subquery.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-06 : 14:36:53

I can't do that since I will need all the records.

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 14:47:24

quote:
Originally posted by azamsharp

I can't do that since I will need all the records.

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

Then you have to rewrite your logic since you are trying to put several dates into one column named StartDate, and that's not allowed. One column, one value.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-06 : 16:12:12

Thanks I used the UNION operator to join the two queries together and now it is working !

Thanks for all your help!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources