Re: Using SQL to group records with similar elements

Yeah, I love me some CTEs! :) CTE stands for Common Table Expression, but I just think of them as temporary tables. I've created some massive queries that require four or five different CTEs -- some total other files, some create temporary joins to simplify other things, etc.! In this case, I used the CTE to "pivot" the data in your key record. Pretty slick stuff. Plus it was fun to have an opportunity to use a UNION; they're much less common than JOINs.

Joe

Thanks for your reply Joe
I must admit, this one took a LOOOONG time to sink in, but once it did -------NNNNNIIIIICCCCCEEEE
Like I said previously, once I got this piece worked out, I still have a lot more to do, but without this problem being solved, it would be REALLY difficult to move forward
Thanks

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 Joe Pluta
Sent: Thursday, July 21, 2011 6:11 PM
To: Midrange Systems Technical Discussion
Subject: Re: Using SQL to group records with similar elements

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)