Common Table Expressions

Someone I work with turned me on to SQL Server 2005 Common Table Expressions (CTEs) a few months ago and I'm really starting to get into them.

CTEs are ways to create a derived set of data; sort of like a view but it doesn't persist in the database after being used. Here's a simple example:

WITH ProductSales (ProductID, ProductName, TotalSales)
(
SELECT product.ProductID,product.ProductName,
TotalSales = (SELECT SUM(Quantity)
FROM salesdetail
WHERE salesdetail.ProductID = product.ProductID)

FROM product
)

You then use SELECT * FROM ProductSales to get the results. Actually, you have to immediately query the results before doing any other non-CTE SQL.

I wonder if this is something we should have thought of for Visual FoxPro 9. Probably would have if Gene Goldhammer hadn't retired since he tended to think about new data features.

Bits of Madness

I ran into an odd situation this week while coding a webform with Yes and No radiobuttons using the RadioButtonList control.

Traditionally, Yes and No are presented in a control with Yes being first. Therefore, in a .Net RadioButtonList control, the Index value for Yes is 0 (zero), while the Index value for No is 1. Being the old traditionalist that I am, I assigned 0 to the Value property for No and 1 to the Value for Yes.

This is an unbound control but is sourced to a Bit column in a SQL Server table. A Bit column is referenced in T-SQL as 0 for No and 1 for Yes, for example:

UPDATE mytable SET mybitfield = 0

This would set MyBitField to No.

When I grab this column into a Dataset in Visual Studio and want to convert it into an integer, I use the CInt function. CInt, though, converts a Yes to -1 and No to 0.

So now we have the following for my simple radiobuttons:

Yes No
Control.Index 0 1
Control.Value 1 0
MyBitField 1 0
CInt("mybitfield") -1 0

And this makes my head hurt

The simple solution is to assign Control.SelectedValue to Abs(CInt("mybitfield")) which will convert the -1 to 1 and leave the 0 values as is. But others might scratch their head while reviewing the code.

Another way to simplify this would be if I changed the control so that No came first, changing the control from Yes / No to No / Yes. That, however, goes against the way people think. I don't recall hearing anyone talk about a "No / Yes situation". I guess I could also reverse the assigned Values for the control so that No is 1 and Yes is 0 but that doesn't feel right either.

Sigh. Leave it to Microsoft to complicate something as simple as Boolean logic.

Been A While

I've been coding my ass off. Plus, my wife accepted a new position about 5 weeks ago and she's now working 60 hours a week making my "free" time an exercise into being a more complete Mr. Mom along with my 40-50 hour a week work.

Some of you all are going to hate me but I've backed off of the VFP to .Net bootcamp idea because, in all honesty, the more I delve into this the less I see for an open-minded, dedicated, and professional developer to overcome. I'm sorry but it's just not that freaking hard what with the extensive online support forums and a learned approach.

I still hope to make the next MVP Global Summit as a guest of the lovely and brilliant Ms. Pountney...that's looking good! I can't wait to see old friends and co-workers. When I left MS 3 years ago I was suffering a lot of personal issues and I didn't believe I was being treated fairly by HR but I rarely held it against my team members (with one exception who is no longer with the company). For me it'll be emotional....it will be great to see them all.