Writing a report query in report builder for organization contact name

Products

• CA Service Catalog

Issue/Introduction

Out of box, the MDB database table ca_organization has contact_uuid for the organization contact.

How we can write a report query in report builder that return the organization contact name instead of UUID?

Environment

Release : 17.3

Resolution

Uuse left join.

For example

SELECT CONCAT('cnt:',dbo.HEX(cnt.contact_uuid)) as group_hdl,cnt.last_name, CONCAT(ca_organization.abbreviation,' - ',ca_organization.org_name) as org_name',

CONCAT(cnt2.last_name,cnt2.first_name) as org_contact_name

FROM ca_contact cnt

LEFT JOIN ca_organization ON ca_organization.organization_uuid = cnt.organization_uuid

LEFT JOIN ca_contact cnt2 ON ca_organization.contact_uuid = cnt2.contact_uuid

WHERE (cnt.inactive = 0 AND cnt.contact_type = 2308) ORDER BY last_name