Pasted

    SELECT 
      CLM_LN_PID,
      MIN(DX_SCHEME_CODE) AS DX_SCHEME_CODE,
      rpad(listagg(rpad(dx_code, 7, ' ')) WITHIN GROUP (ORDER BY CLM_LN_DX_NBR), 91, ' ') AS INMED_DIAGNOSES
    FROM (
      SELECT
        cl.CLM_LN_PID,
        dx.DX_CODE,
        dxs.DX_SCHEME_CODE,
        CLD.CLM_LN_DX_NBR
      FROM midr.clm_ln cl
      JOIN midr.CLM_LN_DX cld
        ON cl.CLM_LN_PID = cld.CLM_LN_PID
      JOIN midr.dx dx
        ON cld.DX_PID = dx.DX_PID
      JOIN MIDR.DX_SCHEME DXS
        ON DX.DX_SCHEME_PID = DXS.DX_SCHEME_PID
    )
    GROUP BY CLM_LN_PID