May 8, 2012 at 2:56 pm
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.
Case may not be the best route as I will have multiple values, but I am trying to figure the best way to write the query. My intent is if the oqt field = 26 then I need to it do a inner join but if the oqt field = 31 I need it to do an left outer join. All other oqt numbers it would just select everything from the tempoq table.
This is my query:
SELECT oqt,
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 = (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 = (SELECT tempoq.oqt FROM tempoq INNER JOIN OQMethods ON tempoq.oqt = OQMethods.id_oqt) and active = 1)
END
from tempoq
May 8, 2012 at 3:05 pm
Wow that is a bit off of how case works in sql. You are trying to use it to capture a bunch of rows and columns all at once. It doesn't do that. It can only work for a single column of data.
If you can post some ddl, sample data and desired output I would be happy to help you with this. I just can't even begin to straighten out what you have here.
Take a look at the first link in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 3:27 pm
I maybe thinking to hard on this one,
But basically, I am selecting this:
Select qm.oqt, qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
FROM OQMethods QM
But it's the joins that are conditional-
If the qm.oqt = 26 then I will have a left outer join
left outer join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0
But if the qm.oqt = 31 then I will have a inner join
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 where tempoq.oqt = QM.id_oqt)and active = 1)
I just don't know how to script this in sql the correct way
May 8, 2012 at 3:29 pm
I would agree that you are over thinking this, but given the code you posted I think you need some help to resolve it. I will be happy to help you but you have to help me by reading the article I suggested and posting ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply