coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.