Hi friends,
(DB: 9.2.0.7) It's not an university or school problem. It's for the real job/work... This is only an example but with the same problematic of real problem.
I have to create a select , that receives a parameter (padre) which would be put into the where clause.. and that has to show all its "descendants" ...
It is for this structure... and I'm thinking about how to do it.. but I don't kow how!
Please help me with your code if you are able to do it.
It seems to be simple.. but it's not so.
--------------------------
----------
----------
----------
----------
------
Imagine you have:
CREATE TABLE FAMILY
(PADRE VARCHAR2(10),
CHILD_LOW VARCHAR2(10),
CHILD_HIGH VARCHAR2(10)
);
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','01','01');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','AA','AN');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','K0','K50');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('AA','100','199');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('K2','500','600');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('500','VV','VV');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','02','02');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','B0','B9');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','1000','2000');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('1500','MA','MZ');
--------------------------
----------
----------
----------
----------
------
This will show this structure, a "parent" with a range of values (low and high) for his sons and descendants
--------------------
PADRE CHILD_LOW CHILD_HIGH
01 01 01
01 AA AN
01 K0 K50
AA 100 199
K2 500 600
500 VV VV
02 02 02
02 B0 B9
02 1000 2000
1500 MA MZ
--------------------------
----
Te idea is give a "parent" value to the select and... the magic query will return the collection of descendants ranges.
I.e. for '01' it would return:
AA AN
K0 K50
100 199
500 600
VV VV
for 'K2' it would return:
500 600
VV VV
I have the initial query:
select child_low ||'-' ||child_high DESCENDANTS
from (select padre,child_low,child_high
from family where
padre<>child_low)
start with padre = '01'
connect by prior child_low <=padre and prior child_high >= padre;
The from clause where it appears padre<>child_low is to avoid the loops (i.e. when a parent and the child would be '01' in this example)
PROBLEM:
In this query it apears as descendant an interval that it isn't that, MA-MZ. this is because ranges are varchars,and, in this case, when we compare '1500'<'199' .. the problem appears.
Solution is not to change varchar to number in database.. We need to find the way to compare varchars... that's the difficulty
So.. your help would be very apreciated...
Thanks,
Jose L
Start Free Trial
