Home » SQL & PL/SQL » SQL & PL/SQL » circular sql ?
circular sql ? [message #330728] Tue, 01 July 2008 01:04 Go to next message
shyam_pn
Messages: 7
Registered: August 2007
Junior Member
CREATE TABLE DTL_TAB
(
  parent_COD        VARCHAR2(12 BYTE)               NOT NULL,
  SERNUM        NUMBER                          NOT NULL,
  LVL1        VARCHAR2(3 BYTE),
  LVL2        VARCHAR2(3 BYTE),
  child_COD     VARCHAR2(12 BYTE),
  VLDFRMDAT     DATE,
  VLDTODAT      DATE
)

INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'YYZ', 1, 'SL', 'CL', 'MRT',  TO_DATE( '06/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '08/31/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'REN', 1, 'SL', 'SL', 'TST',  TO_DATE( '05/06/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '05/07/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'AXN', 1, 'SL', 'CL', 'MRT',  TO_DATE( '05/01/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '05/31/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'SAIRA', 1, 'SL', 'CL', '1ANF',  TO_DATE( '05/06/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '05/07/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'MRT', 1, 'SL', 'CL', 'REN',  TO_DATE( '06/05/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '06/30/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO DTL_TAB ( PARENT_COD, SERNUM, LVL1, LVL2, CHILD_COD, VLDFRMDAT,
VLDTODAT ) VALUES ( 
'XLFRT', 1, 'CL', 'CL', 'AXN',  TO_DATE( '06/16/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  TO_DATE( '06/30/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
COMMIT;





please help me TO solve THE problem.
i have a TABLE LIKE dtl_tab.
i need TO WRITE a QUERY , IN which
i pass a parameter LIKE ,child_cod='MRT'
I need TO retrive ALL parent_cod WITH
child_cod AS 'MRT'. FOR child_cod ='MRT'
i will get parent_cod 'YYZ' AND 'AXN'
again THE same QUERY has TO RETURN THE
ALL parent_cod's having child_cod's
AS 'YYZ' AND 'AXN' (IN this cas 'AXN' has
XLFRT) AND THE resultset should contain
'YYZ','AXN','XLFRT'.It should go like tree.
Re: circular sql ? [message #330734 is a reply to message #330728] Tue, 01 July 2008 01:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi shyam_pn,
thanks for posting the case study. Nice to see that some people try to the posting guidelines.
You will need to search for the term 'Hierarchical Query'. If you use the Orafaq search facility, you will find several examples. Any problems once you have made some attempts,post back with your attempt (and don't forget to include your db version)
Re: circular sql ? [message #330961 is a reply to message #330734] Tue, 01 July 2008 23:16 Go to previous messageGo to next message
shyam_pn
Messages: 7
Registered: August 2007
Junior Member
i dont think this can be achieved by a hierarchical query. i tried with that .so i thought i'll seek help of professionals.
Re: circular sql ? [message #330969 is a reply to message #330728] Tue, 01 July 2008 23:43 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member

Quote:
It should go like tree.



May i watch your tree means desire output?

regards,
Re: circular sql ? [message #330998 is a reply to message #330969] Wed, 02 July 2008 01:47 Go to previous messageGo to next message
shyam_pn
Messages: 7
Registered: August 2007
Junior Member
sorry for creating confusion, i didn't meant the output to be in tree from. when i fetch a parentcod i have to check same code is available as a child for another parent , then i have to get that parentcod also.

[Updated on: Wed, 02 July 2008 01:51]

Report message to a moderator

Re: circular sql ? [message #331016 is a reply to message #330961] Wed, 02 July 2008 02:50 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
i dont think this can be achieved by a hierarchical query. i tried with that .
You went to all of the effort of creating a case study, and yet you decided not to post the query that you already tried but that didn't work? Strange choice. Please post the query that you tried, and as already requested, also post the resultset that you expect.
Previous Topic: Error in Trigger
Next Topic: Help in Pivot Query (Error ORA-00937: not a single-group group function)
Goto Forum:
  


Current Time: Sun Dec 04 02:36:48 CST 2016

Total time taken to generate the page: 0.13972 seconds