Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: how to use coalesce?

    hi,
    anyone please....
    in my formula i have to put coalesce function to each field so that if a field is null it will still continue to compute.
    e.g.
    Code:
    (
      (q.ManHours -
        coalesce(q.OTHrs,0)) -
          coalesce(q.regbreak,0) -
            coalesce(q.regadjustment,0)
    ) as 'Reg Hrs'
    do you know how to simplify coalesce? instead of distributing it on each field.

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Coalesce() function isn't distributive, if that is what you're asking. There may be ways to simplify the expression depending on how you want NULL values treated in each of the steps, but the answer you have now is intuitive to me.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •