Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    10

    Unanswered: column substraction with null value

    hi,

    i think it's a newbie question but i couldn't find teh answer, so here is my little problem:

    i 've areturened resultset like this:
    total_created total_closed date
    5 2 ...
    11 ...
    3 ...
    .
    .
    .

    i need to have total_created - total_closed as a third column but i do that all i would like to have 11 - [undefined or null value] = 11 in this example and not:
    total_created total_closed total date
    5 2 3 ...
    11 ...
    3 ...

    how can i set null or undefined value to 0 ?
    thanks a lot, Julien

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Use NVL

    select NVL(total_created,'0'), NVL(total_closed,'0'), (NVL(total_created,'0') - NVL(total_closed,'0')) difference, date from table1;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Use NVL

    Originally posted by aruneeshsalhotr
    select NVL(total_created,'0'), NVL(total_closed,'0'), (NVL(total_created,'0') - NVL(total_closed,'0')) difference, date from table1;
    For a NUMBER, use 0 rather than string '0':

    NVL(total_created,0)

    etc.

  4. #4
    Join Date
    Dec 2002
    Posts
    10
    Thanks a lot !
    It works great !

Posting Permissions

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