Camstar Records On Commonly Used Sql

本文内容仅做参考并不完全通用，测试环境为OpcenterEXCR2410，其中数据均为演示所用无实际意义

更新日志

时间轴

2025-06-27

  1. 页面初始化
  2. 添加分类：Sql、Query
  3. 添加语句：Sql-递归查询设备组
  4. 添加语句：Sql-行列转换
  5. 添加语句：Sql-查询VP菜单
  6. 添加语句：Sql-查询当前登录用户信息
  7. 添加语句：Query-矩阵查询语句
  8. 添加语句：Query-矩阵精准查询语句

2026-03-29

  1. 更新语句：Sql-查询VP菜单
  2. 添加语句：Sql-查询CLF

Sql

递归查询设备组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH ResourceHierarchy (resourcegroupid, resourcegroupname) AS (
    SELECT rg.resourcegroupid, rg.resourcegroupname
    FROM RESOURCEGROUP rg
    WHERE rg.resourcegroupname = '.Spec-GZ'

    UNION ALL

    SELECT rg.resourcegroupid, rg.resourcegroupname
    FROM RESOURCEGROUPGROUPS rgg
    JOIN ResourceHierarchy rh ON rgg.resourcegroupid = rh.resourcegroupid
    JOIN RESOURCEGROUP rg ON rg.resourcegroupid = rgg.groupsid
)
SELECT DISTINCT r.resourcename AS "Value", r.resourcename AS "DisplayName"
FROM ResourceHierarchy rh
JOIN RESOURCEGROUPENTRIES rge ON rh.resourcegroupid = rge.resourcegroupid
JOIN RESOURCEDEF r ON r.resourceid = rge.entriesid;

行列转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
    COALESCE(Column1, '物料名称') AS 物料名称,
    COALESCE(Column2, '规格') AS 规格,
    COALESCE(Column3, '供应商') AS 供应商
FROM
(
    SELECT 
        dcdb.datacollectiondefname,
        dcd.inspecttype AS type,
        dcd.datacollectiondefrevision,
        dphd.dataname,
        dphd.datavalue,
        hm.txndate,
        hm.txnid,
        hm.comments
    FROM resourcedef rd
    LEFT JOIN datapointhistory dph ON dph.historyid = rd.resourceid
    LEFT JOIN datapointhistorydetail dphd ON dphd.datapointhistoryid = dph.datapointhistoryid
    LEFT JOIN datacollectiondef dcd ON dcd.datacollectiondefid = dph.datacollectiondefid
    LEFT JOIN datacollectiondefbase dcdb ON dcdb.datacollectiondefbaseid = dcd.datacollectiondefbaseid
    LEFT JOIN historymainline hm ON hm.historymainlineid = dph.historymainlineid
    WHERE dcd.inspecttype = N'铜箔'
)
PIVOT
(
    MAX(datavalue)
    FOR dataname IN ('物料名称' AS Column1, '规格' AS Column2, '供应商' AS Column3)
)
ORDER BY datacollectiondefname, txndate;

查询VP菜单

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
pd.portalmenudefinitionname,
pmi.caption,
pdd.portalmenudefinitionname,
pmid.caption,
vp.uivirtualpagename,
pmid.servicename,
pmi.sequence
from PortalMenuDefinition pd
left join PortalMenuItem pmi on pmi.parentid = pd.portalmenudefinitionid
left join PortalMenuDefinition pdd on pdd.portalmenudefinitionid = pmi.menudefinitionid
left join PortalMenuItem pmid on pmid.parentid = pdd.portalmenudefinitionid
left join PortalMenuDefinition pddd on pddd.portalmenudefinitionid =pmid.menudefinitionid
left join PortalMenuItem pmidd on pmidd.parentid = pddd.portalmenudefinitionid
left join UIVirtualPage vp on vp.uivirtualpageid = pmid.virtualpageid
where 1=1 
--and pd.portalmenudefinitionname = 'ahTR_PortalMain'
and vp.uivirtualpagename = 'ahEqpUpdateTraceData_VP'
order by pmi.sequence;

查询当前登录用户信息

1
SELECT * FROM activeusersession;

查询CLF

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select cdo.CDOName,
    em.Name eventName,
    c.CLFName,
    f.Name functionName,
    p.ValueExpression
from CLFDefinition c
left join CLFFunctions cf
    on cf.CLFID = C.CLFID
left join FunctionDefinition f
    on f.FunctionID = cf.FunctionID
left join CLFFunParm p
    on p.CLFFunctionID = cf.CLFFunctionID
left join CLFEventMap em
    on em.CLFEventMapID = p.CLFEventMapID
left join CDODefinition cdo
    on cdo.CDODefID = em.CDODefID
left join FunParmDef fp
    on fp.FunctionParmID = p.FunctionParmID
where upper(p.ValueExpression) like upper('%FA_ET%')
order by CDOName, CLFName, cf.Sequence;

Query

矩阵查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SELECT
  Spec AS Spec
  , Product AS Product
  , Factory AS Factory
  , ProductTypeName AS ProductTypeName
  , ProductFamilyName AS ProductFamilyName
  , MaterialGroup AS MaterialGroup
  , PNUOM AS PNUOM
  , ProductionCheckType AS ProductionCheckType
  , Description AS Description
  , Name AS Name
  , RN AS RN
FROM
  (
    SELECT
      CASE WHEN IR.Wzmspecid IS NULL THEN NULL ELSE SB.SpecName || ':' || S.SpecRevision END AS Spec
      , CASE WHEN IR.Wzmproductid IS NULL THEN NULL ELSE PB.ProductName || ':' || P.ProductRevision END AS Product
      , CASE WHEN IR.WzmFactoryId IS NULL THEN NULL ELSE F.Factoryname END AS Factory
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN NULL ELSE PF.Productfamilyname END AS ProductFamilyName
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN NULL ELSE PT.Producttypename END AS ProductTypeName
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN NULL ELSE U.Uomname END AS PNUOM
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN NULL ELSE MG.Wzmmaterialgroupname END AS MaterialGroup
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN NULL ELSE CT.Wzmproductionchecktypename END AS ProductionCheckType
      , IR.Description AS Description
      , IR.Wzminspectrulematrixname AS Name
      , CAST(ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN IR.Wzmspecid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.WzmFactoryId IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN 0 ELSE 1 END DESC
      ) AS INT) RN
    FROM
      wzminspectrulematrix IR
      LEFT OUTER JOIN Spec S ON IR.Wzmspecid = S.Specid
      LEFT OUTER JOIN Specbase SB ON S.Specbaseid = SB.Specbaseid
      LEFT OUTER JOIN Product P ON IR.Wzmproductid = P.ProductId
      LEFT OUTER JOIN ProductBase PB ON P.ProductBaseId = PB.ProductBaseId
      LEFT OUTER JOIN Productfamily PF ON IR.WzmProductfamilyid = PF.Productfamilyid
      LEFT OUTER JOIN Producttype PT ON IR.Wzmproducttypeid = PT.Producttypeid
      LEFT OUTER JOIN Factory F ON IR.wzmFactoryId = F.FactoryId
      LEFT OUTER JOIN Uom U ON IR.Wzmpnuomid = U.Uomid
      LEFT OUTER JOIN Wzmmaterialgroup MG ON IR.Wzmmaterialgroupid = MG.Wzmmaterialgroupid
      LEFT OUTER JOIN Wzmproductionchecktype CT ON IR.Wzmproductionchecktypeid = CT.Wzmproductionchecktypeid

    WHERE
      (IR.Wzmspecid LIKE ?SpecId OR IR.Wzmspecid IS NULL)
      AND (IR.Wzmproductid LIKE ?ProductId OR IR.Wzmproductid IS NULL)
      AND (IR.Wzmfactoryid LIKE ?FactoryId OR IR.Wzmfactoryid IS NULL)
      AND (IR.Wzmproductfamilyid LIKE ?ProductFamilyId OR IR.Wzmproductfamilyid IS NULL)
      AND (IR.Wzmproducttypeid LIKE ?ProductTypeId OR IR.Wzmproducttypeid IS NULL)
      AND (IR.Wzmpnuomid LIKE ?PNUOMId OR IR.Wzmpnuomid IS NULL)
      AND (IR.Wzmmaterialgroupid LIKE ?MaterialGroupId OR IR.Wzmmaterialgroupid IS NULL)
      AND (IR.Wzmproductionchecktypeid LIKE ?CheckTypeId OR IR.Wzmproductionchecktypeid IS NULL)
  ) DesignerAdvancedQuery
  WHERE
  RN BETWEEN ?STARTROWNUM AND ?STOPROWNUM

矩阵精准查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SELECT
  Spec AS Spec
  , Product AS Product
  , Factory AS Factory
  , ProductTypeName AS ProductTypeName
  , ProductFamilyName AS ProductFamilyName
  , MaterialGroup AS MaterialGroup
  , PNUOM AS PNUOM
  , ProductionCheckType AS ProductionCheckType
  , Description AS Description
  , Name AS Name
  , RN AS RN
FROM
  (
    SELECT
      CASE WHEN IR.Wzmspecid IS NULL THEN NULL ELSE SB.SpecName || ':' || S.SpecRevision END AS Spec
      , CASE WHEN IR.Wzmproductid IS NULL THEN NULL ELSE PB.ProductName || ':' || P.ProductRevision END AS Product
      , CASE WHEN IR.WzmFactoryId IS NULL THEN NULL ELSE F.Factoryname END AS Factory
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN NULL ELSE PF.Productfamilyname END AS ProductFamilyName
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN NULL ELSE PT.Producttypename END AS ProductTypeName
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN NULL ELSE U.Uomname END AS PNUOM
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN NULL ELSE MG.Wzmmaterialgroupname END AS MaterialGroup
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN NULL ELSE CT.Wzmproductionchecktypename END AS ProductionCheckType
      , IR.Description AS Description
      , IR.Wzminspectrulematrixname AS Name
      , CAST(ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN IR.Wzmspecid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.WzmFactoryId IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN 0 ELSE 1 END DESC
      ) AS INT) RN
    FROM
      wzminspectrulematrix IR
      LEFT OUTER JOIN Spec S ON IR.Wzmspecid = S.Specid
      LEFT OUTER JOIN Specbase SB ON S.Specbaseid = SB.Specbaseid
      LEFT OUTER JOIN Product P ON IR.Wzmproductid = P.ProductId
      LEFT OUTER JOIN ProductBase PB ON P.ProductBaseId = PB.ProductBaseId
      LEFT OUTER JOIN Productfamily PF ON IR.WzmProductfamilyid = PF.Productfamilyid
      LEFT OUTER JOIN Producttype PT ON IR.Wzmproducttypeid = PT.Producttypeid
      LEFT OUTER JOIN Factory F ON IR.wzmFactoryId = F.FactoryId
      LEFT OUTER JOIN Uom U ON IR.Wzmpnuomid = U.Uomid
      LEFT OUTER JOIN Wzmmaterialgroup MG ON IR.Wzmmaterialgroupid = MG.Wzmmaterialgroupid
      LEFT OUTER JOIN Wzmproductionchecktype CT ON IR.Wzmproductionchecktypeid = CT.Wzmproductionchecktypeid
      
    WHERE
        ((?SpecId<>'%' and IR.Wzmspecid = ?SpecId) or (?SpecId='%' and IR.Wzmspecid is null))
        AND ((?ProductId<>'%' and IR.Wzmproductid = ?ProductId) or (?ProductId='%' and IR.Wzmproductid is null))
        AND ((?ProductFamilyId<>'%' and IR.Wzmproductfamilyid = ?ProductFamilyId) or (?ProductFamilyId='%' and IR.Wzmproductfamilyid is null))
        AND ((?ProductTypeId<>'%' and IR.Wzmproducttypeid = ?ProductTypeId) or (?ProductTypeId='%' and IR.Wzmproducttypeid is null))
        AND ((?FactoryId<>'%' and IR.Wzmfactoryid = ?FactoryId) or (?FactoryId='%' and IR.Wzmfactoryid is null))
        AND ((?PNUOMId<>'%' and IR.Wzmpnuomid = ?PNUOMId) or (?PNUOMId='%' and IR.Wzmpnuomid is null))
        AND ((?MaterialGroupId<>'%' and IR.Wzmmaterialgroupid = ?MaterialGroupId) or (?MaterialGroupId='%' and IR.Wzmmaterialgroupid is null))
        AND ((?CheckTypeId<>'%' and IR.Wzmproductionchecktypeid = ?CheckTypeId) or (?CheckTypeId='%' and IR.Wzmproductionchecktypeid is null))
  ) DesignerAdvancedQuery
  WHERE
  RN BETWEEN ?STARTROWNUM AND ?STOPROWNUM