CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60691

Using a select statement as one of the columns

I have a query:

select 
      index_imsid
    , SUM(weighted_value) sumWeightedValue
    , (
        select 
              top 1 percentof
            , [Plan_Name_OR_Payment_Type] 
        from [v_PlanPerProvider1] 
        where [PLAN_RANK]=1
      ) plan1
from [v_PlanPerProvider1]
where plan_rank between 1 and 10
group by index_imsid
order by 1

and I am getting this error:

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

Can you please help me to understand why I am getting this error?

It appears that it does not like the select statement as one of the columns?

Upvotes: 3

Views: 56734

Answers (2)

GilM
GilM

Reputation: 3761

Or you can do this:

select  
    t1.index_imsid, SUM(t1.weighted_value) sumWeightedValue, t2.percentof, t2.[Plan_Name_OR_Payment_Type]
from [v_PlanPerProvider1] t1
cross join (select top 1 percentof, [Plan_Name_OR_Payment_Type] from [v_PlanPerProvider1] where [PLAN_RANK]=1) t2 
where t1.plan_rank between 1 and 10 
group by t1.index_imsid, t2.percentof, t2.[Plan_Name_OR_Payment_Type]
order by 1

Upvotes: 3

Todd Li
Todd Li

Reputation: 3269

It does not like you selecting two columns in the subquery. You can only select one column at a time.

  select index_imsid, SUM(weighted_value) sumWeightedValue,
    (select top 1 percentof from [v_PlanPerProvider1] where [PLAN_RANK]=1) percentof
    (select top 1 [Plan_Name_OR_Payment_Type] from [v_PlanPerProvider1] where [PLAN_RANK]=1) Plan_Name_OR_Payment_Type
  from [v_PlanPerProvider1]
  where plan_rank between 1 and 10
  group by index_imsid
  order by 1

Upvotes: 9

Related Questions