代码之家 › 专栏 › 技术社区 › user3268139

Visual Studio数据库项目MATERIALIZED VIEW错误SQL71640:使用此工具创建物化视图时需要COUNT_BIG(a)

database-project materialized-views azure-synapse visual-studio azure

0

user3268139 · 技术社区 · 3 年前

我找不到解决这个问题的办法。

我有COUNT_BIG( )在脚本中,我得到了“SQL71640:COUNT_BIG(a)是必需的”错误。但当我将脚本更改为COUNT_BIG(1)时,错误将更改为COUT_BIG( )是必需的。。。

我是不是遗漏了什么?

SQL71640:COUNT_BIG(a)是使用此工具创建SELECT列表中具有SUM(a)的物化视图时所必需的。

示例脚本:

    CREATE MATERIALIZED VIEW [EDW_DM].[MVW_Fact_Transaction] WITH ( DISTRIBUTION = HASH (H_PlayerHashKey)) AS 
SELECT
   some columns 
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x] * [Column x] * 0.0001, 0) AS MONEY)) AS Column x,
   COUNT_BIG(*) AS cb 
FROM
   [EDW_DM].[Fact_Table] 
GROUP BY
    some columns

0 回复 | 直到 3 年前

1

0

Bhavani 3 年前

我试图用你的代码在我的环境中重现这个问题,但我得到了同样的错误:

enter image description here

我使用Visual Studio安装程序将Vs2019VS2019更新到最新版本。安装后,我等待了一段时间,并尝试运行代码,它成功地工作,没有任何错误。有关更多信息,请参阅 this .

2

0

Jeremy Caney Abloin 2 年前

我在VisualStudio2022数据库项目中的Materialized视图项目中也遇到了同样的问题。正如我从错误消息中了解到的,Visual Studio需要有一个单独的 count_big 对于每个摘要列。在我添加了额外的 count_big ,错误消失了。

Visual Studio中出现错误的SQL脚本:

CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb

From mytable
group by a, b

当我编译上面的代码时,我得到了以下错误:

SQL71640:COUNT_BIG(a)是使用此工具创建SELECT列表中具有SUM(a)的物化视图时所必需的。

我将脚本更改为以下代码以解决该错误消息:

CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb, count_big(c) as cb2, count_big(d) as cb3

From mytable
group by a, b