---

with uid_keys as (
    select distinct
        lower(nullif(btrim(rd.uidpensionscheme), '')) as uid_key
    from back_office.report_conformity_pu_bu_detail rd
    where rd.report_conformity_pu_bu_id = 97
      and rd.account in ('38902', '39002')
      and nullif(btrim(rd.uidpensionscheme), '') is not null
)
select
    f.field_name,
    (select count(*) from prod_line.pens_scheme) as scheme_rows,
    m.matched_uid,
    m.example_name,
    m.example_brief
from (
    values ('uid_1c'), ('scheme_id_1c'), ('code_1c')
) f(field_name)
cross join lateral (
    select
        count(distinct k.uid_key) as matched_uid,
        min(ps.name) as example_name,
        min(ps.brief) as example_brief
    from prod_line.pens_scheme ps
    join uid_keys k
        on k.uid_key = lower(nullif(btrim(
            case f.field_name
                when 'uid_1c' then ps.uid_1c
                when 'scheme_id_1c' then ps.scheme_id_1c
                when 'code_1c' then ps.code_1c
            end
        ), ''))
) m;