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

Question

Hi All,

The following query gives the error

Msg 116, Level 16, State 1, Procedure rptMonthlyFuelDelivery, Line 35
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS .


The query is:

select
case when Category='M'
then
(

select
s.Name,
s.age,
m.mark1,
m.mark2

from Students as s
inner join marks as m on s.studentid = m.studentid
)


else
(

select
GN.Category,
S.Name,
s.age,
m.mark1,
m.mark2

from students as s
inner join gender as GN on s.studentid=GN.studentid
inner join marks as m on m.studentid=s.studentid
)
END
from Gender

Help me What modifications i have to do to clear the error..?

Thank you.

Answer 1

The SELECT lists in the subqueries must not have > 1 column.

---

Tom https://mvp.support.microsoft.com/profile/Tom.Moreau

Answer 2

Hi Tom,

Is there any other possible way to run the query with some modifications?

 

 

Answer 3

Try

 

select
case when GN.Category = 'M' then '' else GN.Category as Category,
S.Name,
s.age,
m.mark1,
m.mark2

from students as s 
inner join gender as GN on s.studentid=GN.studentid
inner join marks as m on m.studentid=s.studentid

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Hi Naom,

Thanks for your suggestion.

but I want the query to be executed like this..

 

If the condition is true,

That particular part of query must execute,

else the another part must execute.

 

Can you please tell me the method of querying for that?

 

Thank you.

 

Thank you.

 

 

 

Answer 5

This will work only if the condition is static (e.g. it's not based on a column):

if @SomeCondition = 'M'

  select something here

else

   select something else here

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 6

This will work only if the condition is static (e.g. it's not based on a column):

---

Naomi Nosonovsky, Sr. Programmer-Analyst

This is not entirely true, because you can use OUTER APPLY or CROSS APPLY.
The thing that however makes this request impossible is that OP wants different number of columns depending on a row criteria.
Can't be done. Whole resultset must have same number of columns.

 

Answer 7

Any progress?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog