在 Teradata 中使用 COUNT 和 INTERSECT 时遇到问题

我正在尝试在 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