我正在尝试在 Teradata 中运行下面的代码。但是，当我尝试计算该交集的行数时，我不断收到错误。错误是：失败[2616：22003]计算期间发生数字溢出。
我尝试将 CAST 与 BIGINT 一起使用，但现在该值变为空。当我运行实际相交（没有 COUNT 子句）时 - 我能够看到该相交的行列表。我希望能够数出这个数字。你知道我该怎么做吗？
select CAST(count(a.main_id) AS BIGINT) from second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
database.release_day c ON a.release_key = c.release_key AND g_description = 'FW'
database.ft_feature d on a.main_id = d.main_id AND first_time >= 20200319
where action_date_key between 20200319 and 20200324 and a.main_id IN
(select a.main_id
From second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
where action_date > 20200324 and release_key = 200)
INTERSECT
select a.main_id
From second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
database.release_day c ON a.release_key = c.release_key AND g_description = 'FW'
database.ft_feature d on a.main_id = d.main_id AND DATE_KEY >= 20200319
where action_date_key between 20200319 and 20200324 and a.main_id IN
(select a.main_id
From second_database.tra_rock a
database.game_active b ON a.genome_key=b.genome_key AND description_detail <> 'AC'
where action_date > 20200324 and release_key = 200)
COUNT 仅应用于第一个选择，然后尝试将计数与第二个选择中的 main_id 相交。
您需要将完整查询包装到派生表或公共表表达式中：
select cast(count(*) as bigint)
from
(
select a.main_id from second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
database.release_day c ON a.release_key = c.release_key AND g_description = 'FW'
database.ft_feature d on a.main_id = d.main_id AND first_time >= 20200319
where action_date_key between 20200319 and 20200324 and a.main_id IN
(select a.main_id
From second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
where action_date > 20200324 and release_key = 200)
INTERSECT
select a.main_id
From second_database.tra_rock a
database.game_active b ON a.main_key=b.main_key AND description_detail LIKE 'AC'
database.release_day c ON a.release_key = c.release_key AND g_description = 'FW'
database.ft_feature d on a.main_id = d.main_id AND DATE_KEY >= 20200319
where action_date_key between 20200319 and 20200324 and a.main_id IN
(select a.main_id
From second_database.tra_rock a
database.game_active b ON a.genome_key=b.genome_key AND description_detail <> 'AC'
where action_date > 20200324 and release_key = 200)
) as dt
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
