[资源分享]t100

 SELECT bmba001 主件料件,bmba009 项次,bmba003 元件料号,yl 汇总用量,lev 层级,bmba011 组成用量,bmba012 主件底数,bmbaud001,bmbaud002,bmbaud003,bmbaud004 FROM (
SELECT DISTINCT bmba001,bmba009,bmba003,eval_number(LTRIM(sys_connect_by_path(bmba011/bmba012,'*'),'*')) AS yl,to_char(sys_connect_by_path(bmba003,'/')),LEVEL lev,
bmba011,bmba012,bmbaud001,bmbaud002,bmbaud003,bmbaud004
FROM bmba_t WHERE bmbaent=88 AND bmba005 <= SYSDATE AND (bmba006 >  SYSDATE OR bmba006 IS NULL ) START WITH bmba001 in ('0221000101AU', '022100900917' )  --为父bom料号
CONNECT BY PRIOR bmba003=bmba001)
ORDER BY lev,bmba001,bmba009

/*--需增加eval_number函数：
CREATE OR REPLACE FUNCTION eval_number( p_In_str varchar)
RETURN NUMBER
IS
  v_RESULT NUMBER;
  v_sql varchar(100);
BEGIN
  -- 拼动态 SQL.
  v_Sql := 'SELECT ' || p_In_str || ' FROM dual';
  -- 执行动态 SQL， 并将结果存储到变量中.
  EXECUTE IMMEDIATE ( v_Sql )   INTO v_RESULT ;
  -- 返回.
  RETURN v_RESULT;
END eval_number;*/