Newbee Query Problem

[horsecock]

Hi,

Being new to DB2 I'm having a small problem that I'm sure is quite easy to someone smarter than me.

I have a table with duplicate entries and I need to know how to code a query to exctract certain fields from the table with a count > 1. I can do this with one field however I need to do this with multiple fields.

Working 1 Field Query,

 select field_3 from CUSTOMER where field_3 <> ' ' group by field_3 having count(*) > 1 

What doesn't work,

 select * from CUSTOMER where field_3 <> ' ' group by field_3 having count(*) > 1 

Table Name: CUSTOMER
# of Fields: 5 (e.g. Filed_1, Field_2, Field_3, etc)

If some could help me with this I would be eternally grateful.

Thanks in advance.......

[cburnett]

Provided you are on V8.1 or later, try the following:

create table test(col1 char(3),
                  col2 char(3),
                  col3 char(3));
 
insert into test values ('1A','1B','1C');
insert into test values ('2A','2B','2C');
insert into test values ('3A','3B','3C');
insert into test values ('3A','3B','3C');
insert into test values ('3A','3B','3C');
insert into test values ('4A','4B','4C');
insert into test values ('5A','5B','5C');
insert into test values ('5A','5B','5C');
 
with x(rnum, col1, col2, col3) as (select rownumber() over(order by col1, col2, col3),
                                          col1,
                                          col2,
                                          col3
                                     from test)
select distinct x1.col1,
                x1.col2,
                x1.col3
  from x x1,
       x x2
 where x1.col1 = x2.col1
   and x1.col2 = x2.col2
   and x1.col3 = x2.col3
   and x1.rnum <> x2.rnum

[cburnett]

Thinking some more on this, there is a much simpler way:

select distinct col1,
                col2,
                col3
  from test
 group by col1,
          col2,
          col3
 having count(*) > 1