Windchill SQL 篇之 分类特征值相关

1. 分类特征值
   分类的上下级关系，并列出各个分类的特征值信息。

-- 包含了指定特征值的分类
SELECT a.* FROM (
SELECT 
  t.ida2a2,
  t.name AS current_name,
  p.name AS parent_name,
  SYS_CONNECT_BY_PATH(t.name, '/') AS all_parent_names
FROM LWCStructEnumAttTemplate t
LEFT JOIN LWCStructEnumAttTemplate p ON t.ida3a4 = p.ida2a2
START WITH t.ida3a4 = 0
CONNECT BY PRIOR t.ida2a2 = t.ida3a4
ORDER SIBLINGS BY t.ida2a2
) a,LWCIBAATTDEFINITION b
WHERE a.ida2a2 = b.ida3a5
AND b.name = 'xxx'  --分类特征名称

2. 分类特值 指定属性 程序ID的设置的值情况，包括未设置程序ID的属性

-- 分类特值 指定属性 程序ID的设置的值情况，包括未设置的
SELECT a.*,  ba.value FROM (
SELECT a.ida2a2,a.current_name,a.parent_name,a.all_parent_names FROM (SELECT 
  t.ida2a2,
  t.displayName AS current_name,
  p.displayName AS parent_name,
  SYS_CONNECT_BY_PATH(t.displayName, '|') AS all_parent_names
FROM (SELECT b.*,CASE WHEN a.ZH_CN IS NULL THEN  a.VALUE ELSE a.ZH_CN END displayName  FROM LWCStructEnumAttTemplate b 
LEFT JOIN (SELECT a.IDA3b4,a.VALUE , a.ZH_CN FROM LWCLocalizablePropertyValue a , LWCPropertyDefinition c  WHERE a.CLASSNAMEKEYC4  = 'com.ptc.core.lwc.server.LWCStructEnumAttTemplate'
AND c.name = 'displayName'
AND a.ida3a4 = c.IDA2A2 ) a ON b.IDA2A2 = a.IDA3b4 ) t
LEFT JOIN (SELECT b.*,CASE WHEN a.ZH_CN IS NULL THEN  a.VALUE ELSE a.ZH_CN END displayName  FROM LWCStructEnumAttTemplate b 
LEFT JOIN (SELECT a.IDA3b4,a.VALUE , a.ZH_CN FROM LWCLocalizablePropertyValue a , LWCPropertyDefinition c  WHERE a.CLASSNAMEKEYC4  = 'com.ptc.core.lwc.server.LWCStructEnumAttTemplate'
AND c.name = 'displayName'
AND a.ida3a4 = c.IDA2A2 ) a ON b.IDA2A2 = a.IDA3b4 ) p ON t.ida3a4 = p.ida2a2
START WITH t.ida3a4 = 0
CONNECT BY PRIOR t.ida2a2 = t.ida3a4
ORDER SIBLINGS BY t.ida2a2) a
) a
INNER JOIN (SELECT * FROM LWCIBAATTDEFINITION b WHERE b.name = 'xxxx' ) b ON a.ida2a2 = b.ida3a5   ---查询 属性名称为 xxx的
LEFT JOIN LWCGroupMembership c ON b.ida2a2 = c.ida3b4
LEFT JOIN (SELECT aa.ida3b4,ba.ida2a2,aa.value FROM LWCPropertyDefinition ba,LWCPropertyValue aa  WHERE  ba.name = 'dataUtilityId' AND aa.ida3a4 = ba.ida2a2 ) ba ON ba.ida3b4 = c.ida2a2

3. 分类特值 指定属性 设置了程序ID的清单

-- 分类特值 指定属性 设置了程序ID的清单
SELECT a.*,aa.value FROM (
SELECT 
  t.ida2a2,
  t.name AS current_name,
  p.name AS parent_name,
  SYS_CONNECT_BY_PATH(t.name, '/') AS all_parent_names
FROM LWCStructEnumAttTemplate t
LEFT JOIN LWCStructEnumAttTemplate p ON t.ida3a4 = p.ida2a2
START WITH t.ida3a4 = 0
CONNECT BY PRIOR t.ida2a2 = t.ida3a4
ORDER SIBLINGS BY t.ida2a2
) a,LWCIBAATTDEFINITION b,LWCGroupMembership c,
 LWCPropertyValue aa ,
LWCPropertyDefinition ba
WHERE a.ida2a2 = b.ida3a5
AND b.name = 'xxxx'  ---软属性名称
and b.ida2a2 = c.ida3b4
AND aa.ida3a4 = ba.ida2a2
AND ba.name = 'dataUtilityId'
AND aa.ida3b4 = c.ida2a2