Connect by and order by
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Connect by and order by

  1. #1
    Join Date
    Mar 2002
    Posts
    46
    I want to generate a treeview but I have problem with the order of my records.

    How can I order a connect by query? I don't want to order all the records set and loose the hierarchy! I want to order each level and keep the hierarchy.

    I need help!

    Thanks
    DONTBSHY

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    It depend from Oracle version:

    in old versions (peharps before 9.0.1) u can use:

    -- create index on field, that u need sort
    -- use HINT with this index

    select /*+ index_asc (dc_accs pk_rg_acc) */
    RG_ACCOUNT ,
    RG_ROOT ,
    T_ACCOUNT ,
    T_ROOT ,
    T_V_ROOT ,
    T_V_SIGN ,
    CD_EI ,
    substr(lpad(' ',(level-1)*4) || NM_ACCOUNT,1,255) ,
    substr(lpad(' ',(level-1)*4) || EN_ACCOUNT,1,255) ,
    SH_ACCOUNT ,
    cd_ldform,
    LEVEL
    from accs_dictionary
    connect by prior rg_account = rg_root
    start with rg_root is null;
    in 9.0.1 u can use ORDER SIBLINGS BY ..

    select RG_ACCOUNT ,
    RG_ROOT ,
    T_ACCOUNT ,
    T_ROOT ,
    T_V_ROOT ,
    T_V_SIGN ,
    CD_EI ,
    substr(lpad(' ',(level-1)*4) || NM_ACCOUNT,1,255) ,
    substr(lpad(' ',(level-1)*4) || EN_ACCOUNT,1,255) ,
    SH_ACCOUNT ,
    cd_ldform,
    LEVEL
    from accs_dictionary
    connect by prior rg_account = rg_root
    start with rg_root is null
    order siblings by sh_account;



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