Merry Christmas, Sauron!

Roamatherapy!

Ahh, Christmas, the time for presents, tasty food, and ruminating on the memories of days when Santa Claus really existed outside mere childhood imagination.

For the cheapskate who's always wanted to dabble in aromatherapy, and because "road aromatherapy" or "road rage smells like Christmas" didn't seem catchy enough, I present ROAMATHERAPY. 


TRY THIS

1. Go to your local live Christmas Tree seller. This might be your local supermarket with trees and wreaths out front, or some guy selling these out of a tent along the roadside.
   
   
2. Grab a discarded sprig of Douglas Fir. You also could ask nicely, but I doubt the seller would mind if it's already fallen away from the tree.
   
   
3. Using Duck™ tape, a chip clip, or a convenient crevice between the vent and the dash, secure the sprig so that hot air from the vents wafts across it.
   
   
4. Turn on your car's heater. Akin to the principles used by various aromatherapy diffusers, the pleasant aroma of the Douglas Fir is expressed from the plant material into your car's interior.

 
If your memories of Christmas are gloomy or nonexistent, hopefully this will bring you some of the Christmas cheer I associate with the holiday that is far more enjoyable than its rampant consumerism.

Merry Christmas!

 

String Concatenation and Implicit Conversions

I needed to modify a SQL function to strip some leading zeros in order to concatenate a string. Part of this process involved dealing with pluralization of the number of cents (I'm picky that way).

SELECT
 CASE
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1 THEN ''
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1 THEN 's' 
  ELSE ''
 END
 

The idea is that if the value (0.30 representing cents) translates into a number greater than 1, the resulting concatenated sentence will read something like "The discount is 30 cents." If the value equals 1, however, it would read "The discount is 1 cent."

When the above is executed, I get an error:

 Msg 245, Level 16, State 1, Line 1
 Conversion failed when converting the varchar value '0.03' to data type int.


At first this baffled me. Data type int?? I wasn't trying to obtain an integer value, I was ultimately trying to obtain a varchar for this string I'm building.

Then it hit me, SQL is performing an implicit conversion when it is comparing the value resulting from the conversion first to decimal, then float, and finally to varchar, with 1.

Lesson learned, when attempting to compare two varchar with numeric values, make sure that if the value being tested is decimal, that the static value being used to compare it against is also decimal.

I made the following changes and now it works flawlessly.

SELECT
 CASE
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1.0 THEN ''
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1.0 THEN 's' 
  ELSE ''
 END
 

Pretty Formatting for XML or HTML

Sometimes it's helpful to pretty up XML or HTML in order to more easily analyze it. Many tools create machine-readable XML or HTML, which is well-suited to being parsed by computers, but whose formatting can be painful for a human to wade through. 

You might think, why do humans need to be able to work with machine-readable text, anyway? Well, if you happen to be the new guy on the job and get stuck with having to debug some code that spits out some XML or HTML you've never dealt with before for some complex data transformation processing, you can see why it might be necessary, particularly if something changes between the sender and receiver and the XML, as produced, no longer does the job.

In Notepad++, one of my favorite text editors, I can utilize the TextFX plugin to take a given chunk of XML or HTML and clean it up, neatly indenting the nodes and attributes for easier examination. I paste the text into the editor and select TextFX => TextFX HTML Tidy => TiDy clean document - wrap. Finally I select Language => XML or HTML to enable syntax highlighting.

Prior to discovering Notepad++, I had discovered the freeware MoreMotion XML Editor a few years ago when I was having to dissect some XML being output by a tool whose XML output I needed to clean up. Provided the XML was well-formed, I could very quickly pretty it up by clicking XML => Pretty Format, or even more easily with a quick key combo, Shift-Ctrl-P.

BEFORE: Messy, messy XML!

AFTER: Ahhh, clean, legible, structured XML.

Unfortunately, the original download URL for MoreMotion XML Editor appears to be defunct, leading to a parked page rather than the actual file. You can download MoreMotion's more comprehensive XML application suite here, but if you just need relatively simple XML editing provided by the older (and much smaller, around 1.3 MB) freeware tool, this working download link fits the bill. The ZIP file contains the EXE, which can be copied to a convenient folder and run.

A few caveats, however. MoreMotion seems to have a few editing issues with extremely long lines of text; you may notice keypresses on such lines may take a long time to register. Also, whereas Notepad++ will do its best to deal with poorly-formed XML or HTML and log any errors it encounters, MoreMotion will not allow you to proceed until the errors are resolved, and in my experience some of these errors can be difficult to pin down.

Nevertheless, for relatively small chunks of well-formed XML or HTML, MoreMotion XML Editor makes cleaning up XML or HTML just a few steps quicker than Notepad++.