How to add expression on result of aggregate function when create Materialized View in Synapse

Question

How to add expression on result of aggregate function when create Materialized View in Synapse

zmsoft 470

Hi there,

When I creating materialized view in synapse, i got an error, it says "because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list."

But I need to use the case when expression to generate one of the columns, Any suggestion?

My code is:


CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123]
WITH (  
        DISTRIBUTION = ROUND_ROBIN
        -- ,FOR_APPEND
    )
AS
with base as (
    SELECT 
    [A],
    [B],
    [C],
    [D],
    CASE  WHEN [A] LIKE '%cat%'
            OR [A] LIKE '%dog%'
            OR [A] LIKE '%pig%' THEN 'X'
        ELSE ''
    END AS AFlag
    FROM [ABC].[ABC123]
)
SELECT 
    [A],
    [B],
    [C],
    [D],
	AFlag,
    count_big(*) as cb
FROM base
 GROUP BY 
	[A],    
	[B],    
	[C],    
	[D],	
	AFlag;

Thanks

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-12T18:36:33.8066667+00:00
@zmsoft

Thanks for reaching out to Microsoft Q&A.

When creating a materialized view in Azure Synapse, you may encounter an error if you try to include expressions on the results of aggregate functions or grouping columns. In your case, the CASE statement in the SELECT clause is causing this issue.

To resolve this, you can modify your approach by moving the CASE statement into the GROUP BY clause instead of the SELECT clause. Here’s how you can adjust your SQL code:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS WITH base AS ( SELECT [A], [B], [C], [D], CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM [ABC].[ABC123] ) SELECT [A], [B], [C], [D], COUNT_BIG(*) AS cb, CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM base GROUP BY [A], [B], [C], [D];

Key Changes:

Removed AFlag from the GROUP BY clause: Instead, you can calculate it directly in the SELECT statement after the aggregation.

Re-added the CASE statement: This allows you to generate the AFlag column based on the original data without violating the materialized view constraints.

This should help you create the materialized view without errors

Hope this helps. Do let us know if you any further queries.
zmsoft 470 Reputation points

2024-11-13T01:49:52.2066667+00:00
Hi @phemanth ,

Modify to the following code, still get an error, it says "because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list."

CREATE MATERIALIZED VIEW [ WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb, CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM GROUP BY A, B, C, D;
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-14T18:53:59.9233333+00:00
@zmsoft To resolve this, you can use a Common Table Expression (CTE) or a subquery to first calculate the COUNT_BIG(*) and then apply the CASE statement in an outer query. Here’s how you can modify your code:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS WITH AggregatedData AS ( SELECT A, B, C, D, COUNT_BIG(*) AS cb FROM [YourTable] -- Replace with your actual table name GROUP BY A, B, C, D ) SELECT A, B, C, D, cb, CASE WHEN A LIKE '%cat%' OR A LIKE '%dog%' OR A LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM AggregatedData;

Key Changes:

Use a CTE: The AggregatedData CTE calculates the counts first.

Outer Query: The outer query applies the CASE statement based on the original data without violating the materialized view constraints.

Make sure to replace [YourTable] with the actual name of your source table. This approach should help you create the materialized view without errors
zmsoft 470 Reputation points

2024-11-15T06:36:21.5266667+00:00

Hi @phemanth ,

Thanks for your help, but after modification, still get the following error

Info: MemoTimeoutEnabled feature switch is not enabled, compile_timeoutsec value of 0 will be ignored Cannot create materialized view because it references a common table expression.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-20T04:47:21.56+00:00
@zmsoft

No CTEs: Ensure that your SQL does not include any CTEs or subqueries that are not allowed in materialized views.

Direct Queries: Materialized views should be created using straightforward SELECT statements without any complex logic that may involve expressions on aggregate functions or grouping columns.

Correct SQL Code Example:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb, CASE WHEN A LIKE '%cat%' OR A LIKE '%dog%' OR A LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM [ABC].[ABC123] -- Replace with your actual table name GROUP BY A, B, C, D;

to Troubleshoot:

Check for CTEs: Ensure that you are not accidentally including any CTEs in your SQL. The provided code does not use any CTEs, so it should not trigger that error.

Test the Query: Run the SELECT statement independently without creating the materialized view to verify that it executes correctly. This helps isolate whether the issue is with the query itself or with the materialized view creation process.

Simplify Further: If the error persists, try to simplify the query even further. For example, remove the CASE statement temporarily to see if the materialized view can be created without it:
CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb FROM [ABC].[ABC123] -- Replace with your actual table name GROUP BY A, B, C, D;

Check for Other Constraints: Ensure there are no other restrictions or limitations in your Azure Synapse environment that might affect the creation of materialized views.

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-12T18:36:33.8066667+00:00

@zmsoft

Thanks for reaching out to Microsoft Q&A.

When creating a materialized view in Azure Synapse, you may encounter an error if you try to include expressions on the results of aggregate functions or grouping columns. In your case, the CASE statement in the SELECT clause is causing this issue.

To resolve this, you can modify your approach by moving the CASE statement into the GROUP BY clause instead of the SELECT clause. Here’s how you can adjust your SQL code:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS WITH base AS ( SELECT [A], [B], [C], [D], CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM [ABC].[ABC123] ) SELECT [A], [B], [C], [D], COUNT_BIG(*) AS cb, CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM base GROUP BY [A], [B], [C], [D];

Key Changes:

Removed AFlag from the GROUP BY clause: Instead, you can calculate it directly in the SELECT statement after the aggregation.

Re-added the CASE statement: This allows you to generate the AFlag column based on the original data without violating the materialized view constraints.

This should help you create the materialized view without errors

Hope this helps. Do let us know if you any further queries.
zmsoft 470 Reputation points

2024-11-13T01:49:52.2066667+00:00

Hi @phemanth ,

Modify to the following code, still get an error, it says "because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list."

CREATE MATERIALIZED VIEW [ WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb, CASE WHEN [A] LIKE '%cat%' OR [A] LIKE '%dog%' OR [A] LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM GROUP BY A, B, C, D;
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-14T18:53:59.9233333+00:00

@zmsoft To resolve this, you can use a Common Table Expression (CTE) or a subquery to first calculate the COUNT_BIG(*) and then apply the CASE statement in an outer query. Here’s how you can modify your code:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS WITH AggregatedData AS ( SELECT A, B, C, D, COUNT_BIG(*) AS cb FROM [YourTable] -- Replace with your actual table name GROUP BY A, B, C, D ) SELECT A, B, C, D, cb, CASE WHEN A LIKE '%cat%' OR A LIKE '%dog%' OR A LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM AggregatedData;

Key Changes:

Use a CTE: The AggregatedData CTE calculates the counts first.

Outer Query: The outer query applies the CASE statement based on the original data without violating the materialized view constraints.

Make sure to replace [YourTable] with the actual name of your source table. This approach should help you create the materialized view without errors
zmsoft 470 Reputation points

2024-11-15T06:36:21.5266667+00:00

Hi @phemanth ,

Thanks for your help, but after modification, still get the following error

Info: MemoTimeoutEnabled feature switch is not enabled, compile_timeoutsec value of 0 will be ignored Cannot create materialized view because it references a common table expression.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-11-20T04:47:21.56+00:00

@zmsoft

No CTEs: Ensure that your SQL does not include any CTEs or subqueries that are not allowed in materialized views.

Direct Queries: Materialized views should be created using straightforward SELECT statements without any complex logic that may involve expressions on aggregate functions or grouping columns.

Correct SQL Code Example:

CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb, CASE WHEN A LIKE '%cat%' OR A LIKE '%dog%' OR A LIKE '%pig%' THEN 'X' ELSE '' END AS AFlag FROM [ABC].[ABC123] -- Replace with your actual table name GROUP BY A, B, C, D;

to Troubleshoot:

Check for CTEs: Ensure that you are not accidentally including any CTEs in your SQL. The provided code does not use any CTEs, so it should not trigger that error.

Test the Query: Run the SELECT statement independently without creating the materialized view to verify that it executes correctly. This helps isolate whether the issue is with the query itself or with the materialized view creation process.

Simplify Further: If the error persists, try to simplify the query even further. For example, remove the CASE statement temporarily to see if the materialized view can be created without it:
CREATE MATERIALIZED VIEW [MaterializedView].[MaterializedView123] WITH ( DISTRIBUTION = ROUND_ROBIN ) AS SELECT A, B, C, D, COUNT_BIG(*) AS cb FROM [ABC].[ABC123] -- Replace with your actual table name GROUP BY A, B, C, D;

Check for Other Constraints: Ensure there are no other restrictions or limitations in your Azure Synapse environment that might affect the creation of materialized views.

Share via

How to add expression on result of aggregate function when create Materialized View in Synapse

Your answer