CONNECT_BY_ISLEAF multiply up QTY_PER_ASSEMBLEY

hello

I don’t know how to do it with infinite levels.  Perhaps google knows a way…

If you can define an X, where we can assume there will never be more than X levels, then I think it gets a lot easier, even if X is 10.

When i have done something like this before, i have filled out all the gaps in the blank levels with dummy rows with a multiplier of 1.  Then just multiply A*B*C*D*1*1*1*1*1*1

It’s “non-trivial”, that’s for sure  

Hi,

This won’t work on Oracle 11, but if you’re on at least Oracle 12, you can in-line a PL/SQL function in the WITH clause to evaluate the expression. For safety, I added a check on the incoming SQL.

WITH
   FUNCTION eval_(
      input_ IN VARCHAR2) RETURN NUMBER
   IS
      output_ NUMBER;
   BEGIN
      IF NOT REGEXP_LIKE(input_, '^([0-9]|\.|\*)*$') THEN
         error_sys.appl_general(
            'HelloWorld',
            'BADINPUT: Check input_ for bad SQL.');
      END IF;
      EXECUTE IMMEDIATE
         'BEGIN :1 := '
         || LTRIM(input_, '*')
         || '; END;' USING OUT output_;
      RETURN output_;
   END eval_;
SELECT ps.part_no AS parent_part,
       ps.component_part AS child_part,
       LEVEL,
       ps.line_item_no,
       SYS_CONNECT_BY_PATH(ps.component_part, '\') AS structure_path,
       CONNECT_BY_ISLEAF,
       CASE
          WHEN ps.qty_per_assembly < 0 THEN 'BY-PRODUCT'
          ELSE 'COMPONENTS'
       END AS type,
       ps.qty_per_assembly AS qty_per_assembly1,
       SYS_CONNECT_BY_PATH(ps.qty_per_assembly, '*') AS equate_this,
       eval_(SYS_CONNECT_BY_PATH(ps.qty_per_assembly, '*')) AS equated
  FROM prod_structure_head psh
 INNER JOIN part_revision pr
    ON pr.contract      = psh.contract
   AND pr.part_no       = psh.part_no
   AND pr.eng_chg_level = psh.eng_chg_level
 INNER JOIN prod_struct_alternate psa
    ON psa.contract       = psh.contract
   AND psa.part_no        = psh.part_no
   AND psa.eng_chg_level  = psh.eng_chg_level
   AND psa.bom_type_db    = psh.bom_type_db
   AND psa.alternative_no = '*'
 INNER JOIN prod_structure ps
    ON ps.contract       = psa.contract
   AND ps.part_no        = psa.part_no
   AND ps.eng_chg_level  = psa.eng_chg_level
   AND ps.bom_type_db    = psa.bom_type_db
   AND ps.alternative_no = psa.alternative_no
 WHERE psh.eff_phase_out_date IS NULL
   AND psh.bom_type_db = 'M'
   AND pr.part_revision_status_db = 'N'
 START WITH ps.contract = 'SSMD'
        AND ps.part_no = 'H-DCR-TX-4X10-IP33-7035'
CONNECT BY PRIOR ps.component_part = ps.part_no
       AND PRIOR ps.contract = ps.contract
 ORDER BY LEVEL, ps.line_item_no
/