case statement and nested loops

From: oranewbie <teekgeek99_at_hotmail.com>
Date: 6 Mar 2002 11:51:56 -0800
Message-ID: <a606c112.0203061151.1a53a7bc_at_posting.google.com>

Hi,
I'm trying to create a table by using three case statements in my where clause. But the problem is that this query keeps running. I did the explain plan which shows that I have nested loops instead of a hash join. Is there a better way to write out this statement, maybe by using the decode statement or splitting this up and using a union?

create table market.matches tablespace
as
select /*+ use_hash(a,b) */ count(distinct(a.emp_id)) FROM table a, table b
WHERE (case when (a.emp_id is null and b.emp_id is null) then 'Y'

            when (a.emp_id = b.emp_id) then 'Y'                     
            else 'N' end ) = 'Y'
and   (case when (a.dept_no is null and b.dept_no is null) then 'Y'
             when (a.dept_no = b.dept_no) then 'Y'
             else 'N' end ) = 'Y'
and (case when (a.phone_flg is null and b.phone_flg is null) then 'Y'
          when (a.phone_flg = b.phone_flg ) then 'Y'
          else 'N' end ) = 'Y';

thanks! Received on Wed Mar 06 2002 - 20:51:56 CET