1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL-20090722

Discussion in 'SQL PL/SQL' started by Pragnesh, Jul 22, 2009.

  1. Pragnesh

    Pragnesh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi:

    I need help to create SQL to get all Object_id and corresponding folder_id for give folderID.

    Output result should include all child object and folders.

    Here is the table structure:

    Table-1
    -----------------------------
    Object_type Object_id Folder_id
    ------------ --------- ----------
    Folder 111
    project 555 111
    project 657 111
    Folder 333 111
    Folder 766 111
    project 345 333
    Folder 876 333
    project 677 766
    project 577 876
    project 988 876



    The result shoul be as below:
    Object_id Folder_id
    -------- ---------
    555 111
    657 111
    345 333
    677 766
    577 876
    988 876

    I will be really appreciated for your help.

    Thank you,
    Prag
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi, your question isn't clear. What is the output based on? You need to explain
     
  3. Pragnesh

    Pragnesh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi Sadik:

    I need to get all object_id anf corresponding folder_if for gievn parent folder_id (ex. folder_id = 111). So, I have to include all sub folders and theris object_id.

    Thank you,
    Pragnesh
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
  5. Pragnesh

    Pragnesh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi:
    I tried below SQL but did not get expected result.

    select distinct object_type, object_id, enclosing_folder, name
    from cz_rp_entries
    where object_type in ('PRJ'/*, 'FLD'*/) and
    object_id <> 0 and
    enclosing_folder <> 0
    start with enclosing_folder = 18456070
    connect by nocycle prior enclosing_folder = object_id
    order by 3,2

    Thank you,
    Pragnesh
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    How can we know what you are trying ot achieve ?

    The fields in the SELECT statement that in your first example are completely different !!!!
    Post the test case first to generate the issue.
     
  7. Pragnesh

    Pragnesh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi Raj:

    There was some isuue with data and I am able to fix it.

    Thank you,
    Pragnesh