与sql update等价的配置单元

n8ghc7c1  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(281)

我正在尝试将下面的查询转换为hive。
正在更新rpt表中的第\u层cd,但配置单元0.13不支持更新,因此使用更改为配置单元等效值和输出是不正确的。

UPDATE test_report A 
SET TIER_CD = (SELECT B.TIER_CD FROM lk  B
WHERE B.CLASS_CD = A.CLASS_CD AND B.RC_TYPE_CD = 'OATS' 
AND A.ACPTD_ROE_CT >= B.BEGIN_QT 
AND A.ACPTD_ROE_CT <= B.END_QT
AND EFCTV_DT = (SELECT MAX(C.EFCTV_DT) FROM LK C WHERE 
C.CLASS_CD = B.CLASS_CD AND C.RC_TYPE_CD = 'OATS' AND 
C.TIER_CD = B.TIER_CD AND
C.EFCTV_DT <= ?
AND B.TIER_CD <> 'I')
WHERE A.YEAR_CD = ?
AND A.MONTH_CD = ?;

Hive当量低于

select z.*,x.tier_Cd from
(
select * from test_report
where year_Cd=?
and month_Cd=?')z
left outer join
(SELECT B.TIER_CD TIER_cD, A.MKT_CLASS_CD,A.year_cd FROM LK B, test_report A
WHERE B.CLASS_CD = A.CLASS_CD AND B.RC_TYPE_CD = 'OATS' 
AND A.ACPTD_ROE_CT >= B.BEGIN_QT 
AND A.ACPTD_ROE_CT <= B.END_QT
AND EFCTV_DT = (SELECT MAX(C.EFCTV_DT) FROM LK C WHERE 
C.CLASS_CD = B.CLASS_CD AND C.RC_TYPE_CD = 'OATS' AND 
C.TIER_CD = B.TIER_CD AND
C.EFCTV_DT <= ?)  
AND B.TIER_CD <> 'I')x
on(z.class_Cd=x.Class_Cd)

请帮助解决问题。

dtcbnfnu

dtcbnfnu1#

配置单元不支持多个子查询,相反,您可以仅使用联接连接三个表。我猜你在附近犯了个错误 select (max

相关问题