If you are unable to create a new account, please email bspsoftware@techdata.com

Author Topic: List of all Packages and the path to the package  (Read 511 times)

Offline CraigFSindorf

  • Full Member
  • ***
  • Join Date: Sep 2022
  • Posts: 5
  • Forum Citizenship: +0/-0
List of all Packages and the path to the package
« on: 13 Sep 2022 04:04:28 pm »
I found this to list all packages:
SELECT cmobjnames.cmid,
cmstoreids.storeid,
cmobjects.pcmid,
cmobjnames.name,
cmobjnames.mapdlocaleid,
cmobjnames.localeid,
cmlocales.locale,
cmclasses.name as type
FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
WHERE
cmobjects.cmid = cmobjnames.cmid
AND cmobjects.classid = cmclasses.classid
AND cmobjects.cmid = cmstoreids.cmid
AND cmclasses.name ='package'
AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('en')
order by name

what would i alter to get the path to the packages?

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 800
  • Forum Citizenship: +30/-1
Re: List of all Packages and the path to the package
« Reply #1 on: 13 Sep 2022 08:19:36 pm »
All of it.

You'll need a recursive common table expression.

This site doesn't like code.  Good luck.

Offline Spyhop

  • Full Member
  • ***
  • Join Date: Nov 2013
  • Posts: 21
  • Forum Citizenship: +0/-0
Re: List of all Packages and the path to the package
« Reply #2 on: 23 Nov 2022 03:26:07 pm »
I created a function to get the path info and called it in the query showed
Code: [Select]
CREATE OR REPLACE FUNCTION getpath(p_pcmid IN NUMBER) RETURN NVARCHAR2 IS
    l_parentname NVARCHAR2(100);
    l_pcmid      NUMBER;
BEGIN
    IF p_pcmid = 0 THEN
        RETURN 'root';
    ELSE
        SELECT n.name object_name
              ,o.pcmid
        INTO   l_parentname
              ,l_pcmid
        FROM   rnr_tbls.cmobjnames n
              ,rnr_tbls.cmobjects  o
        WHERE  o.cmid = n.cmid
        AND    o.cmid = p_pcmid
        AND    isdefault = 1;
    END IF;
    RETURN getpath(l_pcmid) || '\' || l_parentname;
EXCEPTION
    WHEN OTHERS THEN
        RETURN SQLERRM;
END getpath;

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 800
  • Forum Citizenship: +30/-1
Re: List of all Packages and the path to the package
« Reply #3 on: 29 Nov 2022 01:16:20 pm »
Since I'm currently working with this...

https://pastebin.com/zPn7rmje