Question

CONNECT_BY_ISLEAF multiply up QTY_PER_ASSEMBLEY

  • 20 August 2020
  • 2 replies
  • 142 views

Badge +2

Using connect pror query below

how would you get the QTY_PRE_ASSEMBLEY multiplying as it goes down each level?

OR

Convert this  EQUATE_THIS coloum I have into as resule i.e *1*8*.022222 becomes 0.16

 

help

 

 

 

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
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


2 replies

Userlevel 7
Badge +24

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  :sweat:

Userlevel 7
Badge +18

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
/

 

Reply