Table name which contain folder information of any object in UC4 Version 10

Can anyone please help with finding table name contain the folder location of any object.

 i.e:

Object Name: JOBS.SQL.JUNK

Path: 007/ADMIN/EXAMPLES

 So I want to fetch above "007/ADMIN/EXAMPLES" path through query.

 Prashant

Comments

  • Christine ChavezChristine Chavez USTeam Automic Posts: 222 Specialist ✭✭✭✭
    Hi @Prashant Batham

    The folder path is stored in the OFS table. You can link the %OH_Idnr% to the OH table.

    Regards,
    Christine
  • Josef ScharlJosef Scharl ViennaTeam Automic Posts: 155 Expert ✭✭✭✭✭
    edited February 27
    I think the following SQL can help. Just replace 1977 with the client you need and JOBS.WIN.DIR.RSET by the object you like to query.

    WITH 
      objectpath (OH_IDNR, OH_NAME, LVL) AS
      (
        SELECT
          OH_IDNR,
          CAST('\'
          || OH_Name AS VARCHAR(400)) AS OH_Name,
          0                           AS LVL
        FROM
          OH
        WHERE
          OH_Client = 1977
        AND OH_Name = 'JOBS.WIN.DIR.RSET'
        UNION ALL
        SELECT
          OFS_OH_IDNR_F,
          SUBSTR(OH.OH_Name,INSTR(OH.OH_Name,'\'),LENGTH(OH.OH_Name))
          || objectpath.OH_Name,
          LVL + 1
        FROM
          objectpath
        INNER JOIN OFS
        ON
          objectpath.OH_Idnr = OFS_OH_Idnr_O
        INNER JOIN OH
        ON
          OFS_OH_IDNR_F = OH.OH_Idnr
      )
    SELECT
      OH_Name AS Objectpath
    FROM
      objectpath
    WHERE
      LVL =
      (
        SELECT
          MAX(LVL)
        FROM
          objectpath
      )
      -1
  • Wolfgang BruecklerWolfgang Brueckler Developer LV3 EXAutomicPosts: 1,261 Innovator ✭✭✭✭✭✭
    THY @Josef Scharl I can also use it very well :-)
    Wolfgang Brückler
    Developer Lv. 3
    AE 11.2/ORA11G/Linux
  • Laura AlbrechtLaura Albrecht Bannockburn, ILPosts: 359 Specialist ✭✭✭✭
    @Josef Scharl  - what db is this for?  Tried using it on Oracle and got an error:

    Unsupported column aliasing.

  • Josef ScharlJosef Scharl ViennaTeam Automic Posts: 155 Expert ✭✭✭✭✭

    Hi @Laura Albrecht

    It’s for Oracle and work for v10, v11 and v12 AE databases.


    I guess you tried to run it in SQL Plus and the error is because of missing “;”.
    I developed it in Oracle SQL Developer, where the “;” is not needed.

  • Laura AlbrechtLaura Albrecht Bannockburn, ILPosts: 359 Specialist ✭✭✭✭
    edited July 13
    @Josef Scharl   I am using Toad.  But this is a V9 system.   :(    Is there no way this info can be extracted from V9?  TIA.
  • Matthias SchelpMatthias Schelp WermelskirchenPosts: 75 Specialist ✭✭✭✭
    @Laura Albrecht

    This may work in V9. But i'm not able to test. Replace <jobname> and <client> with your jobname and client nr and run the statement as the automic database user :

    select '\' || LISTAGG(folder, '\') WITHIN GROUP (order by lvl DESC ) from 
    (
    select SUBSTR(OH.OH_NAME,INSTR(OH.OH_NAME,'\')+1) AS folder, level as lvl from GFD_IS_UC4_O_01.OFS OFS, GFD_IS_UC4_O_01.OH oh where OFS_OH_IDNR_O = oh.OH_IDNR
    start with OFS.OFS_OH_IDNR_O = (
    select
    OFS.OFS_OH_IDNR_O
    from
    ofs
    INNER JOIN OH ON ofs.OFS_OH_IDNR_O = OH.OH_IDNR
    where OH_NAME = '<jobname>' AND
    OH.OH_CLIENT = <client>
    )
    connect by OFS_OH_IDNR_O = prior OFS_OH_IDNR_F
    );

  • Laura AlbrechtLaura Albrecht Bannockburn, ILPosts: 359 Specialist ✭✭✭✭
    Sorry @Matthias Schelp  - When I run that I get this error:

    ORA-00923: FROM keyword not found where expected

    Also, just want to mention that I am looking to use this code within or in conjunction with another query to identify a list of object names that have not executed in x days.  THAT code I have, but am now looking to add the folder location of any objects listed in that report.

    So I'm trying to get a functional query for how to retrieve the folder name, but ultimately, I am looking to use it for a different purpose if that makes sense.  Thanks.
  • Matthias SchelpMatthias Schelp WermelskirchenPosts: 75 Specialist ✭✭✭✭
    Hello Laura,
    sorry i'm not familiar with toad. I use SQLdeveloper. Maybe toad doesn't like linebreaks. Does it work if you put it all in one line? If that doesn't help, try to run the separate select statements one by one.

    Regards, Matthias
Sign In or Register to comment.