CODEWITHSUNDEEP
sqlexists
Ron
Ron

Reputation: 53

How to fix “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?

My query is as follows, and contains a subquery within it:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from xxxx_snapshot.dbo.bill_prvdr_acct_history a
where a.acct_id in 
(select acct_id, count(*)
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1)

and i get the error message "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Upvotes: 4

Views: 53154

Answers (4)

befree2j
befree2j

Reputation: 371

TO simplify everything run your queries independently first to make sure you return one column:

select acct_id
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1
Above you are returning the acct_id

if it brings back the desired results include it in the main query:

SELECT a.bill_prvdr_acct_id,
       a.acct_id,
       a.bill_prvdr_acct_strt_dt,
       a.bill_prvdr_acct_end_dt
FROM   xxxx_snapshot.dbo.bill_prvdr_acct_history a
WHERE  a.acct_id IN (select acct_id
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1)

In that way it will be easy to confirm if the subquery return a single column and the desired results and linking it to the main query will make it easy if there is a problem in the main query

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270663

Given the structure of your query, window functions are probably an easier method to do what you want:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from (select a.*, count(*) over (partition by acct_id) as cnt
      from xxxx_snapshot.dbo.bill_prvdr_acct_history a
     ) a
where cnt > 1;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93754

Error is due to multiple columns selection in subquery.

You cannot select more than one column in subquery when you use IN operator in where clause.

As mentioned in error use EXISTS or remove the count(*) from subquery which is not needed

SELECT a.bill_prvdr_acct_id,
       a.acct_id,
       a.bill_prvdr_acct_strt_dt,
       a.bill_prvdr_acct_end_dt
FROM   xxxx_snapshot.dbo.bill_prvdr_acct_history a
WHERE  EXISTS (SELECT 1
               FROM   xxxx_snapshot.dbo.bill_prvdr_acct_history b
               WHERE  a.acct_id = B.acct_id
               GROUP  BY acct_id
               HAVING Count(*) > 1)

Or do a Inner Join

SELECT a.bill_prvdr_acct_id,
       a.acct_id,
       a.bill_prvdr_acct_strt_dt,
       a.bill_prvdr_acct_end_dt
FROM   xxxx_snapshot.dbo.bill_prvdr_acct_history a
       INNER JOIN (SELECT acct_id
                   FROM   xxxx_snapshot.dbo.bill_prvdr_acct_history b
                   GROUP  BY acct_id
                   HAVING Count(*) > 1) B
               ON a.acct_id = B.acct_id

Upvotes: 2

user359040
user359040

Reputation:

Count(*) is not necessary in the SELECT clause of the subquery, only in the HAVING clause - the following should work:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from xxxx_snapshot.dbo.bill_prvdr_acct_history a
where a.acct_id in 
(select acct_id
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1)

Upvotes: 2

Related Questions