Hive:不支持的子查询表达式' extract_date':只有最高级别的子查询表达式
我对Hive是新手,目前我正在使用WSL2上的Hive 3.1.2,我正在尝试使用命令 hive -f test2subquery.sql 在Hive CLI上运行此SQL脚本:
use mydb;
SELECT
date_photo as extract_date, camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
FROM
extract_resa
WHERE
extract_date = (select CAST(y.extract_date as date) from app_params y)
AND extract_date>= datein
AND extract_date< date_add(datein, nb_nuit_fid)
AND nb_nuit_fid > 0
AND statres <> 'X'
AND id_stay_to = 'NULL' -- eliminer les TO
AND idstay <> idstay_res -- seulement les residents reel
AND client_id_grp <> -1
GROUP BY
extract_date , camping_code
)e
GROUP BY
date_photo, camping_code
但是我一直在开始此错误:不支持的子查询表达式“ extract_date':仅允许使用最高级别的子查询表达式 我尝试在子查询中使用别名,但仍然遇到相同的错误。
use mydb;
SELECT
s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
r.extract_date, r.camping_code, count(*) as nb_nuit_fid
FROM
extract_resa r
WHERE
r.extract_date = (select CAST(y.extract_date as date) from app_params y)
AND r.extract_date>= r.datein
AND r.extract_date< date_add(r.datein, nb_nuit_fid)
AND nb_nuit_fid > 0
AND r.statres <> 'X'
AND r.id_stay_to = 'NULL' -- eliminer les TO
AND r.idstay <> r.idstay_res -- seulement les residents reel
AND r.client_id_grp <> -1
GROUP BY
r.extract_date , r.camping_code
)s
GROUP BY
date_photo, camping_code
当评论行 extract_date&lt&lt时,我也感到困惑。 date_add(datein,nb_nuit_fid)这样:
use mydb;
SELECT
s.date_photo as extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
FROM
extract_resa
WHERE
extract_date < (select cast(y.extract_date as date) from app_params y)
AND
extract_date >= datein
/* AND extract_date < date_add(datein, nb_nuit_fid) */
AND nb_nuit_fid > 0
AND statres <> 'X'
AND id_stay_to = 'NULL' -- eliminer les TO
AND idstay <> idstay_res -- seulement les residents reel
AND client_id_grp <> -1
GROUP BY
extract_date, camping_code
)s
GROUP BY
date_photo, camping_code
脚本运行顺利。请帮忙。
I am kinda new to HIVE and I am currently using HIVE 3.1.2 on WSL2 and I am trying to run this SQL script on HIVE CLI using the command hive -f test2subquery.sql:
use mydb;
SELECT
date_photo as extract_date, camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
FROM
extract_resa
WHERE
extract_date = (select CAST(y.extract_date as date) from app_params y)
AND extract_date>= datein
AND extract_date< date_add(datein, nb_nuit_fid)
AND nb_nuit_fid > 0
AND statres <> 'X'
AND id_stay_to = 'NULL' -- eliminer les TO
AND idstay <> idstay_res -- seulement les residents reel
AND client_id_grp <> -1
GROUP BY
extract_date , camping_code
)e
GROUP BY
date_photo, camping_code
But I keep getting this error: Unsupported SubQuery Expression 'extract_date': Only SubQuery expressions that are top level conjuncts are allowed
I tried using alias inside the subquery but i still get the same error.
use mydb;
SELECT
s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
r.extract_date, r.camping_code, count(*) as nb_nuit_fid
FROM
extract_resa r
WHERE
r.extract_date = (select CAST(y.extract_date as date) from app_params y)
AND r.extract_date>= r.datein
AND r.extract_date< date_add(r.datein, nb_nuit_fid)
AND nb_nuit_fid > 0
AND r.statres <> 'X'
AND r.id_stay_to = 'NULL' -- eliminer les TO
AND r.idstay <> r.idstay_res -- seulement les residents reel
AND r.client_id_grp <> -1
GROUP BY
r.extract_date , r.camping_code
)s
GROUP BY
date_photo, camping_code
what also confuses me is when commenting the line extract_date< date_add(datein, nb_nuit_fid) like this:
use mydb;
SELECT
s.date_photo as extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM
(
SELECT
extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
FROM
extract_resa
WHERE
extract_date < (select cast(y.extract_date as date) from app_params y)
AND
extract_date >= datein
/* AND extract_date < date_add(datein, nb_nuit_fid) */
AND nb_nuit_fid > 0
AND statres <> 'X'
AND id_stay_to = 'NULL' -- eliminer les TO
AND idstay <> idstay_res -- seulement les residents reel
AND client_id_grp <> -1
GROUP BY
extract_date, camping_code
)s
GROUP BY
date_photo, camping_code
the script runs smoothly. please help.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

将您的子查询子句转换为内在加入。
我不确定评论该线路如何工作。请让我知道结果。
convert your sub query clause to inner join.
I am not sure how commenting that line is working. Pls let me know the outcome.