Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    parent child display

    Hi All,

    Following query gets the data for the parent data (i.e. msi.segment1 and msi.segment3 ) and child data (i.e. msi2.segment1 and msi2.segment3 ) in one row. is there way can i have one seperate row for partent data and subsequent row will be the data for corrosponding parent?

    SELECT
    msi.segment1 assembly_item1,
    msi.segment3 assembly_item2,
    bom.ASSEMBLY_TYPE,
    msi2.segment1 component_item1,
    msi2.segment3 component_item2,
    msi2.bom_item_type,
    bic.component_quantity,
    bic.EFFECTIVITY_DATE,
    bic.IMPLEMENTATION_DATE,
    bic.disable_date ,
    bic.ITEM_NUM,
    bom.BILL_SEQUENCE_ID,
    msi.enabled_flag assembly_flag,
    msi2.enabled_flag component_flag,
    msi.attribute2 assembly_flag1,
    msi2.attribute2 component_flag1,
    MSI.LAST_UPDATE_DATE ASSE_LAST_UPDATE_DATE,
    MSI2.LAST_UPDATE_DATE COMP_LAST_UPDATE_DATE,
    msi.INVENTORY_ITEM_STATUS_CODE assembly_status,
    msi2.INVENTORY_ITEM_STATUS_CODE component_status
    FROM
    mtl_system_items_b msi,
    bom_bill_of_materials bom,
    mtl_system_items_b msi2,
    BOM_INVENTORY_COMPONENTS bic,
    MTL_PARAMETERS PRM

    WHERE
    msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
    And msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
    And bom.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
    And msi2.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
    and msi2.ORGANIZATION_ID = prm.ORGANIZATION_ID
    And msi.BOM_ENABLED_FLAG = 'Y'
    And msi.item_type in ('NETWORK')
    And Msi.attribute2 = 'Y'
    And prm.ORGANIZATION_CODE = 'NTW'
    and msi.inventory_item_status_code in ('B','E','G')


    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    That is not done by SQL itself, but rather by code in the application. In SQL Plus you can use various commands like COLUMN and BREAK to format the report, like this:
    Code:
    SQL> break on deptno
    SQL> select deptno, ename
      2  from emp
      3  order by deptno, ename;
    
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
               MILLER
            20 ADAMS
               FORD
               JONES
               SCOTT
               SMITH
            30 ALLEN
               BLAKE
               JAMES
               MARTIN
               TURNER
               WARD
    
    13 rows selected.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    LAG is very usefull with this kind of stuf also.. a simulation of SQL*Plus BREAK command, can be as follow:

    Code:
    select case when lag(deptno) over(order by deptno) = deptno
                then null
                else deptno
            end deptno,
            ename
       from (
    select deptno, ename
      from emp
     order by deptno, ename
             )

  4. #4
    Join Date
    Nov 2003
    Posts
    33
    is it possible to get the result like below.
    what i mean is i dont want employee name on first line just deptno.


    DEPTNO ENAME
    ---------- ----------
    10
    CLARK
    MILLER
    20
    ADAMS
    FORD
    JONES
    SCOTT
    SMITH
    30
    ALLEN
    BLAKE
    JAMES
    MARTIN
    TURNER
    WARD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •