Re: Using SQL to group records with similar elements -- MIDRANGE-L

Alan, are you trying to find matches for only one record (the first) or are you trying a more comprehensive test of finding any records that match any other records? The reason I ask is that it can be even simpler. If one record is your master record, it becomes very easy (in this case, the one record is the one with a key of 00001:

// Create a temporary table of all target values
// Select from SYSDUMMY1 makes sure there is a blank in the table
with t1 as (
select field1 as value from Example where key = '00001' union
select field2 as value from Example where key = '00001' union
...
select field21 as value from Example where key = '00001' union
select ' ' as value from sysibm/sysdummy1
)
// Select records where every value is in the target value list
SELECT * FROM Example WHERE
field1 in (select value from t1) and
field2 in (select value from t1) and
...
field21 in (select value from t1)

In trying to explain my problem to one of my colleagues, I may have inadvertently stumbled upon the answer.
Here is the example file again
Key field1 field2 field3 field4 field5 field6 field7 etc.-->
00001 360 160 202 400 118
00002 160
00003 400 202 600
00004 160 118
00005 202
00006 400
00007 111
Here is my SQL

SELECT * FROM Example WHERE
field1 in ('360', '160', '202', '400','118', ' ') and
field2 in ('360', '160', '202', '400','118', ' ') and
field3 in ('360', '160', '202', '400','118', ' ') and
field4 in ('360', '160', '202', '400','118', ' ') and
field5 in ('360', '160', '202', '400','118', ' ')

This results in the following being displayed
KEYVALUE FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7
00001 360 160 202 400 118
00002 160
00004 160 118
00005 202
00006 400

I can't believe it seems so simple.
If it turns out to be THIS simple, having gone almost 3 days.
Looking at my notes and the CRAP that I came up with ----- it CANT be this simple
I'll have to expand my test cases

Granted, the example I showed, was just that, an example. I may have to use dynamic SQL to create the SQL code because it could be up to 21 fields that have to be catered for.

I want to thank everyone who answered my e-mail with their suggestions

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom E Stieger
Sent: Thursday, July 21, 2011 3:21 PM
To: Midrange Systems Technical Discussion
Subject: RE: Using SQL to group records with similar elements

Alan-

I appreciated the challenge of this problem, and I am not sure if my approach is broad enough for your specific application, but it solves the example given.

If the number of fields to check are huge this would not be a good candidate. Also I am pretty sure the performance on this is terrible.

Use the following at your own risk:
----------
create table mylib.testingsql
(keyvalue CHAR(5),
field1 CHAR(3),
field2 CHAR(3),
field3 CHAR(3),
field4 CHAR(3),
field5 CHAR(3),
field6 CHAR(3),
field7 CHAR(3) )
;

insert into mylib.testingsql
values('00001','360','160','202','400','118','','');
insert into mylib.testingsql values('00002','160','','','','','','');
insert into mylib.testingsql
values('00003','400','202','600','','','','');
insert into mylib.testingsql values('00004','160','118','','','','','');
insert into mylib.testingsql values('00005','202','','','','','','');
insert into mylib.testingsql values('00006','400','','','','','','');
insert into mylib.testingsql values('00007','111','','','','','','');

;

with master_all (keyvalues, fields)as ( select keyvalue, field1 from
mylib.testingsql
union select keyvalue, field2 from mylib.testingsql
union select keyvalue, field3 from mylib.testingsql
union select keyvalue, field4 from mylib.testingsql
union select keyvalue, field5 from mylib.testingsql
union select keyvalue, field6 from mylib.testingsql
union select keyvalue, field7 from mylib.testingsql), master as (select fields from master_all where keyvalues = '00001' and fields<> '')

select * from mylib.testingsql
where (field1 in (select * from master) or field1 = '') and (field2 in (select * from master) or field2 = '') and (field3 in (select * from master) or field3 = '') and (field4 in (select * from master) or field4 = '') and (field5 in (select * from master) or field5 = '') and (field6 in (select * from master) or field6 = '') and (field7 in (select * from master) or field7 = '') ;

I get the following results:
----------------
00001 360 160 202 400 118
00002 160
00004 160 118
00005 202
00006 400

Hope this helps and gets you started along the right direction. I still have a feeling there is a better/easier way.

-Tom Stieger
IT Manager
California Fine Wire

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, July 21, 2011 9:38 AM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: Using SQL to group records with similar elements

Good afternoon all
I'm not too sure if the subject line truly describes my predicament, so here goes I've been working on this for the past couple of days without success, so I am hoping someone has an answer to my problem I have a file that contains (among other things) 21 occurrences of the same size field that can contain similar data For example

Key field1 field2
field3 field4 field5
field6 field7 etc.-->
00001 360 160
202 400 118
00002 160
00003 400 202
600
00004 160 118
00005 202
00006 400
00007 111

Using record key 00001 as a starting point, I need to group all records that have any of the non- blank values in any of the occurrences, EXCLUDING those records that have OTHER none blank values Using the above example Keys 00002, 00004, 00005, 00006 would be grouped with
00001 Key 00003 would not (value 600 NOT in Key 00001) Key 00007 would not (value 111 NOT in Key 00001)

Hopefully, what I am asking makes sense, AND is feasible As always, any and all suggestions are welcome

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.