how to use coalesce?

homer.favenir · #1 (**permalink**) 08-26-08, 20:45

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

Pat Phelan · #2 (**permalink**) 08-26-08, 23:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech