If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > parent child display

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
         )
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On