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

本文介绍了在 Teradata 中使用 COUNT 和 INTERSECT 时遇到问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

我正在尝试在 Teradata 中运行以下代码.但是，当我尝试计算此交叉点的行数时，我不断收到错误消息.错误是:Failed [2616 : 22003] 计算过程中发生数值溢出.

I am trying to run the code below in Teradata. However, I keep getting an error when I try to count the number of rows this intersection has. The error is: Failed [2616 : 22003] Numeric overflow occurred during computation.

我尝试将 CAST 与 BIGINT 一起使用，但现在该值变为空.当我运行实际的相交(没有 COUNT 子句)时 - 我能够看到这个相交的行列表.我希望能够数出这个数字.你知道我该怎么做吗?

I tried using a CAST with BIGINT, but now the value comes empty. When I run the actual intersect (without the COUNT clause) - I am able to see the list of rows of this intersect. I want to be able to count this number. Do you know how I can do this?

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 仅应用于第一个 Select，然后您尝试将计数和第二个 Select 的 main_id 相交.

The COUNT is applied to the first Select only and then you try to Intersect the counts and the main_id from the second Select.

您需要将完整查询包装到派生表或公用表表达式中:

You need to wrap the full query into a Derived Table or a Common Table Expression:

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

这篇关于在 Teradata 中使用 COUNT 和 INTERSECT 时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！