I am trying to extract data from HP OVSD to copy to another DB. I can retrieve all field values with their relations to other items except for the custom fields in the tables of unlimited custom fields (e.g. SRV_CFT001). I found the table where this data is stored but I couldn't find back the link with the item (e.g. the service on which the field is used). Can anyone help me find the missing link?
Thanks for your prompt reply. I already had a look at the query in the link you posted, however, in that query there is no real link between a named service and the unlimited custom field. The link which was posted creates a link between the 'entity' service and the custom field. I need to know the value of the custom field for each service.
I am extracting all Services and their attributes. One of the attributes of a service is an custom field (type=unlimited) which is an entity reference to a person ("Technical Service Responsible").
When extracting my services, I don't know how to extract the value behind this custom field. I can perfectly extract all attribute values of the service and even the standard custom field values of all services. However, I don't know how to link the values behind the custom fields in table SRV_CFT_001 to the Service table. Is this any clearer?
Ok, in this example the name of the custom person field is TSR (from "Technical Service Responsible").
ITSM_SRV_CFT001 will have at least two columns, the one is ITSM_SRV_CFT001_OID and the other TSR. Since TSR is a person, this column will hold person's OID so you must join it with ITSM_PERSONS on TSM_SRV_CFT001.TSR=PER.PER_OID
To see what I mean just run the following query:
select (lower (tab.tab_acronym) + '.' + col.col_name) as 'from column', (lower (tab2.tab_acronym) + '.' + col2.col_name)as 'to column' from ifc_columns col inner join ifc_tables tab on col.col_tab_oid=tab.tab_oid inner join ifc_attributes atr on col.col_atr_oid=atr.atr_oid inner join ifc_attrib_types att on att.att_oid=atr.atr_att_oid inner join rep_labels_locale lal on atr.atr_lbl_oid=lal.lal_lbl_oid inner join ifc_entities ent on col.col_ent_ref=ent.ent_oid inner join ifc_entities ent2 on atr.atr_ent_oid=ent2.ent_oid left outer join ifc_columns col2 on col.rel_col_to=col2.col_oid left outer join ifc_tables tab2 on col2.col_tab_oid=tab2.tab_oid where lal.lal_lng_oid=1033 and tab.tab_name like 'itsm_srv_cft%' order by ent.ent_name asc
Create a left outer join between "from column" to "to column" (this must be left outer join in case that a person is not defined for all services).