Results 1 to 4 of 4

Thread: How to remove records with NULL values from computed columns

  1. #1
    Join Date
    Aug 2004
    Posts
    52

    How to remove records with NULL values from computed columns

    I've 2 columns after executing query,

    one column from those 2 is calculated column.

    I am getting resultset as follows



    ACCOUNT ¦(calculated column)

    ------------------------------

    100 ¦alpha

    100 ¦NULL



    My concern is how should I remove record with NULL from final resultset ?

    If I give some name to that calculated column say 'Name'

    and tried to put in main query as

    "where 'Name' IS NOT NULL"; it doesnot work.

    Can you have any workaround for it?

  2. #2
    Join Date
    Aug 2004
    Location
    UK
    Posts
    3

    What about...

    I am guessing, as you have not supplied the SQL, but you used a function such as sum(x) or count(*)?

    Have you tried the having clause?

    a where clause comes first, then the grouping is applied, which is why where clauses have no affect on the actual groups, only the data put in them.

    So what about:

    select object_type, count(object_name)
    from dba_objects
    group by object_type
    having count(object_name) > 20
    ;

    You may have:

    ....
    having sum(stuff) is not null
    ....

    Rgds
    Fuzzypig

  3. #3
    Join Date
    Aug 2004
    Location
    Missouri
    Posts
    1

    NVL function

    this is a funtion in Oracle 9i.

    Basicly it replaces the Null value with a 0.

    NVL (x,y)
    where y represents the value to be substituted if x is null.

    so it would be NVL(calculated sum,0)
    if you wanted to substitute 0 for the null values in the calculation.

  4. #4
    Join Date
    Sep 2004
    Posts
    7
    The problem is because you are using the calculated value column 'Name' in your where clause. Where clause will first filter the records. To this resultset, the calculation is applied and then the final calculated values are displayed.
    Threfore at the time of filtering in the where cluase, Oracle would not know your calculated column bacause the calculation is done after this step.

    Two scenarios exist:
    1. The calculation is based on a column containing null values - Here, use the null column used as a part of your calculation to filter out the records in the where clause.

    Eg:

    If your columns are as such:
    Account
    Commission - can have nulls
    Name (calculated column) - say (commission * 100)

    Then use:
    select account,(commission *100) as Name
    from tbl
    where commision is not null; -- use the column which is part of the calculated field, and can be null, to eliminate null records

    Scenario2:
    The calculated column is NOT based on any null column. - Here, you could try the following, using an inline view.

    select * from (
    select account, (a*b) as Name
    from tbl)
    where Name is not null
    -Vinita
    Last edited by vinitasinha; 09-28-2004 at 11:10 PM.

Posting Permissions

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