Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Angry Unanswered: sort help (1 table)

    I'm using SQL/Oracle. I have the required report written, but the sort is driving me crazy. I'm fairly new to SQL, I'm sure this is painfully obvious, so my apologies in advance. I need to sort dept name alpha within next higher dept name alpha, etc.. for 15 levels... Basically, there's a 75-char numeric 'levelkey' field (comprised of 15 5-digit level code fields) associated with the field 'deptname'. Any directional influence appreciated!! Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: sort help (1 table)

    Originally posted by raylove
    I'm using SQL/Oracle. I have the required report written, but the sort is driving me crazy. I'm fairly new to SQL, I'm sure this is painfully obvious, so my apologies in advance. I need to sort dept name alpha within next higher dept name alpha, etc.. for 15 levels... Basically, there's a 75-char numeric 'levelkey' field (comprised of 15 5-digit level code fields) associated with the field 'deptname'. Any directional influence appreciated!! Thanks.

    Im having a little trouble trying to visualize this - can you draw out a quick example of the levels? Or exactly what you think the output should be? Then we can work backwards and figure out how to sort it.

  3. #3
    Join Date
    Jan 2004
    Posts
    6

    Smile

    Okay. I'm basically assimilating access info for users in a purchasing-card system at a college. An example of an end result I'd need: all user-ids (alpha) that are in: IT Group, which is in: Neurology Administration: which is in: Dept of Neurology, which is in: School of Medicine, which is in: Executive VP for Health Affairs, which is in: XXX University. My problem is how to show all those department names - showing users in 'IT Group' is easy, but meaningless, since there are 'IT Groups' in most departments. Sorting alpha within alpha comes in when I'd like to run this report for all departments within the entire university.
    Each dept. 'level code' is as:
    'XXX University" = 00001 00000 00000..etc
    "Exec VP/Health Affairs" = 00001 00035 00000..etc
    "School of Medicine" = 00001 00035 00007 00000..etc...
    But of course, the level codes are just assigned as each new dept is added, they're no help sorting alpha. Well,
    hope this helps. Thanks for your time!

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    How bout trying this?

    It seems as if this data is in a hierarchical fashion.

    For Example:

    IT (level 1)
    ----Security ------ (level 2)
    ----------Infrastructure (level 3)
    ----------Software (level3)

    ----Applications Development--- (level 2)
    ---------- Oracle Applications (level 3)
    ---------- Internet Applications (level 3)


    So you are looking for say all children records of parent record IT Department.

    I would look into hierarchical queries using START WITH / CONNECT BY.

    Ex:

    Code:
    Select a.dept, b.col 1, b.col 2
    from table1 a, table2 b
    start with deptname = 'Dept_you_are_starting_with'
    connect by a.parent_dept_id = prior a.dept_id
    where a.column = b.column
    This should go down the hierarchy starting at whatever you choose. This will assume you have a column stating a departments parent.


    If you dont have this column, or cant figure it out, the other suggestion I have I is to try selecting the 5 groups as substrings in your select statement. You could then order by these groups.

    EX:

    Code:
    select col1, substr(col2, 1,15), substr(col2, 16, 30)...etc to 75
    from table1
    where some_criteria
    order by substr(col2, 1,15), substr(col2, 16,30)...etc
    Would give you all
    00001 groups first (first grouping)
    00002 groups second (first grouping)
    ______00001 groups first (2nd grouping)
    ______00002 groups second (2nd grouping)

    So you would group by each 15 block of codes....not sure if this helps. Try both and let me know.

  5. #5
    Join Date
    Jan 2004
    Posts
    6

    Red face

    Curses! Neither worked. I think where I'm screwed is the table design - the deptname and levelcode fields are in the same table, one-to-one, neither one references or is referenced by any other field. So I thought to do a self-join, but joins dont work with 'connect by'.
    Too bad, looks like 'start with/connect by' is exactly what I needed, with a little research I find that 'connect by' even has a 'level' parameter to indent the levels.
    Sorting/grouping by levelcodes - if only I could say 'sort deptname within levelcode', sigh. I can get the departments grouped together, but not alpha, and impossible to tell which is 'under' which. Oh well. I'll keep slogging along. Thanks for your help so far, if anything else comes to you, I'm listening!

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Yes unfortunately Oracle does not allow joins on its CONNECT BY queries..I believe I did include one on the example by mistake.

    It seems as if the table design is your biggest problem. Usually the easiest way in your DEPT table is to have the dept_id, and a parent_dept_id. So lets say you have 5 levels deep -

    Dept ID Parent_dept_id
    A --- Null
    AB --- A
    ABC --- AB
    ABCD --- ABC
    ABCDE --- ABCD


    I think you get my idea...not sure how many departments you have, and if this would be feasible, but in the long-run it is the most sensible way. You can then easily do a START WITH/CONNECT BY query to run up or down the chain.

    In the meantime I will see if I can think of a way to do it with your current data structure.

  7. #7
    Join Date
    Jan 2004
    Posts
    6

    Talking

    Thanks again! I've just come on to this project, so I'm having to work with what's here. I see why the app admin is so frustrated trying to get reports!

Posting Permissions

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