VBUC
Visual Basic Upgrade Companion. VB6 Converter for .NET & Web.

VB6
ASP
.NET7 supportDownload Now

WebMAP
Cloud Application Migration Tools Transform Desktop Applications

C#
Silverlight
Access
VB.NET
PowerBuilder
Winforms
ASP.NET Web Forms

SnowConvert
The best available tools to perform code migrations from a source database or Spark application to Snowflake.

Oracle
Spark Scala
Spark Python
SQL Server
Teradata

Get Started
There are countless ways to take advantage of BlackDiamond Studio and SnowConvert.

Migration and Other Snowflake Services
Get Up and Running with Snowpark
Using the SnowConvert Trial
Build a Cross-Platform Object Inventory

Try BlackDiamond Studio

Time to Mobilize
Free Assessment Tool

Mirroring the RESET WHEN Functionality to Snowflake

by Juan Pablo Martínez, on Jan 12, 2023 6:00:00 AM

The power of the SnowConvert integration in BlackDiamond Studio is rarely on better display that when it replicates the functionality of the RESET WHEN clause in Snowflake.

First, how does the RESET WHEN clause work in Teradata SQL? Depending on the evaluation of the specified condition, RESET WHEN determines the group or partition over which an ordered analytical function operates. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition. 

See the Teradata documentation for more information about RESET WHEN.

How to Mirror RESET WHEN to Snowflake Example

Suppose we have the following data set and we want to analyze the sequence of consecutive annual increases in profits:

subsidiary_id

year_id

profit

1

1

7000

1

2

10550

1

3

11800

1

4

10860

1

5

11800

1

6

9700

 

 

 

 

 

 

 


When a year's profit is less than or equal to the previous year's profit, the requirement is to reset the counter to zero and restart. To analyze this data, Teradata SQL uses a window function with a nested aggregate and a RESET WHEN clause as shown below:

 SELECT
  subsidiary_id,
  year_id,
  profit,
  (
    ROW_NUMBER() OVER (
      PARTITION BY subsidiary_id
      ORDER BY year_id
      RESET WHEN profit <= SUM(profit) OVER (
          PARTITION BY subsidiary_id
          ORDER BY year_id
          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
      )
    ) -1
  ) AS profit_increase
FROM subsidiary_profit
ORDER BY 1, 2;

That gives us the following set of results:

subsidiary_id

year_id

profit

profit_increase

1

1

7000

0

1

2

10550

1

1

3

11800

2

1

4

10860

0

1

5

11800

1

1

6

9700

0

 

 

 

 

 

 

 


This is where the SnowConvert for Teradata tool comes in to do its job. Since Snowflake does not support the
RESET WHEN clause in window functions. To reproduce the same result, the Teradata SQL code has to be translated using native SQL syntax and nested subqueries, as shown below:

SELECT
  subsidiary_id,
  year_id,
  profit,
  (
    ROW_NUMBER() OVER (
      PARTITION BY subsidiary_id, new_dynamic_part
      ORDER BY year_id
    ) - 1
  ) AS profit_increase
FROM
  (
    SELECT
      subsidiary_id,
      year_id,
      profit,
      previous_value,
      SUM(dynamic_part) OVER (
        PARTITION BY subsidiary_id
        ORDER BY year_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS new_dynamic_part
    FROM
      (
        SELECT
          subsidiary_id,
          year_id,
          profit,
          SUM(profit) OVER (
            PARTITION BY subsidiary_id
            ORDER BY year_id
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          ) AS previous_value,
          (
            CASE WHEN profit <= previous_value THEN 1 ELSE 0 END
          ) AS dynamic_part
        FROM subsidiary_profit
      ) A
  ) B
ORDER BY 1, 2;

Two nested subqueries are required to support the RESET WHEN functionality in Snowflake. 

In the internal subquery labeled A, a dynamic partition indicator labeled dynamic_part is generated and filled in. This indicator (dynamic_part) is set to 1 if a year's profit is less than or equal to the previous year's profit. Otherwise, it is set to 0. In the outer subquery labeled  B, a new_dynamic_part indicator is generated as a result of the SUM window function in the source. Lastly, the new_dynamic_part is automatically added as a new partition attribute to the already existing partition attribute (account_id) and the same window function ROW_NUMBER()  is applied just like Teradata.

After these changes, the Snowflake query will generate precisely the same output as Teradata. 

You can try this out today by getting started with SnowConvert and BlackDiamond Studio for free. 

Comments

Subscribe to Mobilize.Net Blog

More...

More...
FREE CODE ASSESSMENT TOOL