impala case语句中的子查询

wrrgggsh  于 2021-06-26  发布在  Impala
关注(0)|答案(1)|浏览(1109)
select
a.time, b.name, c.user_city, 
case
    when c.user_country='xxxx' and c.user_state in (select cast(state_id as string) from states_list)
    then (select d.STATE_NAME from States_LIST d where d.STATE_ID = cast(c.user_state as int) )
    else c.user_state 
end state,
case
    when c.user_country in (select cast(COUNTRY_ID as string) from COUNTRIES_LIST) 
    then (select e.COUNTRY_NAME from COUNTRIES_LIST e where e.COUNTRY_ID = cast(c.user_country as int)) 
    else null
end country, col6, col7, ......
from ......
where.......

在impala中执行上述查询时,我遇到以下错误:
不兼容的返回类型表达式“select d.state\u name from states\u list d where d.state\u id=cast(c.user\u state as int)”和“c.user\u state”的数组和字符串
请帮忙!!!!

3mpgtkmj

3mpgtkmj1#

如错误消息所示,您试图在同一个案例中同时分配字符串和数组。因此,case语句只能在子查询只有一个输出的情况下工作。
为此,您需要一个标量子查询。根据需要解决的问题,可以尝试使用聚合函数。
来自impala文件:
标量子查询生成一个结果集,其中一行包含一列,通常由聚合函数(如max()或sum()生成)
第二个原因是impala不允许在select子句中使用子查询。
子查询可以返回一个结果集,以便在from或with子句中使用,或与in或exists等运算符一起使用。
根据您的表,您必须通过加入 d 以及 e 表,因此子查询的需求消失了。例子:

select
a.time, b.name, new.user_city, 
case
    when new.user_country='xxxx' and new.user_state is not null
    then new.STATE_NAME
    else new.user_state 
end state,
e.country_name country,
col6, col7, ......
from
a,
b,
countries_list e right outer join
        (select * from
            (select * from states_list) as d
             right outer join c on cast(d.state_id as string)=c.user_state
         ) as new
on e.COUNTRY_ID = cast(new.user_country as int)
,..
where
...

请让我知道其中一个是否解决了你的问题。

相关问题