---

WITH sample AS (
    SELECT rd.*
    FROM back_office.report_conformity_pu_bu_detail rd
    WHERE rd.report_conformity_pu_bu_id = 39
    ORDER BY rd.id
    LIMIT 20
)
SELECT
    rd.id AS detail_id,
    rd.uidpensionscheme AS report_scheme_uid,
    ps.id AS found_scheme_id,
    ps.name AS pension_scheme,
    ps.index_scheme,
    ps.uid_1c AS scheme_uid_1c,
    ps.scheme_id_1c,

    rd.uidsubtypeactivity AS report_activity_uid,
    s.id AS found_strategy_id,
    s.name AS strategy,
    s.uid_1c AS strategy_uid_1c,
    s.id_1c AS strategy_id_1c
FROM sample rd
LEFT JOIN prod_line.pens_scheme ps
    ON NULLIF(LOWER(TRIM(rd.uidpensionscheme)), '') =
       NULLIF(LOWER(TRIM(ps.uid_1c)), '')
    OR NULLIF(LOWER(TRIM(rd.uidpensionscheme)), '') =
       NULLIF(LOWER(TRIM(ps.scheme_id_1c)), '')
LEFT JOIN prod_line.hb_invest_strategy s
    ON NULLIF(LOWER(TRIM(rd.uidsubtypeactivity)), '') =
       NULLIF(LOWER(TRIM(s.uid_1c)), '')
    OR NULLIF(LOWER(TRIM(rd.uidsubtypeactivity)), '') =
       NULLIF(LOWER(TRIM(s.id_1c)), '')
ORDER BY rd.id, ps.id, s.id;