Home » SQL & PL/SQL » SQL & PL/SQL » circular sql ?
circular sql ? [message #330728] |
Tue, 01 July 2008 01:04  |
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   |
pablolee
Messages: 2882 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 #330998 is a reply to message #330969] |
Wed, 02 July 2008 01:47   |
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  |
pablolee
Messages: 2882 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.
|
|
|
Goto Forum:
Current Time: Thu Feb 06 10:15:01 CST 2025
|