Complex SQL Updates with the Case Statement

One of the most important rules-of-thumb for database performance and data quality is to do as much as you can in as few transactions as possible.  When making Updates to data sometimes you need to do different things based on different conditions.  In order to make these complex Updates and to do them in as few transaction as possible, you'll need some way to make a decision.  One way to make a decision in SQL is by using the Case statement.

Given the following table:

id	country	state	zip
2	USA	IL	60191
3	UK	AA	0
5	USA	CA	90210
7	UK	?	99999

Say the state and zip columns are now made nullable so that garbage data will stop being put into it (e.g., UK having state AA and ?, and zip of 0 and 99999).  You'll want to correct the data you have so that you no longer have garbage data in the table.  To do so what you'll want to do is null the state and zip when the country is not USA.


update location
  set
    state = case
      when country = 'USA' then state
      when country <> 'USA' then null
    end,
    zip = case
      when country = 'USA' then zip
      when country <> 'USA' then null
    end


or you can do


update location
  set
    state = case
      when country = 'USA' then state
      else null
    end,
    zip = case
      when country = 'USA' then zip
      else null
    end


or you can replace null with ''.

The table should look like this now:

id	country	state	zip
2	USA	IL	60191
3	UK	(null)	(null)
5	USA	CA	90210
7	UK	(null)	(null)

You can also used any other value you can obtain from the table in the Update statement, like this.

Given the following table again:

id	country	state	zip
2	USA	IL	60191
3	UK	AA	0
5	USA	CA	90210
7	UK	?	99999

We can do something like this (assuming that state and zip are Character data types).

update location
  set
    state = case
      when country = 'USA' then state
      when country <> 'USA' then country
    end,
    zip = case
      when country = 'USA' then zip
      when country <> 'USA' then country
    end

The table should look like this now:

id	country	state	zip
2	USA	IL	60191
3	UK	UK	UK
5	USA	CA	90210
7	UK	UK	UK

Remember if you can picture what you want the data to look like then you can do it in SQL, it is a declarative programming language after all.

Tracking New Referrals Traffic Through Google Analytics

If you read this blog on a regular basis (thank you) you might notice two tags at the bottom of the site.  These tags have placed this blog on two different blog referral sites.  Since I use Google Analytics to monitor the site, I figured I'd set up Segments to see if the blog referral sites are actually sending traffic to the site.  The goal is for these sites to send 100 new visitors in a month, if they do not then the tags will be removed.

To set up a Segment in Google Analytics you do the following.

1) Click on "Advance Segments" under "My Customizations" on the left.

2) Click the "Create new custom segment" button

3) Drag the "Dimensions" and "Metrics" you want to use to create the Segment

4) Add the Segments to the view by clicking on the "View" button on the top right

5) Now the Segments on show along with everything else on the dashboard.

That is it.  Now I can easily see if these sites are sending any traffic my way.  Feel free to comment.

Subtractive Merge

Programming is a fun thing, sometimes the best thing you can do is to remove or undo what you have done. There are many reason for this, some of which could be:

• You were trying out something and it did not work as planed.
• You were using a new version of a library and it has bugs in it.
• You made changes that were not needed and what to just go back to an old version.

In the end, the result is the same, you have changes that you made and now you want to get rid of them.

If you are using a Code Management System, what you'll need to is a subtractive merge. A subtractive merge is a way to remove or undo changes.

Say you have the following Ruby code

Version 1:

# Hello World program in Ruby

puts "Hello World"

Version 2:

# Hello World program in Ruby

puts "Good Bye World"

Version 3:

# Hello World program in Ruby

puts "Good Bye World"

puts "Hello world again"

Say you are currently at the third version of code ("Hello world again") and you want to go back to the first version of the code ("Hello World"), what you'll need to do is subtractive merge back to the first version of the code.

The way to think of a subtractive merge is that you are removing changes that were done. In the case of doing a subtractive merge back to version 1, creating a new version 4 of the code we will have the following for version 4 of our code:

Version 4:

# Hello World program in Ruby

puts "Hello World"

Example in ClearCase:

To do this in ClearCase from the command line you would do the following:

cleartool merge -to filename -delete -ver \main\branch\versionNumber \main\branch\versionNumber

Example with file hello.rb in the \main\dev stream:

cleartool merge -to hello.rb -delete -ver \main\dev\2 \main\dev\3

Next time you find yourself wanting to rollback or undo changes, try a subtractive merge.

What Side of the Tracks Should I Be On?

Whenever I can, I try to take a train to the city. Trains are great: no traffic jams, no trouble finding a parking spot, and no taking the wrong exit off the highway. Plus they are green (for the most part).

One thing I noticed is that most of the time the train station gives no indicator as to which side of the tracks to be on. In the morning when there are lots of people going to work, this is not an issue. Just go to where the people are. However, this does not work at odd times when you might be the only person at the station.

When at a station by one's self I find the general rules to help tell which side of the tracks to be on.

• Most people wait for the train in the morning on their way to work in the city, when they get off the train on their way home they do not wait around they just leave; therefore the benches and covered waiting areas are on the side of the tracks going to the city.

• If benches and covered waiting areas are on both sides of the tracks, the side with more benches and larger covered waiting areas is the side going to the city (the reason is volume).

• If there are three or more tracks with a fence splitting the tracks, the slide of the fence with more tracks is the side going to the city (again the reason is volume, but this time in the number of trains).

Following these simple rules should help get you on the right side of the tracks, if they do not work please let me and others know by commenting.

Use Pictures to Communicate Not Words

As I move up the ranks at work I find myself asked more to share my ideas; as I get to present my ideas I noticed that the more casual (i.e. drawing on a white board) I present my ideas the better they "go-over" and get remembered by others.

Not only do my ideas "go-over" better with others, they also are more memorable to people. The order seems to go, from worst to best, verbal/Word without images, check list, Power Point/Word with text and pictures, and best of all, white board. I believe this happens for a few reason, but I the number one reason is words can have different means to different people.

Think about it, how often do two different people get the same mean from reading the same thing? Not often. If we did then we would not have introductions to books and teachers would not do anything but read to their classes. No, it seems that just words are bad at communicating ideas to people. Pictures are much better at communicating ideas and staying with us after the presentation. In fact most of our brain (75% according to Unfolding the Napkin) is used for processing what we see.

Which do you find communicates how a Data WebService works better?

Next time you need to communicate something to someone, try using a picture or two.