An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING cla
- by brz dot net
I have to find the indentid from the status table based on below two conditions:
1. If there are more than one record having same indentid in status table and the same indentID has count1 in feasibilitystatus table then
I don't want to display the record.
2. If there is only one record of indentid in status table and the same indentID has count0 in feasibilitystatus table then
I don't want to display the record.
Query:
select distinct s.indentid
from status s where
s.status='true'
and s.indentid
not in(select case when count(s.indentid)>1 then (select indentid from feasibilitystatus
group by indentid having count(indentid)>1)
else (select indentid from feasibilitystatus group by
indentid having count(indentid)>0)
end as indentid from status)
Error:
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause or a select list, and the column being aggregated is
an outer reference.
