none
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.

    Monday, June 07, 2010 2:47 PM

Answers

  • 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.

     

    Tuesday, June 08, 2010 9:15 AM

All replies

  • The SELECT lists in the subqueries must not have > 1 column.
    Tom https://mvp.support.microsoft.com/profile/Tom.Moreau
    • Edited by Tom Moreau Monday, June 07, 2010 3:02 PM
    Monday, June 07, 2010 2:59 PM
  • Hi Tom,

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

     

     

    Monday, June 07, 2010 3:01 PM
  • 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
    Monday, June 07, 2010 3:12 PM
    Moderator
  • 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.

     

     

     

    Monday, June 07, 2010 3:19 PM
  • 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
    Monday, June 07, 2010 3:23 PM
    Moderator
  • 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.

     

    Tuesday, June 08, 2010 9:15 AM
  • Any progress?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, June 27, 2010 4:53 PM
    Moderator