尝试运行查询时teradata中出现错误3807

i1icjdpr  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(449)

teradata数据库
当我做左连接时,返回的行数更多。我的volatile表中的基本填充是559157行。我要做的是只返回基本人口中的559157行。但运行此查询后:

SELECT distinct a.*, b.column1, b.column2, b.column3, b.column4, b.column5, b.column6, c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
,CASE 
WHEN b.column5 < a.column6 1
      ELSE 0
      END as column_open_flag
,CASE
WHEN a.column4 < 580 THEN 1
     WHEN a.column4 between 580 and 619 THEN 2
     WHEN a.column4 between 620 and 639 THEN 3
     WHEN a.column4 between 640 and 659 THEN 4
     WHEN a.column4 between 660 and 679 THEN 5
     WHEN a.column4 between 680 and 699 THEN 6
     WHEN a.column4 between 700 and 739 THEN 7
     WHEN a.column4 >= 740 THEN 8
     ELSE 0
     END as column4_band
FROM volatile_table1 a
LEFT JOIN database_table1 b
ON a.column1 = b.column0
LEFT JOIN volatile_table2 c
ON a.column1 = c.column2
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3

现在返回的行数超过2000000行。所以我想试试工会条款。以下是我的最新查询:

SELECT a.column1, a.column2, a.column3, a.column4, a.column5, a.column6
,CASE 
WHEN b.column5 < a.column6 1
      ELSE 0
      END as column_open_flag,
CASE
WHEN a.column4 < 580 THEN 1
     WHEN a.column4 between 580 and 619 THEN 2
     WHEN a.column4 between 620 and 639 THEN 3
     WHEN a.column4 between 640 and 659 THEN 4
     WHEN a.column4 between 660 and 679 THEN 5
     WHEN a.column4 between 680 and 699 THEN 6
     WHEN a.column4 between 700 and 739 THEN 7
     WHEN a.column4 >= 740 THEN 8
     ELSE 0
     END as column4_band
FROM volatile_table1 a
UNION
SELECT b.column1, b.column2, b.column3, b.column4, b.column5, b.column6
FROM database_table1 b
UNION
SELECT c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
FROM volatile_table2 c
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3

现在我不断得到3807错误:对象“a”不存在。我跑了一辆

HELP volatile table

查询,它表示这两个易失性表确实存在。有人能告诉我错误的方向吗(我还尝试删除第一个case语句,该语句引用b.column5<a.column6。相同的错误)。
编辑:
我从注解更新到这个,现在出现错误3653。'所有选择列表不包含相同数量的表达式:

SELECT distinct a.column1, a.column2, a.column3, a.column4, a.column5, a.column6, b.column1, b.column2, b.column3, b.column4, b.column5, b.column6, c.column3, SUM(column3) AS Total_Profit, AVG(column3) AS Profit_Averag
,CASE 
WHEN b.column5 < a.column6 1
      ELSE 0
      END as column_open_flag
,CASE
WHEN a.column4 < 580 THEN 1
     WHEN a.column4 between 580 and 619 THEN 2
     WHEN a.column4 between 620 and 639 THEN 3
     WHEN a.column4 between 640 and 659 THEN 4
     WHEN a.column4 between 660 and 679 THEN 5
     WHEN a.column4 between 680 and 699 THEN 6
     WHEN a.column4 between 700 and 739 THEN 7
     WHEN a.column4 >= 740 THEN 8
     ELSE 0
     END as column4_band
FROM 
(
SELECT a.column1, a.column2, a.column3, a.column4, a.column5, a.column6
FROM volatile_table1 a
UNION ALL
SELECT b.column1, b.column2, b.column3, b.column4, b.column5, b.column6
FROM database_table1 b
UNION ALL
SELECT c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
FROM volatile_table2 c
GROUP BY 1
) d
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3
bvk5enib

bvk5enib1#

如果要在执行union all之后进行聚合,请使用子查询或cte。我想查询应该是这样的:

select . . .
from (select column1, column2, column3, column4, column5, . . .
      union all
      select column1, column2, column3, column4, column5, . . .
      union all
      select column1, column2, column3, column4, column5, . . .
     ) abc
group by GROUP BY a_column1, column2, column3, column4, column5

相关问题