Query help

[Sonia]

Hi,

I have a problem in counting the null values.

I have 2 tables

Emp_main

Emp_no---Date1----------Date2

1-------03-JAN-04
2-------03-JAN-04-----03-JAN-04
3------ 03-JAN-04
4-------03-JAN-04-----03-JAN-04

Emp_Sup

Emp_no-----Code---------Loc

1-------ddls33------Bangalore
2-------ddkls3------Bangalore
3------ ekdk4-------Bangalore
4-------kkss4-------Bangalore


I need to take the count which has a valide date ( any one of date1 or date2) for which i am using this query
I want the o/p to be 4. But i am getting 2. Where am i wrong ???

Instead of 'AND' if i say 'OR' i get 16.


select count(*) from Emp_main a,Emp_sup b
where a.date1 is not null AND
a.date2 is not null
and a.emp_no=b.emp_no
and b.loc='Bangalore'

can somebody help me ??

Regards
Sonia

[aspdba]

You want any 1 of the date to be valid ..isnt it?


select count(*) from Emp_main a,Emp_sup b
where (a.date1 is not null OR a.date2 is not null)
and a.emp_no=b.emp_no
and b.loc='Bangalore'

[balajiyes]

Gurus,

Can we do something like this?

09:56:53 :ccdw> select count(coalesce(a.date1, a.date2)) from Emp_main a,Emp_sup b
09:56:58 2 where
09:56:58 3 --(a.date1 is not null or
09:56:58 4 --a.date2 is not null)
09:56:58 5 --and
09:56:58 6 a.emp_no=b.emp_no
09:56:58 7 and b.loc='Bangalore';

COUNT(COALESCE(A.DATE1,A.DATE2))
--------------------------------
4