function to select child data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: function to select child data

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    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
    Sankar B. Mandalika

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    Typical way to restrict CONNECT BY query to a given number of generations would be to use WHERE LEVEL <= 3.

  3. #3
    Join Date
    Nov 2003
    Posts
    31
    Thank you, paddres, for your response.
    Sankar B. Mandalika

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width