Forum Stats

  • 3,817,330 Users
  • 2,259,314 Discussions
  • 7,893,751 Comments

Discussions

how to explode BOM multi level in pl sql

SANPAT
SANPAT Member Posts: 1,087 Silver Badge

Dear Friends

Trying to create the query to explode BOM multi level in pl sql. Following is my code where getting a error message. Please suggest where getting wrong in the Query

ORA-00904: "ECN_V"."ITEM_TYPE": invalid identifier 00904. 00000 - "%s: invalid identifier"

Error at Line: 25 Column: 12

--------------------------------------------

SELECT

  level AS seq_no,

  id,

  ecn_no,

  item_type,

  to_char(parent_item) parent_item,

  ( description ) parent_name,

  prod_uom,

  produced_qty,

  consumed_qty,

  round(consumed_qty / produced_qty, 7) ratio,

  to_char(child_item) child_item,

  child_description,

  rpad('/',(level - 1) * 1, '/')

  || child_item AS l1,

  con_uom

FROM

(SELECT SALESORDER_O_VIEW.ITEM_CODE,SALESORDER_O_VIEW.PENDING_QTY

 FROM ECN_V,SALESORDER_O_VIEW 

 WHERE ECN_V.PARENT_ITEM = SALESORDER_O_VIEW.ITEM_CODE  

  )

  ecn_v

CONNECT BY

  PRIOR child_item = parent_item

START WITH ecn_v.item_type = 'A'

ORDER SIBLINGS BY ecn_v.item_type

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    You already have a thread with this same query, here.

    And just like there, you make it difficult to read by not using code formatting.


    The error is pretty straigh forward. You don't have an object (or have been granted access to an object) named "ECN_V"."ITEM_TYPE". That is ITEM_TYPE in the ECN_V schema.

    Show us the CODE FORMATTED output of:

    select owner,
          object_name,
          object_type
    where upper(object_name) = 'ITEM_TYPE'
    order by owner, 
            object_type
    ;
    


  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    I do not open attachments. Post the code directly in your messages.

    You've been around this forum long enough to know that.

  • user9540031
    user9540031 Member Posts: 174 Silver Badge

    There are two things named ECN_V in your query: the ecn_v table alias for the subquery shadows the inner ECN_V table/view. Which one is ecn_v.item_type supposed to be referencing? Item_type is not in the SELECT-list of the subquery, hence neither the START WITH nor the CONNECT BY clause can see it anymore.)

    My 2 cents.

  • SANPAT
    SANPAT Member Posts: 1,087 Silver Badge

    Dear Friends

    As I am trying to create the tree table joining 2 database tables.

    Table 1 is ECN & TABLE 2 is Salesorder_O_view,

    If using WHERE then its selecting only the those records whose Item type is "A" Where as i need to to select parent as well as child. if using WHERE then the first record is generating correct but after that its a repeating the record in loop . as per the screen shot immediate after the 4th seq_no it should start with 1 seq_no. it should give the breakup based on Item_code and due_date

    Select level as test,

     ECN_VIEW.ITEM_TYPE,ECN_VIEW.SEQ_NO,ECN_VIEW.PARENT_ITEM,ECN_VIEW.CHILD_ITEM,ECN_VIEW.RATIO,ECN_VIEW.L1,

     SALESORDER_O_VIEW.ITEM_CODE,SALESORDER_O_VIEW.PENDING_QTY

     From ECN_VIEW,SALESORDER_O_VIEW

     Start With ECN_VIEW.ITEM_TYPE = 'A'

     Connect By Prior ECN_VIEW.CHILD_ITEM = ECN_VIEW.PARENT_ITEM


    Sanjay