function to select child data
Hi,
I am trying to come up with a function that would select all the child areas underneath
a specific area.
Here is how the data is arranged.
P is the top level area and there are Q, R and S underneath it and under Q there are
A, B, C and under R there are areas D, E and F. Under F there is another area named G.
If somebody runs the function with P as the input area, then Q,R,S,A,B,C,D,E and F should
be selected. However, if they give R as input, then D, E, F and G should be selected. In
other words, for any given area all the child, grand child and great grand child areas
should be selected.
I have come up with the following function which is somewhat similar but does not quite
do what I want.
Any suggestions, samples or examples would be greatly appreciated.
I have appended the structure of the table used and some sample data at the end of
this posting.
Many thanks for your help.
Best Regards,
Sankar.
--get area path based on the dispatcher's area-id
FUNCTION GET_PATH_AREAS_TD_F(p_user_id VARCHAR2, p_area_id NUMBER)
RETURN AREA_PATH
AS
l_areaListTable AREA_PATH := AREA_PATH();
l_cnt int default 0;
BEGIN
FOR x IN (
SELECT area_id FROM area
WHERE area_id in ( select *
from THE (
select cast( APP_PROCESS_PKG.GET_PATH_AREAS_BU_F(p_user_id) as AREA_PATH ) from
dual ) )
START WITH parent_area_id = p_area_id
CONNECT BY parent_area_id = PRIOR area_id
) LOOP
l_cnt := l_cnt + 1;
l_areaListTable.extend;
l_areaListTable(l_areaListTable.count) := x.area_id;
END LOOP;
RETURN l_areaListTable;
END;
Note: APP_PROCESS_PKG is the name of the package that consists of this function
The GET_PATH_AREAS_BU_F function simply gets the area path based on sales rep's user-id
SQL> desc AREA;
Name Null? Type
----------------------------------------- -------- ----------------------------
AREA_ID NOT NULL NUMBER(38)
AREA_NM NOT NULL VARCHAR2(64)
PARENT_AREA_ID NUMBER(38)
AREA_TYP_NM VARCHAR2(64)
CREATE_DT TIMESTAMP(6)
LAST_UPDATE_DT TIMESTAMP(6)
SQL> select AREA_ID,AREA_NM,PARENT_AREA_ID from AREA;
A AreaName ParentAreaId
= ======== ============
1 Worldwide
2 EMEA 1
3 Germany 2
4 All-Germany 3
5 All-Germany SR 4
6 Edu-Germany 3
7 Edu-Germany SR 6
8 Deutsche Bank 3
9 Deutsche Bank SR 8
10 Daimler Chrysler 3
11 Daimler Chrysler SR 10
12 Siemens 3
13 Siemens SR 12
14 SEE 2
15 MENA 14
16 Saudi Arabia 15
17 Saudi Arabia SR 16
18 Egypt 15
19 Egypt SR 18
20 United Arab Emirates 15
21 United Arab Emirates SR 20
22 Algeria 15
23 Algeria SR 22
24 Morocco 15
25 Morocco SR 24
26 Qatar 15
27 Qatar SR 26
28 Kuwait 15
29 Kuwait SR 28
30 Pakistan 15
31 Pakistan SR 30
32 Tunisia 15
33 Tunisia SR 32
34 MENA-Other 15
35 MENA-Other SR 34
36 SSA 14
37 South Africa 36
38 South Africa SR 37
39 Nigeria 36
40 Nigeria SR 39
41 Mauritius 36
42 Mauritius SR 41
43 Zambia 36
44 Zambia SR 43
45 Ethiopia 36
46 Ethiopia SR 45
47 SSA-Other 36
48 SSA-Other SR 47
49 Mid-Europe 14
50 Czech Republic 49
51 Czech Republic SR 50
52 Slovakia 49
53 Slovakia SR 52
54 Poland 49
55 Poland SR 54
56 Hungary 49
57 Hungary SR 56
58 Austria 49
59 Austria SR 58
60 MEDI 14
61 Greece 60
62 Greece SR 61
63 Turkey 60
64 Turkey SR 63
65 Israel 60
66 Israel SR 65
67 Balkans and Adriatic 60
68 Balkans and Adriatic SR 67
69 Kazakhstan 60
70 Kazakhstan SR 69
71 Central Asia 60
72 Central Asia SR 71
73 CIS 14
74 Russia 73
75 Russia SR 74
76 Ukraine 73
77 Ukraine SR 76
78 Moldova 73
79 Moldova SR 78
80 Belarus 73
81 Belarus SR 80
82 UK 2
83 All-UK 82
84 All-UK SR 83
85 CNE 2
86 Sweden 85
87 Sweden SR 86
88 Finland 85
89 Finland SR 88
90 Norway 85
91 Norway SR 90
92 Denmark 85
93 Denmark SR 92
94 Netherlands 85
95 Netherlands SR 94
96 BeLux 85
97 BeLux SR 96
98 Switzerland 85
99 Switzerland SR 98
100 Ericsson 85
101 Ericsson SR 100
102 Estonia 85
103 Estonia SR 102
104 Latvia 85
105 Latvia SR 104
106 Lithuania 85
107 Lithuania SR 106
108 France 2
109 All-France 108
110 All-France SR 109
111 Iberia 2
112 All-Iberia 111
113 Spain 112
114 Spain SR 113
115 Portugal 112
116 Portugal SR 115
117 Italy 2
118 All-Italy 117
119 All-Italy SR 118
