coalesce is not working

• jessica-314018

  SSC-Addicted

  Points: 470

  More actions

  September 27, 2006 at 2:23 pm

  #61831

  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

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

  65765

  564

  6546

  So is not nulls seems to bring up the 3rd record but then how do i include this in a case statement to return any 3.

  thanks

   

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 27, 2006 at 2:47 pm

  #662862

  Try this

  Select

  COALESCE(CASE RTRIM(Mobile) WHEN '' THEN null ELSE Mobile END ,

  CASE RTRIM(Business) WHEN '' THEN null ELSE Business END,

  CASE

  RTRIM(Private) WHEN '' THEN null ELSE Private END) as Number from coalescetest

• jessica-314018

  SSC-Addicted

  Points: 470

  More actions

  September 27, 2006 at 3:46 pm

  #662871

  perfect, that's exactly what I was looking for.

  Thanks alot

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  September 28, 2006 at 12:39 am

  #662909

  Try,

  SELECT COALESCE(NULLIF(Mobile,''),NULLIF(Business,''),NULLIF(Private,'')) as Number

  FROM Table

  Andy