Re: case statement and nested loops

I'm not sure

1. whether your working with one or two tables. Your example quotes one table named "table" which you want to join with itself.
2. what you want to do, creating a "table as select from" which contains only a count seems to make small sense ;-)

Here are two possible ways to do the job:

Only one table/Identifying dublettes:

SELECT emp_id,dept_no,phone_flg
  FROM table
 GROUP BY
       emp_id,dept_no,phone_flg
HAVING count(*) > 1
/

Two tables/Finding matches

SELECT emp_id,dept_no,phone_flg
  FROM table_a
INTERSECT
SELECT emp_id,dept_no,phone_flg
  FROM table_b
/

On 6 Mar 2002 11:51:05 -0800, teekgeek99_at_hotmail.com (oranewbie) wrote:

>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!

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Mar 09 2002 - 04:51:32 CST