Thursday, April 23, 2009

Using SQL PIVOT with non-aggregate column

I was banging my head on my desk for a while over this one, hopefully this will save you the pain…

I wanted to use SQL 2005’s PIVOT function except the data I was trying to PIVOT was a text column, not an aggregate of a column.  However, the business rule for this table was a 1:1 rule so there’d never be anything to aggregate anyways (even when the data is numeric).

What got me at first was the “Incorrect syntax near the keyword 'FOR'.” error message which didn’t make a whole lot of sense until I realized that added a SUM(1) resolved the problem (hence, the requirement for an aggregate column).

So how, might you ask, do you work around this?  Well, you don’t – the PIVOT function only takes an aggregate value after all.  That being said, our friend MAX and MIN don’t require a numeric value to be passed to them – they are perfectly happy accepting a varchar or nvarchar value.

So instead of trying

PIVOT([MyTextColumn] FOR [MyHeader] IN ([List],[Of],[Columns]))

Use

PIVOT(MAX([MyTextColumn]) FOR [MyHeader] IN ([List],[Of],[Columns]))

Tuesday, April 07, 2009

Roomba Surgery: Replace the Bumper Articulating Arm Optical Sensor Set

A few weeks ago my iRobot Roomba started spinning in circles and failing with a 9-beep error.  This error is related to the two bumper sensors located at 10 and 2 o’clock.  In some cases there might simply be debris behind the bumper interfering with the sensors.  I ruled this out with a disassembly and clean.

If your unit is under warranty you would likely be sent a replacement unit for this case, but that being said, mine was not.  Plus I was secretly looking forward to a full disassembly.

I followed a combination of instructions I found on the net to diagnose and replace the faulty part.

The problem is caused by one or two faulty “Bumper Articulating Arm Optical Sensors” in your robot.  These sensors consist of a plastic arm that swings between two IR sensors.  My readings on the working sensor showed a range of 0-5 volts.

The first sensor I tested functioned correctly switching between  0 and 5 volts.  The second sensor was fixed at 0v.  I had read about shorting out the IR to half blind your Roomba but I was not in the mood to solder fragile wires.  With that said, I managed to find a replacement part from Protech Robots. 

A week and a bit later (the item was shipped USPS ground) my part arrived and it was time to put Roomba back together again.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 002
Remove the face plate

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 004
Remove the side brush, bottom plate, battery and the bumper (after removing the plate that holds the bumper on, the bumper gently lifts off.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 006

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 007
This is one of the Articulating Arm Optical Sensors

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 009
Remove the top of the robot (don’t remove the screws for the handle)

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 011
Disassemble and remove the display

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 012
Gently remove the plastic sheet on top of the PCB

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 013
Disconnect all of the connections from the top of the PCB and remove the screws securing it.

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 016
Gently lift up the PCB towards you (the front of the robot) exposing the bottom connections.  Gently disconnect all of the connections including the bumper.

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 020

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 021
Gently lift out the IR sensor array

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 024
Remove the 2 screws securing each articulating arm sensor and remove the sensors from the robot.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 027

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 029
Here comes the trickiest of the parts.  You need to follow all of the wires from the sensors to the connector located in the bottom right of this photo, ignoring the white wire that connects the two sensors together.  Carefully remove any material securing the cables together.  I highly suggest you draw a visual of the connector and keep track of the individual wires as you remove them.  To remove the individual wires from the connector you need to gently lift up the plastic the secures the wire, and pull at the same time.  I used a small paper clip to lift the plastic.2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 034

Once the connector is reassembled the rest of the steps are just the re-assembly of the robot and testing.

 

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 046
It works!