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;