Creative COALESCE()

• WILLIAM MITCHELL

  SSChampion

  Points: 13766

  More actions

  June 1, 2007 at 9:17 am

  #176875

  I could not find a discussion thread for Arthur Fuller's article, so here goes...

  Another way to use COALESCE() is that it can be used in a join expression. This is a little rough, but it illustrates the idea:

  I have an Office table with an OfficeID and a BillingOfficeID. Normally the office statements go to the street address, except if there is a BillingOfficeID then the statements need to go there instead. A brief code snippet might be as follows:

  SELECT
    o.FirmCode,
    o.StreetAddress,
    o2.MailingAddress
  FROM
    Office o
    INNER JOIN Office o2
      ON COALESCE(o.BillingOfficeID,o.OfficeID) 
        = o2.OfficeID
  

  T-SQL allows the join using an expression, in this case COALESCE() which returns the first non-NULL value in the list.

   

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  June 1, 2007 at 10:02 am

  #710382

  Interesting idea, I never considered that possibility.  Thanks for a great example that really helps illustrate the concept.

  James.