Cursor with IF statement

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

This pattern defines a function that conditionally uses a cursor to fetch and return a value based on an IF statement.

Components:

1. Function Declaration:

   • CREATE FUNCTION functionName(parameters) RETURN returnType

   • Declares the function with input parameters and the return type.

2. Cursor Declaration:

   • CURSOR cursorName IS SELECT singleColumn FROM ... WHERE ... [AND col1 = localVar1];

   • Defines a cursor to select a single column from a table with optional filtering conditions.

3. Variable Declaration:

   • Declares variables, including the return variable.

4. BEGIN-END Block with IF Statement:

   • Variables assignment.

   • Check if a condition is true.

   • If true, opens the cursor, fetches the result into the return variable, closes the cursor, and returns the fetched value. (The cursor can also be opened in the ELSE block and must meet the same conditions)

   • The ELSE Block is optional, if it exists, it should only contain a single statement that can be an assignment or a RETURN statement.

The variables are transformed into a common table expression (CTE). As well as the query within the cursor to which, in addition, the FETCH FIRST 1 ROW ONLY clause is added to simulate the FETCH CURSOR behavior.

IF/ELSE statement can be handled using the CASE EXPRESSION inside the select allowing conditionals inside the queries. RETURN statement is transformed to the final select..

Queries

Oracle

IN -> Oracle_01.sql

CREATE OR REPLACE FUNCTION func1 (
   company_          IN NUMBER) RETURN NUMBER
IS
   CURSOR getmaxperiod IS
      SELECT max(col2)
      FROM   table1;
   max_period_               NUMBER := 12;
BEGIN
   IF 1 = 1 THEN
      OPEN   getmaxperiod;
      FETCH  getmaxperiod INTO max_period_ ;
      CLOSE  getmaxperiod;
      RETURN max_period_;
   ELSE
      RETURN NULL;
   END IF;
END func1;

Snowflake

Result

Oracle

IN -> Oracle_02.sql

CREATE OR REPLACE FUNCTION func2(
   company_          IN NUMBER) RETURN NUMBER
IS
   CURSOR getmaxperiod IS
      SELECT max(col2)
      FROM   table1;
   max_period_               NUMBER := 1;
BEGIN
   max_period_:= 2;
   IF company_ = 1 THEN
      RETURN max_period_ * 2;
   ELSE
      OPEN   getmaxperiod;
      FETCH  getmaxperiod INTO max_period_ ;
      CLOSE  getmaxperiod;
      RETURN max_period_;
   END IF;
END func2;

Snowflake

Result

Oracle

IN -> Oracle_03.sql

CREATE OR REPLACE FUNCTION func3 (
   company_          IN NUMBER) RETURN NUMBER
IS
   CURSOR getmaxperiod IS
      SELECT max(col2)
      FROM   table1;
   max_period_               NUMBER := 0;
BEGIN
   IF company_ = 1 THEN
      OPEN   getmaxperiod;
      FETCH  getmaxperiod INTO max_period_ ;
      CLOSE  getmaxperiod;
   END IF;
   RETURN max_period_;
END func10;

Snowflake

Result

Known Issues

No issues were found.

Related EWIS

No EWIs related.