SQL Merge and Multiple Cases

The Merge feature is new as of SQL 2008, and it’s great for doing exactly that. However, you can only have one when  matched clause, and only one when not matched .


MERGE Mammals AS TARGET
USING(
	SELECT C.LatinName, C.CommonName, C.Lifespan
	FROM Camels C
) AS SOURCE (LatinName, CommonName, Lifespan)
ON TARGET.LatinName=SOURCE.LatinName
WHEN MATCHED THEN
	-- update conditionally. Cases are : normal update, update when newly inactive
	UPDATE SET
	CommonName=
		CASE WHEN SOURCE.LatinName='ABC' THEN 'Brown Camel'
		     WHEN SOURCE.IsActive='DEF' THEN 'Red Camel'
		END
	, Lifespan=SOURCE.Lifespan
WHEN NOT MATCHED THEN
	-- does not exist, new record, insert it
	INSERT (LatinName, CommonName, Lifespan)
	VALUES (SOURCE.LatinName, SOURCE.CommonName, SOURCE.Lifespan );

MS Access Query Truncation Problem

There’s been a small change to the way MS Access works from the ’97 to the 2010 version that can cause some serious issues.

Consider this query “Select * From Table1″ . What happens? In both versions, it will run just fine. However, when importing this command through any database connection, say OLEDB, it won’t work. Instead, what’s fed into 2010 is “able1″.

What’s happening? For database connections, table names are required to be square-bracketed. A simple fix.

Just use “Select * From [Table1]” instead.

SQL Sproc Gotcha

It’s easy to mistake the SQL Sproc syntax

ALTER PROCEDURE [dbo].[fooBar]
(
)
AS
-- etc
RETURN  @Success

With this. Note the BEGIN and END statements. It generally works for shorter queries, but for longer ones, you begin to get baffling SQL syntax queries, where sometimes it will execute the ALTER PROCEDURE, but things like changing the whitespace, reverting it, and executing it again will fail.

ALTER PROCEDURE [dbo].[fooBar]
(
)
AS
BEGIN
-- etc
RETURN  @Success
END

Contracting: Tales of Being Short A Project Manager

Dipping your toes in

As a young developer growing up, it’s easy to take all kinds of things for granted. For example, a testing and production environment that aren’t close, but instead, exactly the same. The ability to ask for a copy of Visual Studio without balking at it’s price in sticker-shock. Or a dedicated tester.

It’s easy to get caught up browsing through contracting websites and fancy being a solo contractor on your spare time. You forget to consider how tired you actually are when you drag yourself home from your real job, how much time it really takes to do the dishes, how much of a procrastinator you are, and even possibly how you’ll drop near everything to be immersed in the latest Elder Scrolls game. Instead, you wrap your head around how much money you think you’ll make in your spare time doing what you already love.

You enthusiastically tell your friends who give a shrug, explain to your Uncle Fred how it isn’t a get rich scheme,  try to unconvince your un-computer-savvy Aunt Bethelida to learn programming just so they can try it too, and ward off concerns from family members who believe that because your contracting involves a computer, it is a demonic form of giraffe-sacrifice and should be shut down immediately.  Perhaps you manage to get excellent advice from all your buddies who dipped their toes in, and possibly you even find some templates for things like contracts, invoices, and various other communications. You feel disappointed at the back-and-forth between you, your friends’ friends’ lawyer, your client, their boss, their HR department, and their accounting department that takes months before the final, final contract is sent, looking much different than what you had expected. And if you’re like me, you succumbed to your human tenancy to skim and well… just shot yourself in the foot. Multiple times.

Baited and hooked

All sorts of questions arise like, how does the signature dates affect the dates already written on the contract’s staticly-dated timeline? Does that mean that you really only have a few days to deliver item X or that it is even, that it is already overdue? How exactly will payments come? And when will they come, since it’s been months since task X and your client’s manager’s manager is preoccupied. Suddenly, you realize that depending on the terms of your contract and employment, you may are actually making 30% less of your revenue due to taxes – and the fines for not paying them, because ignorance is no excuse. And what about health care, benefits, sick and vacation days, hm…

But, if you’re like me, that’s only the tip of the iceberg of why you miss your project manager to death.

No matter how good you think you are with client relations, you always seem to wonder if you worded things correctly for such an important legal matter. Is it too bossy? Is it even correct? Does it abide by the contract? There’s no second pair of eyes to do it, and you’ll be the one answering all the questions, inquiries, papers, forms, nonconstructive criticism, and time sheets.

You now manage your own schedule, which in some ways is nice, but in others, you miss that gentle reminder to get back to work when you find yourself engrossed in the latest TV series, and then even procrastinating with re-runs.

You begin your work and perhaps you find the lack of office noise enthralling, but at the same time, you miss the occasional passerby’s surely prying eyes which kept you working, out of fear they’d discover your love for FarmVille. If you’re like me, you find that programming on your couch, on your porch, and even in a coffee shop is enlightening. But when you make a mistake, you have no one to blame but yourself. When you short sight something, it’s your own fault. When you misunderstand, over budget, or encounter scope creep, everything falls on your lap. Where a project manager might help you keep a clear perspective on things, manage that creeping scope caterpillar, and lessen the blow of your own shortcomings, you have no one. Of course, it never was the project manager’s fault for any of these things, and you probably knew that already, but they kept it all under wraps and off your shoulders.

But what you really, really miss is what your project manager did best – sitting down with the clients and figuring out what they are asking, what they really mean, what they really need, what fits under the budget, the easiest way to do it, and what they will ask for later. As a self-contractor, you get stuck commuting or telecommuting to your client’s sites, map in hand, and wondering how to put your gas on the bill. You feel helpless as you wait for everyone to be available for the meeting even though it was extensively planned, while wading through all the wary, suspicious glances of you as an outside. You got lost in the building. You forgot a paper. You just now realize your only pen doesn’t work. You parked where you’re weren’t supposed to,. And you come home eight hours later in rush-hour freeway traffic feeling overwhelmed, and with a notebook full of your own notes that you can’t decipher.

In your communications, you feel helplessly lost as terms like ‘database’, ‘software’, ‘on-line’, ‘desktop’, ‘excel’, and ‘report’ are interchangeably used. But you can’t blame your clients, you created your software to be so seamless and easy that they didn’t have to worry about those terms. When you ask for an explanation of their request, they can only repeat the same vague request, that in your mind means “change everything“. You ask for an example and receive a squiggle comparable to Jack’s attempt to request ‘Chipotle’ If you’re lucky, you stumble onto the approach of providing the entire product, asking them to point to a specific thing, and tell you “make this X a Y”.

Contracting: When it’s worth it

If you’re like me, you find yourself looking at the video games, new computers, plasma television sets, and imagining being able to afford it all along with a pair of working prescription eye glasses. But at the end of the day, you pay your bills, and settle in for a Redbox movie and McDonalds Dollar Menu item, paid mostly in pennies. Your quality of life is really no different than it was before.

Contracting is a lot of work and requires a lot of know-how, and not just the technical kind. But between all the stress headaches and deadline midnight latte’s, if you enjoy what you do and who you do it for, it’s worth it. So, if you’re considering contracting as a beginner, don’t tease yourself with fictitious dollar signs and dreams of a sixth computer. Be realistic, expect your money ten tames later than you imagined, and consider setting some extra cash aside for that inevitable life emergency that will occur until then – say, an accident, a sprained ankle, or an embarrassing trip to the vet because your dog likes their squeaky ball a little too much.

A special thank you to J.H,, MBA and D.W.