Automic Workload Automation

 View Only
  • 1.  SQL - List of objects in a given folder

    Posted Mar 04, 2021 10:14 AM
    Hi Everyone...
    My SQL is not the best, so need some help...
    Does anyone have any example Oracle SQL that lists the objects in a given folder?

    Any help would be appreciated.

    Regards,
    Colin.


  • 2.  RE: SQL - List of objects in a given folder

    Posted Mar 05, 2021 03:11 AM
    Edited by Bernhard Flegel Mar 05, 2021 03:16 AM
    Hello Colin,
    you can use the following sql-query to get all objects in a given folder in client 1:


    with allepfade (Client, Idnr, Name, Typ, Pfad, Link) as (
    --Anchor
    select mit.OH_Client, mit.OH_Idnr, mit.OH_Name, mit.OH_Otype, mit.OH_Name, 0
    from OH mit
    where mit.OH_Otype = 'CLNT'
    and mit.OH_Name = '0001'

    UNION ALL

    --rekursiver Teil
    select mit2.OH_Client, OFS_OH_IDNR_O, mit2.OH_Name, mit2.OH_Otype, Pfad || '\' || substr(Name, instr(Name, '\')+1, 200), OFS_Link
    from allepfade
    inner join OFS on OFS_OH_IDNR_F = IDNR
    inner join OH mit2 on OFS_OH_IDNR_O = mit2.OH_IDNR
    )

    select oh_name,oh_otype, pfad from oh
    left outer join allepfade on OH_NAME = Name
    where
    oh_client = 1
    and oh_deleteflag = 0
    and pfad like '%<your_folder>%'
    order by 2
    ;

    Regards
    Bernhard

    ------------------------------
    Landeshauptstadt München
    ------------------------------



  • 3.  RE: SQL - List of objects in a given folder

    Posted Mar 08, 2021 11:01 AM
    Hello
    if ever you had it in windows MSQL, it would be fine!

    thanks

    Marc Broussard
    ENGIE Ineo


  • 4.  RE: SQL - List of objects in a given folder

    Posted Mar 08, 2021 12:33 PM
    For SQLServer;


    with allepfade (Client, Idnr, Name, Typ, Pfad, Link) as ( -- anchor
    select mit.OH_Client, mit.OH_Idnr, mit.OH_Name, mit.OH_Otype, cast(mit.OH_Name as varchar(200)), cast(0 as smallint)
    from OH mit
    where mit.OH_Otype = 'CLNT'
    and mit.OH_Name = '0100'

    UNION ALL

    --rekursiver Teil
    select mit2.OH_Client, OFS_OH_IDNR_O, mit2.OH_Name, mit2.OH_Otype, cast(Pfad + '\' + substring(Name, charindex(Name, '\')+1, 200) as varchar(200)), OFS_Link
    from allepfade
    inner join OFS on OFS_OH_IDNR_F = IDNR
    inner join OH mit2 on OFS_OH_IDNR_O = mit2.OH_IDNR
    )

    select oh_name,oh_otype, pfad from oh
    left outer join allepfade on OH_NAME = Name
    where
    oh_client = 0100
    and oh_deleteflag = 0
    and pfad like '%<your folder>%'
    order by 2
    ;

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 5.  RE: SQL - List of objects in a given folder

    Posted Mar 10, 2021 06:38 AM
    I cannot seem to get his working for Oracle ..

    What I'm trying to work out is how to get a list of all objects in the <No Folder> folder!


  • 6.  RE: SQL - List of objects in a given folder

    Posted Mar 10, 2021 10:52 AM
    select *
    from oh
    where oh_deleteflag = 0
    and oh_client = 200
    and not exists (select 1 from ofs where ofs_oh_idnr_O = oh_idnr)

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 7.  RE: SQL - List of objects in a given folder

    Posted Mar 11, 2021 03:21 AM
    Thanks Pete.. thats exactly what I need! And actually a lot simpler than I thought!


  • 8.  RE: SQL - List of objects in a given folder

    Posted Dec 07, 2021 05:19 PM
    Hi Pete,  just wondering if this trhread is still active to comment on...  I hope it is.  The code above for the SQLServer is actually showing me everything that has the name in it.  
    For instance:
    and pfad like '%<your folder>%'
    =
    and pfad like '%CABS%'

    shows me everything with CABS in the name.

    I am also trying to write a script that will only show me what is in the folder CABS and not every object in the DB that has that name in it.

    Any and all help is greatly appreciated.  There is no other place on the planet I can go to get this type of help.  :(


  • 9.  RE: SQL - List of objects in a given folder

    Posted Dec 08, 2021 03:07 AM
    Hello Smick,

    try a slash in the path ('/') - see below:

    and pfad like '%/CABS/%'

    Best regards
    Bernhard