AS400 SQL - Select records from a file where there are unique values based on the grouping of multiple columns

Question

I run an SQL for a user and it work perfect! The code is pretty straight forward delete a table, create a table where we examine coulmn1 & group by coulmn1. Write out to the table any all data where column2 data differs. See code below-

DROP TABLE IF EXISTS OUTPUT1;            
                                
CREATE TABLE LIB1/OUTPUT1 AS(           
    SELECT *                                   
    FROM INPUTPF                            
    WHERE COLUMN1 IN (                            
    SELECT COLUMN1                                
    FROM INPUTPF                            
    GROUP BY COLUMN1                              
    HAVING COUNT(DISTINCT COLUMN2) > 1)
    )        
    WITH DATA;

The user now has asked to include a third element (i.e. COLUMN3) in the selection criteria. That is, using the code above they want to select COLUMN2 if they differ OR COLUMN3 if it differs.

What is the most efficient way to expand the table given the above code?

Not sure I follow what you are asking, include some example data and the results you want. — Charles
– Charles, Commented Oct 4, 2024 at 20:11
If I'm reading it right, you just need to add a condition in your HAVING clause: having count(distinct column2) > 1 or count(distinct column3) > 1 — Hellion
– Hellion, Commented Oct 7, 2024 at 16:56

stenag · Accepted Answer · 2024-10-09 10:25:03Z

@Hellion comment is correct above.

I think you get all records for a column 1 where there are more than 1 column 2 values. I think the change is to get all records for a column 1 for the above criteria and also if column 3 has more than 1 value.

In the example column 1 is t1a, column 2 is t1b, column 3 is t1c, etc.

Example,

CREATE TABLE qtemp.t1
     (                                                     
      t1a  char( 10) CCSID 37 NOT NULL DEFAULT ''   ,  --
      t1b  char( 10) CCSID 37 NOT NULL DEFAULT ''   ,  --
      t1c  char(10) CCSID 37 NOT NULL DEFAULT ''   ,  --
      t1d  char(10) CCSID 37 NOT NULL DEFAULT ''  
      );
select * from t1;
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('a','a','a','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('a','b','a','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('a','c','a','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('a','a','b','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('a','a','c','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('b','a','c','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('b','a','c','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('c','a','c','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('b','a','d','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('b','b','d','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('c','a','a','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('d','a','a','a');
insert into qtemp.t1 (t1a,t1b,t1c,t1d) values('e','a','a','a');

drop table qtemp/OUTPUT1;           
CREATE table qtemp/OUTPUT1 AS(           
    SELECT *                                   
    FROM t1                            
    WHERE 
    (
    t1a IN (                            
    SELECT t1a                                
    FROM t1                            
    GROUP BY t1a                              
    HAVING COUNT(DISTINCT t1b) > 1)
    )
    or 
    (
    t1a IN (                            
    SELECT t1a                                
    FROM t1                            
    GROUP BY t1a                              
    HAVING COUNT(DISTINCT t1c) > 1)
    )    ) 
    WITH DATA;
select * from output1;
select * from t1;

column 1 = 'e' is not in output1 because neither column 2 or column 3 have more than 1 value when column 1 is 'e'. column 1 = 'f' is not in output1 because neither column 2 or column 3 have more than 1 value when column 1 is 'f'.

Collectives™ on Stack Overflow

AS400 SQL - Select records from a file where there are unique values based on the grouping of multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related