One of the highlights of my Toronto trip was walking downtown and dining with the collective brain trust of Richard Hale Shaw, Walt Ritscher, Chris Kinsman, Rocky Lhotka, Brian Randell, Kevin McNeish, Derek Hatchard, David Totzke, and Rebecca Diaz. Here is a shot of Dave, RHS, Walt, Kevin, and Rocky. Kevin is looking up at the CN Tower.
I also want to thank those who came to my sessions. I hope you enjoyed the presentations and found them informative.
I came to the conclusion this week that I have at least one C++ habit I can’t kick. When I want to check an object reference for null, my fingers automatically type:
Widget woo = GetWidgetFromSomewhere();
if(woo)
{
// …
}
Error: Cannot implicitly convert Widget to bool.
After five years of working together, the compiler still doesn’t understand me.
Here is a subtle bug you can introduce into a query. Let’s pretend we are tracking coffee sales.
CREATE TABLE CoffeeSales
(
TransactionID int IDENTITY PRIMARY KEY,
Customer varchar(10) NOT NULL,
CoffeeType varchar(10) NOT NULL,
TransactionDate datetime NOT NULL,
Cost money NOT NULL
)
INSERT INTO CoffeeSales
VALUES('Joe', 'Decaf', '2004-04-17 07:30:00', 2.25)
INSERT INTO CoffeeSales
VALUES('Joe', 'Decaf', '2004-04-17 09:00:00', 3.25)
INSERT INTO CoffeeSales
VALUES('Joe', 'Mocha', '2004-04-17 17:00:00', 5.00)
INSERT INTO CoffeeSales
VALUES('Amy', 'Maple', '2004-04-17 15:00:00', 1.25)
INSERT INTO CoffeeSales
VALUES('Amy', 'Mocha', '2004-04-17 16:00:00', 4.75)
INSERT INTO CoffeeSales
VALUES('Amy', 'Mocha', '2004-04-17 17:00:00', 3.50)
Here is a query attempting to total up the sales for each customer by coffee type and date.
SELECT
Customer,
CoffeeType,
CONVERT(varchar(10), TransactionDate, 101) AS TransactionDate,
SUM(Cost) AS TotalCost,
COUNT(*) AS TotalTransactions
FROM
CoffeeSales
GROUP BY
Customer, CoffeeType, TransactionDate
ORDER BY Customer
Customer CoffeeType TransactionDate TotalCost TotalTransactions
---------- ---------- --------------- --------------------- -----------------
Amy Maple 04/17/2004 1.2500 1
Amy Mocha 04/17/2004 4.7500 1
Amy Mocha 04/17/2004 3.5000 1
Joe Decaf 04/17/2004 2.2500 1
Joe Decaf 04/17/2004 3.2500 1
Joe Mocha 04/17/2004 5.0000 1
We have 6 records, and when eyeballing the resultset we don’t see why Amy’s mocha purchases and Joe’s decaf purchases are not grouping up, after all, the name, coffee type, and transaction date are identical.
To realize what went wrong we have to think about how the database engine executes the query. Records are grouped before the CONVERT expression in the SELECT list executes. We think we are grouping by the converted TransactionDate field used as a column alias, but what SQL is doing is grouping on the raw TransactionDate field from the table, then executing the CONVERT and showing the field as just a date.
A working version follows.
SELECT
Customer,
CoffeeType,
CONVERT(varchar(10), TransactionDate, 101) AS TransactionDate,
SUM(Cost) AS TotalCost,
COUNT(*) AS TotalTransactions
FROM
CoffeeSales
GROUP BY
Customer, CoffeeType, CONVERT(varchar(10), TransactionDate, 101)
ORDER BY Customer
This week I am traveling to Toronto to speak at VSLive! I have presentations on Thursday and Friday afternoon. Toronto is a spectacular city to visit, and the conference location looks to be in a beautiful spot.
The following week I’ll be speaking at the BaltoMSDN user group meeting in Hunt Valley, MD, just outside of Baltimore. The meeting is on Wednesday, April 20th at 6:30.
In between the two, I’ll be spending some time here, and some time here.
I think I am addicted to satellite imagery.
Is there a support group?
Completely unpredictable ...
.... since 2004.
I’m still trying to rationalize the existence of example code. You know, the poison pills programmers copy and paste into an otherwise healthy body of software.
Sometime today, I was thinking back my first physics class, and of three key idealizations. These idealizations have helped students learn physics since the debut of Principia Mathematica in the 1680s.
The idealizations are:
The immoveable object.
The irresistible force.
The frictionless surface.
Of course, nobody actually builds bridges or launches rockets using irresistible forces and frictionless surfaces, but the concepts do make the first chapters in a physics textbook easier, and we learn from them. If there are parallels to these idealizations in the sample code on MSDN, in books, and on this blog, they would be:
The never-null parameter.
The always-on network.
The perfect string of user input.
We learn from them.
OdeToCode by K. Scott Allen
Subscribe
Twitter
Search
About
Learn C#