Thanks for your reply Tom
Compared to some of the code that I came up, your code is a veritable piece of Shakespeare.
Because of maybe having a need to utilize ALL 21 fields, I've got a feeling that using your example WILL result in some performance hog
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.
midrange.com
