Code: Select all
SELECT COUNT(*) AS record_count, CASE WHEN b.normalizationstatus = '10' THEN 'Other'
WHEN b.normalizationstatus = '20' THEN 'Not Normalized'
WHEN b.normalizationstatus = '30' THEN 'Not Applicable for Normalization'
WHEN b.normalizationstatus = '40' THEN 'Normalization Failed'
WHEN b.normalizationstatus = '50' THEN 'Normalized but Not Approved'
WHEN b.normalizationstatus = '60' THEN 'Normalized and Approved'
WHEN b.normalizationstatus = '70' THEN 'Modified after last Normalization' END AS NormalizationStatus,
b.Classid, b.Category, b.Type, b.Item, b.Model, COALESCE(b.ManufacturerName, 'BMC_UNKNOWN') AS ManufacturerName,
CASE WHEN b.Company = 'ACME Co' THEN '- Global -' ELSE b.Company END AS Company,
c.description AS COM_Company, c.company_type AS CompanyType
FROM BMC_CORE_BMC_BaseElement b LEFT JOIN LATERAL ( SELECT c.description, c.company_type
FROM COM_Company c WHERE c.company_type ILIKE '%Manufacturer%'
AND ( regexp_replace(lower(c.description), '(\binc\b|\bcorp(?:oration)?\b|\bco\b|\bltd\b|[^a-z0-9]+)', '', 'g')
LIKE '%' || regexp_replace(lower(b.manufacturername), '(\binc\b|\bcorp(?:oration)?\b|\bco\b|\bltd\b|[^a-z0-9]+)', '', 'g') || '%'
OR regexp_replace(lower(b.manufacturername), '(\binc\b|\bcorp(?:oration)?\b|\bco\b|\bltd\b|[^a-z0-9]+)', '', 'g')
LIKE '%' || regexp_replace(lower(c.description), '(\binc\b|\bcorp(?:oration)?\b|\bco\b|\bltd\b|[^a-z0-9]+)', '', 'g') || '%'
)
ORDER BY length(c.description) DESC -- prefer the more specific match (e.g., "Intel Inc.") LIMIT 1
) c ON TRUE
WHERE b.datasetid = 'BMC.ADDM' AND b.NormalizationStatus = '40' AND b.Model IS NOT NULL
AND (c.description IS NULL OR b.ManufacturerName <> c.description)
GROUP BY NormalizationStatus, b.Classid, b.Category, b.Type, b.Item, b.Model, b.ManufacturerName, b.Company, c.description, c.company_type;