Re: Locating Primary records with identical detail records

Nice solution Elvis....

I'm thinking there's another way to do it, I've been playing around
with EXCEPT and INTERSECT...

Also, back to John...how big is this table?

If large, perhaps a solution would be to create a hash of the function
IDs for master, then simply find the masters with the same hash.

Charles

On Fri, May 29, 2009 at 1:28 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I haven't been following this thread, but Joe's reply reminded me of a need
for MySQL's GROUP_CONCAT function on System i. I've thought about a lack of
this function recently and found a way to (sort of) simulate it using the
recursive CTE functionality in DB2 for i.

Here is the query that may work on the sample set of data if Joe's
interpretation of John's request is correct:

with
cte1 as
 (select detail_id,function_id,
         row_number() over(partition by detail_id) as row#
  from myFile),
cte2 (detail_id, str, cnt, cnt_max) AS
 (SELECT detail_id, VARCHAR('', 32000), 0, MAX(row#)
  FROM   cte1
  GROUP BY detail_id
  HAVING COUNT(*) > 1
  UNION ALL
  SELECT cte2.detail_id, cte2.str || RTRIM(CHAR(cte1.function_id)),
         cte2.cnt + 1, cte2.cnt_max
  FROM   cte1,cte2
  WHERE  cte1.detail_id = cte2.detail_id AND
         cte1.row# = cte2.cnt + 1 AND
         cte2.cnt < cte2.cnt_max ),
cte3 as
 (select detail_id,str from cte2 where cte2.cnt = cte2.cnt_max)
select distinct a.detail_id, a.str
from cte3 a join cte3 b using(str)

Of course, there is a limit of a maximum VARCHAR length to consider when
concatenating FUNCTION_IDs. Hopefully a max concatenated value doesn't
exceed the 32K VARCHAR limit.

If on the other hand we had a GROUP_CONCAT support in DB2, this query would
be MUCH friendlier.  So John  (and other readers), don't be shy and submit a
DCR to IBM:
https://www-912.ibm.com/r_dir/ReqDesChange.nsf/Request_for_Design_Change?Ope
nForm
More customers they hear from, more likely it would make the cut for next
release.

I sure would like to see it as a built-in in my favorite RDBMS, even with a
32K limit consideration.

BTW John, sorry if I'm totally off the mark. As I've said, I haven't been
following the thread.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Locating Primary records with identical detail records

Bill Meecham wrote:

or restated,
I just want to see those ID's that have the same FUNCTION.
in your example Detail ID=E is an orphan record if the detail is owned by

the master.

still, my example would find ID's with the same FUNCTION, even the

orphans.

No, here's what John wants: he wants basically to see all headers which
have the same SET of detail records: if master A has three function
record, M1, N4 and Z5, it would match any and every master that ALSO had
exactly those three detail records.

It's a set-based operation, so it sounds SQL-ish, but in reality its not
so simple, because I don't know an easy way to build a field that
consists of a concatentation of the contents of a specific field for
every detail record in a 1-to-many relationship.

Basically, John needs a syntactical construct in SQL that, for master A
above, returns "M1N4Z5".

I tried to jigger a way using quantified predicates, but no luck.  I
don't think there's a way in SQL to directly compare two results sets.
So even if you came up with a list of possible matches (say through a
cartesian product), you couldn't somehow compare their children:

Say that POSSIBLES contains MasterID1 and MasterID2:

select * from POSSIBLES where
 (select function from DETAIL: where master = MasterID1) =
 (select function from DETAIL: where master = MasterID2)

I don't think there's any syntax for comparing subqueries.

Joe


--
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.