How to create a recursive SQL Oracle query?

Question

Oracle

Hello)

There are three tables:

--Таблица каталога продуктов
create table catalog (cid number primary key, -- id раздела
par_cid number references catalog, -- ссылка на родительский раздел
rname varchar2(400), -- наименование раздела
rdescr varchar2(4000), -- описание
rcdate date -- дата создания
);

--Таблица продуктов
create table products (pid number primary key, -- id продукта
rcid number references catalog, -- ссылка на каталог
pname varchar2(500), -- наименование продукта
pdescr varchar2(4000), -- спецификация
punit number references units, -- единица измерения
pper number references persons -- ответственный
);

--Таблица движения продуктов
create table records (rpid number references products, -- продукт
rdate date, -- дата операции
incoming varchar2(2) default '1', -- поступление '1', расход '0'
quantity number, -- количество
rate number -- цена в рублях
);

It is required to write an sql query for output approximately in the following form:

<Name of the section of the directory level1> || Admission. Rub. || Consumption. Rub
...
<Name of the catalog section of the level (K-1)> || Admission. Rub. || Consumption. Rub
<Name of directory section level(K)> || Admission. Rub. || Consumption. Rub
<Product Name1 of this section> || Admission. Rub. || Consumption. Rub || Admission. Quantity || Consumption. Quantity || Remainder
...
<Product Name of this section> || Admission. Rub. || Consumption. Rub || Admission. Quantity || Consumption. Quantity || Remaining
<Name of directory partition level(K)> || Admission. Rub. || Consumption. Rub
<Product Name1 of this section> || Admission. Rub. || Consumption. Rub || Admission. Quantity || Consumption. Quantity || Remainder
...
<Product Name of this section> || Admission. Rub. || Consumption. Rub || Admission. Quantity || Consumption. Quantity || Remaining
<Level1 directory partition name> || Admission. Rub. || Consumption. Rub
... and so on similarly

Calculation for partitions must be performed in accordance with the hierarchy. The number of hierarchy levels is not limited.

Listed all directories:

SELECT cid, par_cid, rname
FROM catalog
START WITH par_cid is null
CONNECT BY PRIOR cid = par_cid
ORDER BY cid;

Deeper into recursion I don't know how to write a query. Can you suggest how to proceed?

Answer 1

Hello Elijah Eliasum!
You are all moving correctly, only in the opposite direction, starting with the leaves.
Use the system fields sys_connect_by_path, sys_connect_by_root
and after the analytical functions of Oracle sum() over(), row_number() over()
like this:

select *
  from (
       select sum(quantity) over (partition by path_) quantity,
              row_number() over (partition by path_) rn
            --,h.*, p.*, r.*
        from (
              SELECT cid, par_cid, rname, 
                     sys_connect_by_path(cid,'/') path_,
                   sys_connect_by_root(cid)     product_cid
                FROM catalog
               START WITH cid in (
                -- возможно есть способ определить листья 
              -- без использования дополнительного вызова 
               select distinct rcid from products
               ) 
              CONNECT BY PRIOR par_cid = cid
       ) h 
       join products p on (h.product_cid = p.rcid)
       join records r ...
    )
where rn = 1

Answer 2

Good afternoon elijah eliasum !
It seems to me that you do not have a very good idea of ​​how a relational DBMS works in general and the MDL SQL SELECT clause in particular.
For a total of the 1st level of the form:
<Name of the catalog section of the level1> || Admission. Rub. || Consumption. Rub
you need to complete one "SELECT" clause
To get the result by type of product:
<Product NameN of this section> || Admission. Rub. || Consumption. Rub || Admission. Quantity || Consumption. Quantity || The remainder
must be executed by another "SELECT" clause.
And to obtain a hierarchy, one must do additional logic in the language from which the "SELECT" clauses are executed.
And more, something the structure of tables resulted by you does not correspond to the 3rd Normal Form - where fields of primary key ("ID")?
Something like this...