

/* QUERIES */


/* To zapytanie wykorzystam we wszystkich innych */

select
  p.id, p.name, ptp.amount, p.assemblycost, p.assemblymass,
  level depth,
  1 || sys_connect_by_path(ptp.amount, '*') amount_expr,
  connect_by_isleaf is_leaf
from
  Part_to_Part ptp join Part p 
	on p.id = ptp.id_child
start with 
  ptp.id_parent is NULL
connect by nocycle
  ptp.id_parent = prior ptp.id_child;



/* ile poszczegolnych czesci w calosci? */

select
  substr(lpad(' ', 2*depth-1) || name, 1, 20) name,
  eval.uate(amount_expr) amount
from (
select
  p.id, p.name, ptp.amount, p.assemblycost, p.assemblymass,
  level depth,
  1 || sys_connect_by_path(ptp.amount, '*') amount_expr,
  connect_by_isleaf is_leaf
from
  Part_to_Part ptp join Part p 
	on p.id = ptp.id_child
start with 
  ptp.id_parent is NULL
connect by nocycle
  ptp.id_parent = prior ptp.id_child
);



/* ile kazdej czesci w calosci?  */

select
  name, sum(eval.uate(amount_expr)) amount
from (
select
  p.id, p.name, ptp.amount, p.assemblycost, p.assemblymass,
  level depth,
  1 || sys_connect_by_path(ptp.amount, '*') amount_expr,
  connect_by_isleaf is_leaf
from
  Part_to_Part ptp join Part p 
	on p.id = ptp.id_child
start with 
  ptp.id_parent is NULL
connect by nocycle
  ptp.id_parent = prior ptp.id_child
)
group by name;


/* ile kazdej czesci detalowej w calosci? Z leaves by */

select
  name, sum(eval.uate(amount_expr)) amount
from (
select
  p.id, p.name, ptp.amount, p.assemblycost, p.assemblymass,
  level depth,
  1 || sys_connect_by_path(ptp.amount, '*') amount_expr,
  connect_by_isleaf is_leaf
from
  Part_to_Part ptp join Part p 
	on p.id = ptp.id_child
start with 
  ptp.id_parent is NULL
connect by nocycle
  ptp.id_parent = prior ptp.id_child
)
where is_leaf = 1
group by name;



/* koszt i masa calosci */

select 
  sum(eval.uate(amount_expr) * assemblycost) cost,
  sum(eval.uate(amount_expr) * assemblymass) mass
from (
select
  p.id, p.name, ptp.amount, p.assemblycost, p.assemblymass,
  level depth,
  1 || sys_connect_by_path(ptp.amount, '*') amount_expr,
  connect_by_isleaf is_leaf
from
  Part_to_Part ptp join Part p 
	on p.id = ptp.id_child
start with 
  ptp.id_parent is NULL
connect by nocycle
  ptp.id_parent = prior ptp.id_child
);

