www.blackholesurfer.com

Code: Select all

WITH b AS (SELECT
    b.*,
    regexp_replace(
      regexp_replace(lower(b.manufacturername),
        '\b(inc|corp(?:oration)?|co|ltd|llc|gmbh|sa|bv)\b', '', 'g'
      ),
      '[^a-z0-9]+', '', 'g'
    ) AS norm_mfg
  FROM BMC_CORE_BMC_BaseElement b
  WHERE b.datasetid = 'BMC.ADDM'
    AND b.NormalizationStatus = '40'
    AND b.Model IS NOT NULL
),
c AS (
  SELECT
    c.description,
    c.company_type,
    regexp_replace(
      regexp_replace(lower(c.description),
        '\b(inc|corp(?:oration)?|co|ltd|llc|gmbh|sa|bv)\b', '', 'g'
      ),
      '[^a-z0-9]+', '', 'g'
    ) AS norm_desc
  FROM COM_Company c
  WHERE c.company_type ILIKE '%Manufacturer%'
)
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 b
JOIN c
  ON b.norm_mfg = c.norm_desc          -- <-- exact match after normalization
GROUP BY
  NormalizationStatus,
  b.classid, b.category, b.type, b.item, b.model,
  b.manufacturername, b.company,
  c.description, c.company_type;