COALESCE function in Teradata

COALESCE function in Teradata return default value if there is NULL value in the first expression.

Syntax:-

COALESCE(EXPRESSION,'DEFAULT_VALUE);

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null

SELECT COALESCE(EMPNAME,'CHANCHAL') as EMPNAME;

EMPNAME
-------------------
CHANCHAL


You can have many default values. Teradata will check if first expression is null then it will show second expression if it is not null, if second
is also null then it will show third expression likewise.

SELECT EMPNO, COALESCE (EMPNAME,NULL,'CHANCHAL') FROM  EMPLOYEE;

-->  CHANCHAL

SELECT EMPNO, COALESCE (EMPNAME,NULL,NULL,'WANKHADE') FROM  EMPLOYEE;

-->  WANKHADE




Read Also:-  COALESCE function in Greenplum