RE: Hierarchal queries and order by siblings

I believe you're thinking that we have a single level BOM file and could just query the parent for the children. The reason for using a hierarchical query is to navigate down multiple layers through possibly multiple sub-assemblies So, in a traditional BOM file, many parents might be involved in one super-parents exploded BOM structure. What you're talking about would work for a single level BOM, but not a multi-level.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Carel via MIDRANGE-L
Sent: Wednesday, June 29, 2022 10:00 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Carel <coteijgeler@xxxxxxxxx>
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.


With Query Manager (QMQRY) you can create a QM FORM.

Start QMQRY (STRQMQRY)

Take option 1

Press F19 (Shift-F7) to go into SQL mode

Create a new query

Press F5 to run the query

Press F13 (Shift-F1) to go to the FORM editor

Take the first option

Set in parent field BREAK1

Run query again (F5)

See the result and save both query and form.


Op 29-6-2022 om 13:35 schreef Darren Strong:

The syntax of that works, but it scrambles the tree structure.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Carel via MIDRANGE-L
Sent: Tuesday, June 28, 2022 5:02 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Carel <coteijgeler@xxxxxxxxx>
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.


You're sure?

Does this not work?

SELECT paren, child
FROM family
ORDER BY parent, child


Op 28-6-2022 om 21:54 schreef Darren Strong:

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