User910243567 wrote:That depends on your data.
Need help in writing a query to fetch all the assembled items. I have requirement as part of which I will pass Item1, Item2, Item3.... need to get all the assembled items which are using all this items (not just one all the items).
Below query is fetching assembly items which are using any one items.Is it really doing that?
Select * from bom_bill_of_materials bom, bom_inventory_components bic, mtl_system_items_b msibIf the query above really is finding all the objects that use any 1 of the 3 components, then the following will return objects that are using all 3 of them:
where bic.component_item_id = msib2.inventory_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND msib.segment1 IN ('Item1','Item2','Item3')
How can modify it to fetch only assembly items which are using all the 3 items.
As the above query 'IN' is acts like OR (condition1 or condition2....)
Thanks for your response
where x uniquely identifies the object that must include all 3. (X can be a single column, or some other expression, or a list of several columns and/or other expressions.)
WITH got_cnt ( SELECT ... -- list distinct columns needed for output , COUNT (DISTINCT msib.segment1) OVER (PARTITION BY x) AS cnt FROM bom_bill_of_materials bom , bom_inventory_components bic , mtl_system_items_b msib WHERE bic.component_item_id = msib2.inventory_item_id AND bom.bill_sequence_id = bic.bill_sequence_id AND msib.segment1 IN ('Item1', 'Item2', 'Item3') ) SELECT * -- or list all columns except cnt FROM got_cnt WHERE cnt = 3 ;
select substr(path,2,instr(path,'/',1,2) - 2 product, ... from (select sys_connect_by_path(... ,'/') || '/' path, ... from ... start from ... in ('Item1','Item2','Item3') connect by prior ... = ... ) where instr(path,'/' || 'Item1' || '/') * instr(path,'/' || 'Item2' || '/') * instr(path,'/' || 'Item3' || '/') > 0
User910243567 wrote:The way to post a question has not been modified. You still need to post CREATE TABLE and INSERT statements for some same data, and the results you want from that data.
The Requirement is slightly modified now.
If you put all the target ids in a separate table (perhaps a global temporary table), then you can generalize the query above to work with any number of target items, without having to change the query at all.
WITH got_descendants AS ( SELECT SYS_CONNECT_BY_ROOT id AS ancestor_id , id AS descendant_id FROM table_x WHERE id IN (target1, target2, ..., targetn) CONNECT BY parent_id = PRIOR id ) SELECT ancestor_id FROM got_descendants GROUP BY ancestor_id HAVING COUNT (DISTINCT descendant_id) = n ORDER BY ancestor_id ;
User910243567 wrote:What is a "sub-level"? How is it related to the level?
I have built the query as per your suggestions above, but there seems to be issue when the item entered is found in more than 1 sublevel. When i checked the path using SYS_CONNECT_BY.
As my current logic is returning the below result based on the item count: (Items I entered: P-1, P-345, P-2)
As see below Item P-1 appears in more than one sublevel.
My logic is based on the item count(in this case 3) and number of rows it returned.(3 below)No kidding, you need to post CREATE TABLE and INSERT statments for the sample data, and the results you want from that data. Explain how you get those results from that data. It can be very helpful to make up terms, like "sub-level", if you don't know a common name for something, but make sure you define them.
Can someone please guide, I can mail more details if you share your email id.There's no need for that. Post your CREATE TABLE and INSERT statements, results and explanation here, where lots of different people can help you.
User910243567 wrote:If you had my email address, would you send me sensitive information, knowing that I might post it anywhere? Of course not! You would change the data, and probably the names of the columns and the tables as well, so that I couldn't do any harm.
Thanks for your reply, because of the sensitive of the work i am doing, i cannot share the details on this forum. thats the reason i was requesting for email address.