Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Probs with case expression

    hi all,

    i want to make following:

    field1 + field2 + field3

    but only when these fields were not NULL !

    i tried as follows:

    ......
    CASE WHEN b.F1 IS NULL THEN 0 ELSE b.F1 END,
    CASE WHEN b.F2 IS NULL THEN 0 ELSE b.F2 END,
    CASE WHEN b.F3 IS NULL THEN 0 ELSE b.F3 END,
    (b.F1+b.F2+b.F3) as XXX, b.F1, b.F2, b.F3,.....

    the result column XXX is NULL for each row.

    what to to ?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Probs with case expression

    does this help ?


    CASE WHEN b.F1 IS NULL THEN 0 ELSE b.F1 END+
    CASE WHEN b.F2 IS NULL THEN 0 ELSE b.F2 END+
    CASE WHEN b.F3 IS NULL THEN 0 ELSE b.F3 END as XXX,
    b.F1, b.F2, b.F3

    Sathyaram

    Originally posted by ulirebmann
    hi all,

    i want to make following:

    field1 + field2 + field3

    but only when these fields were not NULL !

    i tried as follows:

    ......
    CASE WHEN b.F1 IS NULL THEN 0 ELSE b.F1 END,
    CASE WHEN b.F2 IS NULL THEN 0 ELSE b.F2 END,
    CASE WHEN b.F3 IS NULL THEN 0 ELSE b.F3 END,
    (b.F1+b.F2+b.F3) as XXX, b.F1, b.F2, b.F3,.....

    the result column XXX is NULL for each row.

    what to to ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Re: Probs with case expression

    Originally posted by sathyaram_s
    does this help ?
    Sathyaram

    ...and how it does :-)

    thx a lot - you 're great!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can also use coalesce:


    COALESCE(b.F1,0)+coalesce(b.F2,0)+coalesce(b.F3,0) as XXX,


    Makes it a litter easier to read.

    Andy

Posting Permissions

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