[vchokshi]

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

[andrewst]

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:

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.

[JMartinez]

LAG is very usefull with this kind of stuf also.. a simulation of SQL*Plus BREAK command, can be as follow:

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
         )

[vchokshi]

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