www.blackholesurfer.com

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;