蜂巢中不支持嵌套子查询 [英] Nested subquery not supported in hive

问题描述

我们在蜂巢中尝试了以下查询.但得到错误.请帮助我以其他任何方式解决此问题.

We have tried the below query in hive. but getting the error. please help me to resolve this in any other way.

select count(1) as OpenItems from issues i , issue_statuses s  

where s.id = i.status_id 

and  s.name NOT IN ('Closed','Passed','Rejected','On 

Hold','Baselined','Completed')

and i.project_id IN 

(select id from projects3 from

CASE WHEN ${projectname} = 'All' then id in

(select p.id from members m, projects3 p ,users_1 u

where m.project_id = p.id and u.id = m.user_id and u.status = '1'

and u.id IN 
(select u1.id from users_1 u1, Supervisor_hierarchy s1 where u1.mail = s1.email and s1.name = ${Superisorname})
group by p.id)
WHEN (${projectname} <>'All' and ${SubProject projectname} ='All') then id 
IN (select id from (select id from project_closure where parent_id in (select id from projects where name = ${projectname}) group by id)a)
WHEN (${SubProject projectname}<>'All' and ${projectname}<> 'All') then id 
IN (select id from(select id from project_closure where id in (select id from projects  where name = ${SubProject projectname}) group by id)a)
END
order by id)

---

错误:6:5不支持的子查询表达式'id':子查询表达式同时引用了父查询和子查询表达式，并且不是有效的联接条件.

---

error: 6:5 Unsupported SubQuery Expression 'id': SubQuery expression refers to both Parent and SubQuery expressions and is not a valid join condition.

推荐答案

我知道已经晚了，但是发布给所有遇到此问题的人.

I know it is late but posting for anyone who face this issue.

当我们遇到以下一个或多个Hive子查询限制时，就会发生此问题.

This issue occurs when we encounter one or more of the below limitations of Hive Subqueries.

在这种情况下，在第4个限制下的Group By子句中使用了对父查询的引用.

In this scenario, the reference to the parent query is used in Group By clause which comes under the 4th limitation.

配置单元子查询限制

1. 仅在表达式的右侧支持这些子查询.
2. IN/NOT IN子查询只能选择一个列.
3. 存在/不存在必须具有一个或多个相关谓词.
4. 仅在子查询的WHERE子句中支持对父查询的引用.

来源: 查看全文