-- #New Version 12.01.2018
-- SQL to get Class Data see also TCSQGetClassdata.pptx
SELECT x.pname ClassNamet,
   pa.pname AttrNameinTcClass,
   pc.PTNAME AttrDatainTbl,
   pa.PDBNAME AttrDataInTblColume
FROM
  (SELECT level sortx,
    connect_by_root(pname) classes,
    pname
   FROM infodba.ppom_class
    -- pname ClassName
    WHERE pname LIKE 'Dataset%' or pname like 'SEP_Des%'
    CONNECT BY nocycle prior pname = psuperclass
  ORDER BY level DESC
  ) x,
  infodba.ppom_attribute pa,
  infodba.ppom_class pc
WHERE x.classes = pc.pname
AND pc.puid     = pa.rdefining_classu
 -- Name of Attribut in TCClass
AND pa.pname like 's4_produc%';

-- #New Version 12.01.2021
-- SQL to get Class Data see also TCSQGetClassdata.pptx
SELECT
   x.pname    Class_Namet,
   pom.pname  pom_AttrName_inTc_Class,
   pc.PTNAME  AttrData_inTbl,
   pom.PDBNAME pom_AttrData_InTbl_Colume
FROM
  (SELECT level sortx,
    connect_by_root(pname) classes,
    pname
   FROM infodba.ppom_class pc
    -- pname ClassName
   -- WHERE pc.pname LIKE '%' or pc.pname like 'Vi%SAP%MatNummer'
   -- WHERE pc.pname LIKE '%form%' or pc.pname like 'A-BG Master'
   WHERE pc.pname like '%Vi_%'
    CONNECT BY nocycle prior pc.pname = pc.psuperclass
  ORDER BY level DESC
  ) x,
  infodba.ppom_attribute pom,
  infodba.ppom_class pc
WHERE x.classes = pc.pname
AND pc.puid     = pom.rdefining_classu AND pom.pname like '%Vi_SAP_NR%';
 -- Name of Attribut in TCClass

-------------- NEW VERSION -----------------------------------------------------
SELECT
   x.pname    Class_Namet,
   pom.pname  pom_AttrName_inTc_Class,
   pc.PTNAME  AttrData_inTbl,
   pom.PDBNAME pom_AttrData_InTbl_Colume
FROM
  (SELECT level sortx,
    connect_by_root(pname) classes, pname
   FROM infodba.ppom_class pc
   -- WHERE pc.pname like '%M%'
    CONNECT BY nocycle prior pc.pname = pc.psuperclass
   ORDER BY level DESC
  ) x,
  infodba.ppom_attribute pom,
  infodba.ppom_class pc
WHERE x.classes = pc.pname
AND pc.puid     = pom.rdefining_classu
-- AND x.pname like 'Vi_MAW%'     -- TC Classen
AND pom.pname like 'MK_%'      -- Attribut Name der TC Classen
AND pc.PTNAME like 'PVI_MAW_%' -- Tabllen Name in dem die Attribut Daten der Classen gespeichert werden
AND pom.PDBNAME like 'pMK%' ;  -- die Spalten der Tabellen in dem die Daten gespeichert werden


