Snowflake Scripting Tutorial: CASE Statements and Types of Case Statement Explained

In this tutorial, we'll dive into using CASE statements in Snowflake scripting. Learn about the different types of CASE statements and how to apply them to enhance your SQL scripts for more dynamic and flexible data processing.

-Code-----------------------------
CREATE OR REPLACE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);

INSERT INTO employees (employee_id, first_name, salary, department_id) VALUES
(1, 'John', 50000, 101),
(2, 'Jane', 60000, 102),
(3, 'Alice', 55000, 101),
(4, 'Bob', 70000, 103),
(5, 'Charlie', 65000, 102),
(6, 'Diana', 80000, 103),
(7, 'Eve', 72000, 104),
(8, 'Frank', 68000, 104),
(9, 'Grace', 75000, 105),
(10, 'Hank', 52000, 105);

SELECT * FROM employees;

-- Simple CASE statements
select employee_id,first_name,salary,department_id,
case department_id
when 101 then salary*1.1
when 102 then salary*1.2
when 103 then salary*1.3
else salary
END new_sal
from
employees;


-- Searched CASE statements
------------------------------------
select employee_id,first_name,salary,department_id,
case
when department_id=101 then salary*1.1
when department_id=102 then salary*1.2
when department_id=103 then salary*1.3
else salary
END new_sal
from
employees;
--------------------------------------
-- Simple CASE statements

DECLARE
expression_to_evaluate VARCHAR DEFAULT 'default value';
BEGIN
expression_to_evaluate := 'value c';
CASE expression_to_evaluate
WHEN 'value a' THEN RETURN 'x';
WHEN 'value b' THEN RETURN 'y';
WHEN 'value c' THEN RETURN 'z';
WHEN 'default value' THEN RETURN 'default';
ELSE RETURN 'other';
END;
END;

---------------------------------
DECLARE
v_sal number;
v_desc varchar(100);
BEGIN
select salary into v_sal from employees where employee_id=1;

v_desc:=case
when v_sal is null then 'no salay for the employee'
when v_sal between 10000 and 30000 then 'salay is low'
when v_sal between 30001 and 50000 then 'salay is medium'
when v_sal between 50001 and 100000 then 'salay is good'
else 'salay is High'
end ; --here end not end case

return v_desc;

END;



------------------------

-- Searched CASE statements

DECLARE
a VARCHAR DEFAULT 'x';
b VARCHAR DEFAULT 'y';
c VARCHAR DEFAULT 'z';
BEGIN
CASE
WHEN a = 'x' THEN
RETURN 'a is x';
WHEN b = 'y' THEN
RETURN 'b is y';
WHEN c = 'z' THEN
RETURN 'c is z';
ELSE
RETURN 'a is not x, b is not y, and c is not z';
END case;
END;

--------------------------------------------------------


DECLARE
v_sal number;
v_desc varchar(100);
BEGIN
select salary into v_sal
from employees
where employee_id=1;

case
when v_sal is null then
return ('no salay for the employee');
when v_sal between 10000 and 50000 then
return('salay is low');
when v_sal between 50001 and 100000 then
return('salay is medium');
when v_sal between 100001 and 1000000 then
return('salay is good');
else
return('salay is High');
end case; --here end case

END;



DECLARE
v_sal number;
v_desc varchar(100);
BEGIN
select salary into v_sal
from employees
where employee_id=3;

case
when v_sal = 50000 then
delete from employees where employee_id =1;
return ('Employee ID 1 Deleted');

when v_sal =60000 then
delete from employees where employee_id =2;
return ('Employee ID 2 Deleted');
else
return('No Statement');
end case; --here end case

END;






-------------------------------
#Snowflake #SQL #Scripting #CASEStatements #DataProcessing #SnowflakeTutorial #SQLScripting #DataEngineering #CloudData #DataManagement #TechTutorials #dataworldsolution

------------------------------------