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

Question

I have the following query which gives me the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS". I am unable to figure what's technically wrong with this.

select clinicid,
       DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )) as YEAR,
       DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )) as MONTH,
       REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-') AS SalMonYY,
       (select rpTotal.clinicid,SUM(rpTotal.rpPackage) as rpPackage from 
        (
        select clinicid, count(1) as rpPackage
              from PackagePatients
              WHERE PackagePatients.clinicid = clinicid
              AND convert(date,patientdate) <  convert(date,saledate)
              group by clinicid
        union all
        select s.homeClinicId as clinicid,COUNT(1) as rpPackage
            from subscriptionHistory s 
            join patients p on s.ptientId = p.id 
            join products pl on s.productId = pl.id
            WHERE s.HomeClinicId = clinicid
            and pl.expDuration > 1
            group by s.homeClinicId) rpTotal group by rpTotal.clinicid) AS rpPackagesSold 
from PackagePatients

group by clinicid,
         DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )),
         DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )),
         REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-')

You have a subquery that you are using as a column but the subquery has more than 1 column defined. You should rewrite this entire query and instead join to the subquery. — Sean Lange
– Sean Lange, Commented Aug 7, 2014 at 18:44
The expression (select rpTotal.clinicid,SUM(rpTotal.rpPackage) has two columns. It is in a context where a scalar subquery is allowed, but a scalar subquery can only return one column from at most one row. — Gordon Linoff
– Gordon Linoff, Commented Aug 7, 2014 at 18:44
@SeanLange But my subquery has a union clause, so I am unable to figure out how to account for that if I join to the subquey — user3681350
– user3681350, Commented Aug 7, 2014 at 18:47
The union isn't the problem...well unless that query also returns more than 1 row. The problem is you are using a subquery as a column but it has two columns defined. How would it know which one you want? — Sean Lange
– Sean Lange, Commented Aug 7, 2014 at 18:48

Sean Lange · Accepted Answer · 2014-08-07 18:49:39Z

1

With nothing to work with I think you could rework this somewhat along these lines. This should be close. At least it will show you a way to make this work. Honestly, it probably needs to completely rewritten. I don't have the tables and such but I am sure this could be done without the subquery entirely.

select clinicid,
       DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )) as YEAR,
       DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )) as MONTH,
       REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-') AS SalMonYY,
       rp.rpPackagesSold

from PackagePatients
join
(select SUM(rpTotal.rpPackage) as rpPackagesSold, clinicid
     from 
        (
        select clinicid, count(1) as rpPackage
              from PackagePatients
              WHERE PackagePatients.clinicid = clinicid
              AND convert(date,patientdate) <  convert(date,saledate)
              group by clinicid
        union all
        select s.homeClinicId as clinicid,COUNT(1) as rpPackage
            from subscriptionHistory s 
            join patients p on s.ptientId = p.id 
            join products pl on s.productId = pl.id
            WHERE s.HomeClinicId = clinicid
            and pl.expDuration > 1
            group by s.homeClinicId) rpTotal group by rpTotal.clinicid
) AS rp on rp.clinicid = PackagePatients.clinicid
group by clinicid,
         DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )),
         DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )),
         REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-')

answered Aug 7, 2014 at 18:49

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3681350 Over a year ago

This looks promising and logical. but I am getting the "arithmetic overflow when converting expression to data type int"error on the first line of the code.

Sean Lange Over a year ago

I can't help you with that unless you post something I can code against. sqlfiddle is a great place to start for that sort of thing. My coding was in a blackhole with no information coming in.

AHiggins Over a year ago

@user3681350, what's the data type of pl.expDuration?

Jim V. · Accepted Answer · 2014-08-07 19:17:50Z

1

You need to move your derived table to the from clause, like so:

select clinicid,
       DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )) as YEAR,
       DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )) as MONTH,
       REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-') AS SalMonYY,
       SUM(rpPackagesSold.rpPackage) AS rpTotal

from PackagePatients
join 
(
        select clinicid, count(1) as rpPackage
              from PackagePatients
              WHERE PackagePatients.clinicid = clinicid
              AND convert(date,patientdate) <  convert(date,saledate)
              group by clinicid
        union all
        select s.homeClinicId as clinicid,COUNT(1) as rpPackage
            from subscriptionHistory s 
            join patients p on s.ptientId = p.id 
            join products pl on s.productId = pl.id
            WHERE s.HomeClinicId = clinicid
            and pl.expDuration > 1
            group by s.homeClinicId
) rpPackagesSold ON (rpPackagesSold.clinicid = PackagePatients.clinicid)


group by clinicid,
         DATEPART(YEAR,CONVERT(date,PackagePatients.SaleDate )),
         DATEPART(MONTH,CONVERT(date,PackagePatients.SaleDate )),
         REPLACE(RIGHT(CONVERT(VARCHAR(9),PackagePatients.SaleDate, 6), 6), ' ', '-')

edited Aug 7, 2014 at 19:17

answered Aug 7, 2014 at 18:49

Jim V.

2,17516 silver badges14 bronze badges

1 Comment

Sean Lange Over a year ago

You should use ANSI-92 style joins instead of the older 89 style joins you are using there.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related