SQL Oracle ORA-00904:无效标识符错误

Question

有人能帮我找出为什么这段代码返回一个SQLOracle ORA-00904：无效的索引符错误吗？查询在没有Country='阿根廷'的情况下有效，但我需要按此值向下过滤。有人能给我指出正确的方向吗？

select llattrdata.ID dataid,

max(case when llattrdata.ATTRID = 4 then llattrdata.VALSTR end) Country

from llattrdata, dtree 

where
llattrdata.DEFID = 19400074 and
llattrdata.VERNUM = dtree.VERSIONNUM and
llattrdata.ID = dtree.DATAID and 
Country = 'Argentina'
group by llattrdata.ID

Answer 1

也许我错过了一些东西，但这与以下情况不同吗：

select distinct
  llattrdata.ID     dataid,
  llattrdata.VALSTR Country
from
  llattrdata,
  dtree 
where
  llattrdata.VERNUM        = dtree.VERSIONNUM and
  llattrdata.ID            = dtree.DATAID     and 
  llattrdata.DEFID         = 19400074         and
  llattrdata.ATTRID        = 4                and
  llattrdata.VALSTRCountry = 'Argentina'

Answer 2

根据分组条件进行筛选时，需要使用< code>having而不是< code>where。

应该是这样的:

select llattrdata.ID dataid,
        max(case when llattrdata.ATTRID = 4 then llattrdata.VALSTR end) Country
  from llattrdata, dtree 
 where llattrdata.DEFID = 19400074 
   and llattrdata.VERNUM = dtree.VERSIONNUM 
   and llattrdata.ID = dtree.DATAID 
 group by llattrdata.ID
having max(case when llattrdata.ATTRID = 4 then llattrdata.VALSTR end) = 'Argentina'