CREATE OR ALTER VIEW pricing.repc AS
WITH
code AS (
    SELECT
        ltrim(rtrim(c.a9)) rcode
        ,(ltrim(rtrim(c.a9)) + ' - ') + c.a30 repp
    FROM
        CMSInterfaceIN.lgdat.code c
    WHERE c.a2 = 'MM'
)
SELECT 
     COALESCE(c.rcode,q.qr) rcode
    ,COALESCE(c.repp,q.qr) repp
    ,COALESCE(q.dir,'Other') director
FROM 
    code c
    FULL OUTER JOIN pricing.qrh q ON 
        q.qr = c.rcode