× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.