Find the First Non-Null Value in a List

Recently a customer asked how to list the first available phone number for an individual, regardless of what phone type it might be. If there was a Main/Home phone, use it. But if not, list the cell phone instead. If the person had neither but did have a work phone, that would do fine. They only wanted one phone number column in the results, though.

In more technical terms, the customer wanted the first non-null phone number value from the NANames table for a given NameCounter, given the priority of Main/Home first if available, then cell, then work. As it happens, T-SQL provides a very handy function that returns the first non-null value from a list. That function is COALESCE. It works exactly like ISNULL, but instead of taking only two values in the parameter list, COALESCE can take any number of values. The first non-null value in the list is returned as the value of the function. If all the values are null, the value of the function is null.

Here is a simple query on the ShelbyDB database to show how COALESCE can return the first non-null phone number value from a list of phone type values:

select
 Name = FirstMiddle + ' ' + LastName,
 PhoneNumber = coalesce('Home: ' + MainPhones.PhoneNu, 'Cell: ' + CellPhones.PhoneNu, 'Work: ' + WorkPhones.PhoneNu)
from
 Shelby.NANames as Names left join
 Shelby.NAPhones as MainPhones on Names.NameCounter = MainPhones.NameCounter and MainPhones.PhoneCounter = 1 left join
 Shelby.NAPhones as CellPhones on Names.NameCounter = CellPhones.NameCounter and CellPhones.PhoneCounter = 4 left join
 Shelby.NAPhones as WorkPhones on Names.NameCounter = WorkPhones.NameCounter and WorkPhones.PhoneCounter = 2

This query assumes that the Main/Home PhoneCounter value is 1 (it always it), that the Work PhoneCounter is 2 (it always is) and that the Cell Phone PhoneCounter is 4 (it often is, but not always). You should verify the PhoneCounter values for the phone types you want if you use this type of query approach in your own Shelby v.5 database.

In the example I also appended a descriptor to identify which type of phone number was listed. Remember that concatenating a literal string to a null value still results in a null value.

COALESCE comes in handy for any situation where you want to supply the "first available" value from a list of possible values. I have used it to supply either Greeting Type, Salutation, or First Middle name, whichever is available. I have used it to populate an address column with either a preferred address type or the Main/Home address if the preferred type is not available.

Multivalue Parameters in SSRS 2005

I just posted earlier today, but I need to post this topic while it is fresh on my mind. It took some digging, and I want to get this out there while I can remember it clearly. I want to thank Munish Bansal for his blog post on this topic, which was where I found the answer to my search on this question.

In SQL Server 2008 Reporting Services, handling multivalue parameters is relatively straightforward. You just set the parameter property to multivalue and make sure that the filter is set to the IN comparison instead of equals (=). But in SSRS 2005 it is not quite that easy. You set up the parameter the same way, but the filter works differently. Instead of adding it as a filter at all, you add the condition as a Parameter property and put the IN logic into the dataset query directly. For example, the query's WHERE clause would have a condition like this in it:

WHERE My_Column IN (@MyParameter)

Then in the Parameter property of the dataset query you use the JOIN function to turn the multivalued parameter information into a comma-delimited set of values, as shown below. (Click the image to see it full-size.)

Once this is done the parameter will work as desired.

Hopefully you are working in SSRS 2008 because the improved handling of multivalue parameters along with many other enhancements make it almost a non-decision to upgrade as soon as possible to the latest SQL Server version. Denali promises even more enhancements in Reporting Services, and I can't wait to see what's coming next.

Ethernet Over Power thermal problems

If my last post was a departure from my normal topics, this one is a detour through back roads. Don't worry, though, I am working on a lengthy post that will get me back on track with SQL topics.

In the meantime, let me explain something that happened to me last week. The very first symptom was that my desktop would not print to my network printer. The printer itself appeared to be working normally, and I could even pull up the printer's web interface from my desktop. But I could not print. As I tried to resolve the communication between my desktop and the printer, I found my overall network connectivity was becoming intermittently interrupted. The problem escalated until at one point I used the ipconfig utility to manually release my IP address and attempted to renew it. No renewal occurred; DHCP was not working for me.

I would have assumed the router itself was down or having trouble, except that the laptops (yes, my wife and I each have two -- each of us has one for work and one personal laptop) were working fine with their wireless connectivity directly to the router. The only device apparently affected was my desktop. Having concluded this, I ordered a replacement network adapter. My desktop has the new PCI Express ports and no standard PCI ports, and I discovered that network adapters that fit PCI Express ports are difficult to impossible to find in a regular store, even an electronics store. So I ordered it on the Internet, and I expect it to arrive on Monday.

In the meantime my IP phone from work started having trouble. And this made me start the whole process over of trying to find the problem. One device that I thought I had tested and eliminated from the equation was the ethernet-over-power (EoP) adapter that I use to bridge my office computer equipment with the router downstairs, which is just barely within the range of the wireless router signal. But when it became apparent that something more was wrong than just the network adapter in my PC, I took another look. I moved the EoP adapter from the wall plug where it normally sits and moved it downstairs. While carrying it, I noticed that the EoP adapter was not just warm; it was downright hot, especially  the metal ports that hold the RJ45 terminator.

It did not occur to me right then, but gradually I began to wonder if the problem was a thermal one. Our air conditioning has been on the fritz for three days, meaning that my office was warmer than usual. Perhaps the PoE adapter was getting too hot to work properly. To test this, I left the adapter unplugged all last night and plugged it in this morning. Voila. Everything is working again on the network, including my desktop and the IP work phone. Unless something more occurs (which I doubt), I am going to chalk this up to thermal issues. I will probably unplug the PoE adapter in the evening before going to bed and plug it back in when I get to my desk the next day.

I post this as a cautionary tale to those out there who use ethernet-over-power adapters. If your network connectivity becomes unstable, don't overlook the possibility that the EoP adapter is overheating. It might save you some time and expense. As for me, I will have to see about the Amazon.com return policy.