Teradata SQL CASE statement with Example

Teradata SQL CASE statement with Example
Teradata SQL CASE statement with Example

Teradata SQL CASE expression is used to check for various conditions sequentially and produce result depending on which condition is met first. If no condition is matched then TERADATA SQL CASE will result in output specified in ELSE clause. If ELSE clause is missing then, it will result in NULL as value. So, if you have to test multiple conditions sequentially and apply the first condition met ; then you should use CASE statement. Don’t forget to put END keyword to finish CASE statement else you will get syntax error. If you wish to give alias name to the column mention it after END keyword.
Use Below Queries to learn TERADATA SQL CASE expression yourself:

 

CREATE VOLATILE TABLE TST_CASE
(
EMPID INTEGER,
NAME VARCHAR(30),
YEARS_OF_EXP SMALLINT,
SALARY INTEGER
)
PRIMARY INDEX (EMPID)
ON COMMIT PRESERVE ROWS;

INSERT INTO TST_CASE VALUES(1,’WILLIAM’,5,50000);
INSERT INTO TST_CASE VALUES(2,’SARA’,3,30000);
INSERT INTO TST_CASE VALUES(3,’ROBIN’,4,40000);
INSERT INTO TST_CASE VALUES(4,’TED’,7,70000);
INSERT INTO TST_CASE VALUES(5,’LILY’,2,20000);

SELECT * FROM TST_CASE;

EMPID

NAME

YEARS_OF_EXP

SALARY

1

WILLIAM

5

50000

2

SARA

3

30000

3

ROBIN

4

40000

4

TED

7

70000

5

LILY

2

20000

SELECT EMPID,NAME,YEARS_OF_EXP, SALARY ,
CASE WHEN YEARS_OF_EXP< 5 THEN ‘ASSOCIATE’
WHEN YEARS_OF_EXP<6 THEN ‘LEAD’ ELSE ‘MANAGER’
END AS DESIGNATION
FROM TST_CASE ORDER BY EMPID;

EMPID

NAME

YEARS_OF_EXP

SALARY

DESIGNATION

1

WILLIAM

5

50000

LEAD

2

SARA

3

30000

ASSOCIATE

3

ROBIN

4

40000

ASSOCIATE

4

TED

7

70000

MANAGER

5

LILY

2

20000

ASSOCIATE

Nested case statement in SQL

Example: If you want to give salary hike to employees and use following criteria for it.
If Employee Experience in less than 5 years then give them 50% hike if salary is less than 21000 else give 40% hike if salary is less than 25000 else give 30% hike. Also if experience is more than equal to 5 years then give 20% hike only. Below SQL can help in achieving same.

SELECT EMPID,NAME,YEARS_OF_EXP, SALARY ,
CASE WHEN YEARS_OF_EXP< 5 THEN ‘ASSOCIATE’
WHEN YEARS_OF_EXP ELSE ‘MANAGER’
END AS DESIGNATION,
CASE WHEN YEARS_OF_EXP<5

CASE WHEN salary<21000 then salary*1.5 when salary < 25000 then salary*1.4

ELSE salary*1.3 END
ELSE salary*1.2 END AS new_salary
FROM TST_CASE ORDER BY EMPID;

Page with Comments

Leave a Reply