Teradata SQL COALESCE statement with Example

teradata

Teradata SQL COALESCE expression is used for NULL Handling. It returns the first not null value which is passed to it. If all the values passed to COALESCE function are NULL then output would be NULL else output would be first NOT NULL value encountered.
Use Below Queries to test TERADATA SQL COALESCE 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',NULL,40000);
INSERT INTO TST_CASE VALUES(4,'TED',7,70000);
INSERT INTO TST_CASE VALUES(5,'LILY',NULL,20000);

Preview the data in the table.

SELECT * FROM TST_CASE;

EMPID

NAME

YEARS_OF_EXP

SALARY

1

WILLIAM

5

50000

2

SARA

3

30000

3

ROBIN

NULL

40000

4

TED

7

70000

5

LILY

NULL

20000

Let’s apply coalesce to the column YEARS_OF_EXP and if it is NULL then return “-1” else keep the value as-is in the output.

SELECT
	EMPID,
	NAME,
	COALESCE(YEARS_OF_EXP,-1) AS YEARS_OF_EXP,
	SALARY
FROM
	TST_CASE
ORDER BY
	EMPID;

EMPID

NAME

YEARS_OF_EXP

SALARY

1

WILLIAM

5

50000

2

SARA

3

30000

3

ROBIN

-1

40000

4

TED

7

70000

5

LILY

-1

20000

COALESCE can be replaced by CASE statement in which you check the value is NULL then default value else keep the column as the output.

Leave a Reply

Your email address will not be published. Required fields are marked *