Query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query help

  1. #1
    Join Date
    Feb 2002
    Posts
    267

    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

  2. #2
    Join Date
    Aug 2002
    Posts
    115
    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'

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
  •  


Click Here to Expand Forum to Full Width