Automic Workload Automation

Expand all | Collapse all

SQL - List of objects in a given folder

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

    Posted 03-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 03-05-2021 03:11 AM
    Edited by Bernhard Flegel 03-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 03-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 03-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 03-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 03-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 03-11-2021 03:21 AM
    Thanks Pete.. thats exactly what I need! And actually a lot simpler than I thought!