Good morning everybody,
I would like to have some view on one of our warehouse and I wonder if this is doable easilly without having to do some complex PLSQL or Java programming.
I have a structure that is like this (simplied as much as possible to keep only the interesting part of my problem because in reality I have many more tables and lots of joins):
CREATE TABLE boxes (
id number,
name varchar2(4000),
grp varchar2(4000),
parent_id number
);
INSERT INTO boxes(id, name, parent_id, grp) VALUES(1, 'Box 1', NULL, 'cars');
INSERT INTO boxes(id, name, parent_id, grp) VALUES(2, 'Box 999', 1, null);
INSERT INTO boxes(id, name, parent_id, grp) VALUES(3, 'Box 989', 1, null);
Boxes are a two level structure. Root levels don't have parent_id. And if there is a box inside the "root" level box, then that box has a parent_id. What is also interesting to mention is that root boxes have a group. It's a tag we apply on that box to know to which department it belongs.
Nox, in each box, I can have some content. And this is what I would like to have a view on. I can have content in the "root" box or in any of the boxes inside.
So, if I visually try to represent my current structure, i have this; (and indent means it's inside). My boxes 999 and 989 are inside "Box 1"
"Box 1"
"Box 999"
"Box 989"
Now, if I add some content.
CREATE TABLE contents(
box_id number,
content_id number
);
INSERT INTO contents(box_id, content_id) VALUES(1, 209);
INSERT INTO contents(box_id, content_id) VALUES(1, 210);
INSERT INTO contents(box_id, content_id) VALUES(1, 223);
INSERT INTO contents(box_id, content_id) VALUES(2, 787);
INSERT INTO contents(box_id, content_id) VALUES(3, 786);
I can add content in both levels (roots or not roots). And I can even have all possible mix-and-match. For instance a root box may have only content and not other boxes inside. I can have a root box with no content but content in boxes that belongs to it. If I try to represent this, it may look like this:
INSERT INTO boxes(id, name, parent_id, grp) VALUES(2, 'Box 2', NULL, 'cars'); -- this is a root level box.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(3, 'Box 33', 2, null); -- this is a box that belong to parent box having id equal to 2
INSERT INTO contents(box_id, content_id) VALUES(3, 787); -- yes one content may belong to multiple boxes.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(7, 'Box 7', NULL, 'trains'); -- this is a root level box.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(8, 'Box 56', 7, null); -- this is a box that belong to parent box having id equal to 7
INSERT INTO boxes(id, name, parent_id, grp) VALUES(9, 'Box 88', 7, null); -- this is a box that belong to parent box having id equal to 7
INSERT INTO contents(box_id, content_id) VALUES(88, 77);
INSERT INTO boxes(id, name, parent_id, grp) VALUES(10, 'Box 10', NULL, 'cars'); -- this is a root level box.
Now, I would like to have a view on root boxes.
I would like to have a result presented like this:
For each group, count the root boxes ONLY and their content. But presented in a table by range.
So, if I have to take this exemple I want to have:
GRP: 0 [1-5] [6-10]
-------- ------ -------- -------
CARS 1 2 0
trains 0 1 0
The headers are some ranges I would like to have
Box 10 has no content. So, it goes in category cars with 0 content.
Box 1 has 5 content. Their ids are 209, 210, 223 but ALSO 787 (because box with id equals to 2 belong to 1) and also 786 (that belong to box with id = 3)
Box 2 has 1 content. Its id is 787 because we have it through box with id = 3.
Box 7 has one content.
Is this doable easily in Oracle 19? And could you guide me to get that? I simplified here a lot. I will have more ranges and instead of counts I need to do some calculations on the content itself (like calculate the weight etc). But if I have an idea how to count, I think I can move forward easily.
Any help will be appreciated.
Thank you