这是我的Hive查询
INSERT OVERWRITE TABLE bts_monthly_points_liability_rollforward
SELECT currMonth.businessEventType,
prevMonth.totalFaceValue,
prevMonth.totalAccountingValue,
currMonth.earnedFaceValue,
currMonth.earnedAccountingValue,
currMonth.expiredFaceValue,
currMonth.expiredAccountingValue,
currMonth.earnedPointsReturnFaceValue,
currMonth.earnedPointsReturnAccountingValue,
currMonth.spendPointsFaceValue,
currMonth.spendPointsAccountingValue,
currMonth.spendPointsReturnFaceValue,
currMonth.spendPointsReturnAccountingValue,
currMonth.adjustmentFaceValue,
currMonth.adjustmentAccountingValue,
currMonth.totalFaceValue,
currMonth.totalAccountingValue
FROM
(
SELECT business_event_type AS businessEventType,
SUM(earned_face_value) AS earnedFaceValue,
SUM(earned_accounting_value) AS earnedAccountingValue,
SUM(expired_face_value) AS expiredFaceValue,
SUM(expired_accounting_value) AS expiredAccountingValue,
SUM(earned_return_face_value) AS earnedPointsReturnFaceValue,
SUM(earned_return_accounting_value) AS earnedPointsReturnAccountingValue,
SUM(spend_face_value) AS spendPointsFaceValue,
SUM(spend_accounting_value) AS spendPointsAccountingValue,
SUM(spend_return_face_value) AS spendPointsReturnFaceValue,
SUM(spend_return_accounting_value) spendPointsReturnAccountingValue,
CAST(0 AS BIGINT) AS adjustmentFaceValue,
CAST(0 AS BIGINT) AS adjustmentAccountingValue,
(SUM(earned_face_value)+SUM(expired_face_value)+SUM(earned_return_face_value)+SUM(spend_face_value)+SUM(spend_return_face_value)) AS totalFaceValue,
(SUM(earned_accounting_value)+SUM(expired_accounting_value)+SUM(earned_return_accounting_value)+SUM(spend_accounting_value)+SUM(currMonth.spend_return_accounting_value)) AS totalAccountingValue
FROM ${pointsApplicationName}_points_balance
WHERE unix_timestamp(accounting_date,'yyyy-MM-dd') >= unix_timestamp("${startDate}",'yyyy-MM-dd') AND unix_timestamp(accounting_date,'yyyy-MM-dd') < unix_timestamp("${endDate}",'yyyy-MM-dd')
GROUP BY business_event_type
)currMonth
JOIN
(
SELECT business_event_type AS businessEventType,
(SUM(earned_face_value)+SUM(expired_face_value)+SUM(earned_return_face_value)+SUM(spend_face_value)+SUM(spend_return_face_value)) AS totalFaceValue,
(SUM(earned_accounting_value)+SUM(expired_accounting_value)+SUM(earned_return_accounting_value)+SUM(spend_accounting_value)+SUM(spend_return_accounting_value)) AS totalAccountingValue
FROM ${pointsApplicationName}_points_balance
WHERE unix_timestamp(accounting_date,'yyyy-MM-dd') >= unix_timestamp("${previousMonthStartDate}",'yyyy-MM-dd') AND unix_timestamp(accounting_date,'yyyy-MM-dd') < unix_timestamp("${startDate}",'yyyy-MM-dd')
GROUP BY business_event_type
)prevMonth
ON prevMonth.businessEventType = currMonth.businessEventType;
运行此查询后收到的错误:
SemanticException [Error 10004]: Line 38:129 Invalid table alias or column reference 'currMonth': (possible column names are: business_event_type, accounting_date, earned_face_value, earned_accounting_value, expired_face_value, expired_accounting_value, earned_return_face_value, earned_return_accounting_value, spend_face_value, spend_accounting_value, spend_return_face_value, spend_return_accounting_value)
Command exiting with ret '255'
1条答案
按热度按时间2q5ifsrm1#
问题是查询的第35行。以下是查询的工作原理:
在这里,您可以看到在别名为currmonth的子查询中使用currmonth别名。此上下文中不存在别名,因此会出现错误。应该是这样的:
(SUM(earned_accounting_value)+SUM(expired_accounting_value)+SUM(earned_return_accounting_value)+SUM(spend_accounting_value)+SUM(spend_return_accounting_value)) AS totalAccountingValue