Re: case statement and nested loops

This is a better way to write your query:

select count(distinct(a.emp_id))
from a,b
where ((a.emp_id is null and b.emp_id is null) or a.emp_id=b.emp_id) and ((a.dept_no is null and b.dept_no is null) or a.dept_no=b.dept_no) and ((a.phone_flg is null and b.phone_flg is null) or a.phone_flg=b.phone_flg)

Ashish

"oranewbie" <teekgeek99_at_hotmail.com> wrote in message news:a606c112.0203061151.1bbf9963_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 Sat Mar 09 2002 - 09:54:10 CST