Иерархический запрос с использованием Connect By

Ниже пример контекста похож на мой реальный сценарий.

Товар: XYZ QTY:1

Требуется сырье B,0.002 и полуфабрикат A,0.001. Для производства A мне требуется сырье J,0.1 и полуфабрикат K,0.9. У меня есть продукт K I, прежде чем мне нужно сырье G 0,004 enter code hereи T 0.005.

Мне нужно получить результат общего необходимого всего сырья с его совокупным количеством для производства 10 в количестве продукта XYZ.


sql oracle oracle-ebs
person programlover    schedule 03.11.2015    source источник
comment
Нужно ли в решении использовать CONNECT BY? Это будет более естественно с рекурсивным предложением WITH.   -  person Ken Geis    schedule 03.11.2015
comment
Я думаю, что это хороший вопрос, но за него проголосовали, потому что заголовок очень общий, а проблему трудно прочитать. Кроме того, я не буду дальше работать над решением, пока вы не уточните, действительно ли вам нужно использовать CONNECT BY (т.е. вы используете Oracle менее 11gR2?)   -  person Ken Geis    schedule 04.11.2015

Ответы (2)


arrow_upward
1
arrow_downward

Попробуй это:

SELECT component AS material, 10 * quantity AS quantity
  FROM (SELECT component, quantity,
               CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 'Raw' ELSE 'Semi-Finished' END AS type
     FROM bill_of_materials
    START WITH item = 'XYZ' CONNECT BY PRIOR component = item)
 WHERE type = 'Raw'

Пример на SQL Fiddle дает:

J |        1
G |     0.04
T |     0.05
B |     0.02
person Ken Geis    schedule 03.11.2015
comment
Я забыл о умножении факторов вниз по дереву. Переосмыслю это завтра. - person Ken Geis; 03.11.2015

arrow_upward
1
arrow_downward

Как упоминалось в комментариях @KenGeis, для Oracle 11g вы можете использовать рекурсивный запрос:

with t (p, i , q) as (
  select product, ingredient, qty from test where product = 'XYZ'
  union all 
  select product, ingredient, qty*q from test, t where product = i)
select i, sum(q) qty from t 
  where not exists (select 1 from test where product = i) group by i;

Если по каким-то причинам вам нужна connect by версия, вот моя попытка:

with t1 as (
  select ingredient i, sys_connect_by_path(ingredient, '/') path1, 
         sys_connect_by_path(qty, '/') path2
    from test where connect_by_isleaf = 1 connect by prior ingredient = product
    start with product = 'XYZ' ),
t2 as (
  select i, path1, path2, trim(regexp_substr(path2, '[^/]+', 1, lines.column_value)) val
    from t1,
      table (cast (multiset(
        select level from dual connect by level < regexp_count(t1.path2, '/')+1
        ) as sys.ODCINumberList ) ) lines)
select i, sum(s) qty
  from (select i, exp(sum(ln(val))) s from t2 group by i, path1) group by i

Демонстрация SQL Fiddle для обоих запросов

Подзапрос t1 генерирует список необходимых ингредиентов, а в колонке path2 - коэффициенты, которые нам нужно перемножить. t2 разворачивает эти значения, финальный запрос выполняет умножение и группирует результаты в случае, если было два полуфабриката, использующих одно и то же сырье. Для умножения я использовал ответ из этот ТАКОЙ вопрос.

person Ponder Stibbons    schedule 04.11.2015