I had to mis-use the sys_connect_by_path() function to figure out how many children were required multiplied by previous level too, so, given all this rigging to get the "connect by" query to work for my particular use cases, I conclude that I'd probably be better off reverting to the CTE hierarchal method instead.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Friday, July 1, 2022 12:15 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Hierarchal queries and order by siblings
That's an interesting idea. I added a trim like the following, but, it does appear to order by the tree depth I was hoping for. Thank you for the idea.
order by connect_by_root(BPROD)
,sys_connect_by_path(trim(char(BSEQ)),'')
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Hiebert, Chris
Sent: Friday, July 1, 2022 11:50 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Hierarchal queries and order by siblings
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I suggest using SYS_CONNECT_BY_PATH to build a string that contains the desired sort.
order by connect_by_root(BPROD)
, SYS_CONNECT_BY_PATH( cast( BSEQ as Char(20)),':' )
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Tuesday, June 28, 2022 1:55 PM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Hierarchal queries and order by siblings
I'd like to do something like the following, where I can sort by the parent, but then within the parent, sort the siblings, but, this violates SQL syntax. I can choose to sort on one or the other, but not both. Is there a way to do this, or should I use the CTE method when selecting multiple parents?
select
connect_by_root(BPROD) as TOPPARENT,
level as CurLVL,
bchld as Child
from MBM
start with BPROD in (select PROD from PARTLIST)
connect by nocycle prior BCHLD=BPROD
order by connect_by_root(BPROD), siblings by BSEQ
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
