Intralink SQL: Searching Based on Attribute Values

Searching based on attribute values can be performed using Oracle SQL, but a little investigation work needs to be done beforehand.

Attributes and their characteristics are contained in the pdm.PDM_CLASSATTR table in the Oracle database. The first step that needs to be done is to identify the 'id' number of the attribute.

The following query shows, for the "file based" attributes, the 'id', the name, and the 'type'. Also included is the CLADEFVALU column which would show any default values for the attributes.

-- show file based attributes
--
select CLAID,CLANAME,CLADEFVALUE,CLATYPE
from pdm.PDM_CLASSATTR
where CLAUSERDEF=1 and CLAFILEBASED=1
order by CLANAME;

     CLAID CLANAME              CLADEFVALU    CLATYPE
---------- -------------------- ---------- ----------
       123 Desc                                     5
       234 Matl                                     5
       345 Mc_Config                                5
       456 Mc_Errors                                2
       567 Mc_Mode                                  5
       678 Model_Check                              5
       789 Weight                                   3

 

The CLATYPE numbers correspond to these data types:

• 2: Integer
• 3: Real
• 4: Boolean
• 5: String

If the 'Desc' attribute is needed, we take the CLAID value and prepend "UDA" to form the column name in the pdm.PDM_PRODUCTITEM_VUDA1 table. In this example, the Desc attribute corresponds to the "UDA123" column.


Putting this into an example, the following query searches for Intralink PIV's with the Desc attribute matching "...some string...".

-- search based on attribute values 
-- 
column piname format a35 
column UDA123 format a40 
column REVVER format a7 

SELECT 
piv.pivid, PINAME, PIVREV'.'PIVVER as REVVER, UDA123 
FROM 
PDM.PDM_PRODUCTITEM pi, 
PDM.PDM_BRANCH br, 
PDM.PDM_PRODUCTITEMVERSION piv, 
pdm.PDM_PRODUCTITEM_VUDA1 vuda 
WHERE 
pi.PIID = br.PIID 
AND br.BRID = piv.BRID 
AND piv.pivid = vuda.pivid 
AND UDA123 like '%some string%' 
;

 


For an exact match, use this as the last condition in the "WHERE" clause instead:

AND UDA123 = 'some string'