CODEWITHSUNDEEP
sqlsql-server-2008t-sqlreporting-servicesssrs-2008
user2974732
user2974732

Reputation: 103

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

I wrote this query for a ssrs report dataset. The report works fine for single parameter value and error is populated when multiple values are selected for parameter.

@iteration is a multivalued report parameter.

create table #hftable
( id int ,startdate datetime, environment nvarchar(50), iteration nvarchar(50))
insert #hgtable( id , startdate,environment , iteration)
select distinct wit.id, max(wit.startdate),environment , i.iteration
from Workers wit 
join iteration i 
on wit.sckid=i.sckid
and i.iterationlabel in (@iteration)
where system_rev =(SELECT MAX(system_rev) AS system_rev
              FROM workers 
              WHERE system_id = wit.system_id)
group by wit.id, i.iteration,environment 
order by i.iteration

select count(*) ,environment from #hftable 
group by iteration,environment 
order by iteration

Upvotes: 1

Views: 11449

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

This error relates to returning too many columns in your nested query that you are using to lookup relevant values. For example, this is wrong:

SELECT *
FROM MyTable
WHERE SomeColumn = (SELECT MAX(ThisColumn), MAX(ThatColumn) FROM OtherTable)

The nested query must return only one column for comparison:

SELECT *
FROM MyTable
WHERE SomeColumn = (SELECT MAX(ThisColumn) FROM OtherTable)

You don't have this problem in the query shown but this is what the error means. Maybe there is another query in your report that does this? Sometimes it takes the form of ordering:

SELECT *
FROM MyTable
WHERE SomeColumn IN (
    SELECT TOP 10 ThisColumn, COUNT(*) 
    FROM OtherTable 
    GROUP BY ThisColumn 
    ORDER BY COUNT(*) DESC
)

In this instance, you can still do the ordering required without returning COUNT(*) in the column list of the nested query.

Upvotes: 2

Related Questions