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