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?

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

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.

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