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