Mega Code Archive
A SELECT statement that substitutes a different data type
<![CDATA[
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>
]]>