-
Query help
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
-
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'
-
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
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|