SQL Server/T-SQL Tutorial/Data Convert Functions/COALESCE

Материал из SQL эксперт
Перейти к: навигация, поиск

A SELECT statement that substitutes a different data type

   <source lang="sql">

5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> 2> 3> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 4> GO (1 rows affected) 1> 2> 3> 4> create table Billings ( 5> BankerID INTEGER, 6> BillingNumber INTEGER, 7> BillingDate datetime, 8> BillingTotal INTEGER, 9> TermsID INTEGER, 10> BillingDueDate datetime , 11> PaymentTotal INTEGER, 12> CreditTotal INTEGER 13> 14> ); 15> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 4> GO (1 rows affected) 1> 2> 3> 4> SELECT BankerName, 5> COALESCE(CAST(BillingTotal AS varchar), "No Billings") AS BillingTotal 6> FROM Bankers LEFT JOIN Billings 7> ON Bankers.BankerID = Billings.BankerID 8> ORDER BY BankerName 9> GO BankerName BillingTotal


------------------------------

ABC Inc. 165 DEF Inc. 165 DFG Inc. 165 GHJ Inc. 165 HJI Inc. 165 OIP Inc. 165 QWE Inc. 165 RTY Inc. 165 SAD Inc. 165 YUI Inc. 165 (10 rows affected) 1> drop table Billings; 2> GO 1></source>


A SELECT statement that uses the COALESCE function

   <source lang="sql">

7> 8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> SELECT BillingDate, 3> COALESCE(BillingDate, "1900-01-01") AS NewDate 4> FROM Billings 5> GO BillingDate NewDate


-----------------------

2005-01-22 00:00:00.000 2005-01-22 00:00:00.000 2001-02-21 00:00:00.000 2001-02-21 00:00:00.000 2003-05-02 00:00:00.000 2003-05-02 00:00:00.000 1999-03-12 00:00:00.000 1999-03-12 00:00:00.000 2000-04-23 00:00:00.000 2000-04-23 00:00:00.000 2001-06-14 00:00:00.000 2001-06-14 00:00:00.000 2002-07-15 00:00:00.000 2002-07-15 00:00:00.000 2003-08-16 00:00:00.000 2003-08-16 00:00:00.000 2004-09-17 00:00:00.000 2004-09-17 00:00:00.000 2005-10-18 00:00:00.000 2005-10-18 00:00:00.000 (10 rows affected) 1> drop table Billings; 2> GO 1></source>


COALESCE is equivalent to a CASE expression that returns the first NOT NULL expression in a list of expressions.

   <source lang="sql">

COALESCE(expression1, expression2, ... expressionN)</source>


COALESCE() saves quite a lot of IF or CASE decision logic.

   <source lang="sql">

4> 5> CREATE TABLE #ProductPrices ( 6> ProductName VarChar(25), 7> SuperSalePrice Money NULL, 8> SalePrice Money NULL, 9> ListPrice Money NULL) 10> GO 1> 2> SELECT ProductName, COALESCE(SuperSalePrice, SalePrice, ListPrice) AS CurrentPrice 3> FROM #ProductPrices 4> GO ProductName CurrentPrice


---------------------

(0 rows affected) 1> 2> drop table #ProductPrices; 3> GO</source>


Returning the First Non NULL Value in a List of Expressions

   <source lang="sql">

5> DECLARE @Value1 int 6> DECLARE @Value2 int 7> DECLARE @Value3 int 8> SET @Value2 = 22 9> SET @Value3 = 955 10> SELECT COALESCE(@Value1, @Value2, @Value3) 11> GO


        22

(1 rows affected) 1></source>