COALESCE

SQL -> Funkcie -> Funkcie pre prácu s reťazcami -> COALESCE

Syntax

COALESCE ( expression [ ,...n ] )

Popis

Príkaz jazyka SQL
Vráti prvý argument, ktorý nemá hodnotu null.
Ak sú všetky parametre null, funkcia vráti null.
Parameter expression - Is an expression of any type.

K funkcii
COALESCE(expression1,...n) je ekvivalentná táto CASE funkcia:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL
END

Príklad

SET NOCOUNT ON;
GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
WHERE name = 'wages')
   DROP TABLE wages;
GO
CREATE TABLE wages
(
   emp_id    tinyint   identity,
   hourly_wage decimal   NULL,
   salary    decimal   NULL,
   commission   decimal   NULL,
   num_sales tinyint   NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
   salary,
   commission * num_sales) AS money) AS 'Total Salary'
FROM wages;
GO

výsledok:

Total Salary
------------
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000

(12 row(s) affected)

Pozri aj

case, ISNULL