CODEWITHSUNDEEP
sqlsql-server-2008
Michelle Kamp
Michelle Kamp

Reputation: 1

Trouble with case statement

I am having problems with this case statement. I don't know what I am doing wrong but I get the error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have a case when the field equals a value then do a left outer join but if the field equals a different value then do a inner join. This is my query:

SELECT

   case 
    when oqt = '26' then 


            (Select qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
             FROM OQMethods QM left outer join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0 
             WHERE QM.id_oqt in (SELECT  tempoq.oqt FROM tempoq INNER JOIN OQMethods ON tempoq.oqt = OQMethods.id_oqt)and active = 1) 
             END,
case
when oqt = '31' then
            (Select qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
             FROM OQMethods QM inner join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0
             where QM.id_oqt in (SELECT  tempoq.oqt FROM tempoq INNER JOIN OQMethods ON tempoq.oqt = OQMethods.id_oqt) and active = 1) 

    END
from tempoq

Upvotes: 0

Views: 78

Answers (3)

fankt
fankt

Reputation: 1047

Select qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
 FROM OQMethods QM 
 inner join tempoq on tempoq.oqt = QM.id_oqt
 left outer join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0 
 WHERE active = 1 and (tempoq.oqt = '26' or (tempoq.oqt = '31' and courseversions.* is not null))

left outer join means join OQMethods's data which even no match data from courseversions, then filter the data with null courseversions.* that is inner join.

Hope I have the right understanding.

Upvotes: 0

Purplegoldfish
Purplegoldfish

Reputation: 5284

It looks like you want to do something like this rather than using a CASE statement.

DECLARE @t int
-- This would contain your oqt value
SET @t =1


IF @t = 1 
BEGIN
   SELECT * FROM tableA
END
ELSE IF @t = 2
BEGIN
   SELECT * FROM TableB
END

Upvotes: 2

Ralph Shillington
Ralph Shillington

Reputation: 21098

The case is an expression that must evaluate to a value. The Select statements that you have return multiple values.

It would seem that you're trying to use Case like it's a C# switch? If that's the case, then you're likely better off with an IF ELSE IF construction.

Upvotes: 4

Related Questions