SELECT   a.ID,
              a.NOMOR_PERKARA,
              a.INST_SATKERKD,
              a.ID_INSTANSI,
              a.PENYIDIK,
              NVL2 (
                 a.CREATED_TIME,
                    TO_CHAR (a.TGL_SPDP, 'dd-mm-yyyy')
                 || ' <br>Tgl Entri Data:'
                 || TO_CHAR (a.CREATED_TIME, 'dd-mm-yyyy'),
                 TO_CHAR (a.TGL_SPDP, 'dd-mm-yyyy')
              )
                 AS TGL_SPDP,
              CASE
                 WHEN terdakwa_perperkara.nama_baru IS NOT NULL
                 THEN
                    terdakwa_perperkara.identitas
                 ELSE
                    a.terdakwa
              END
                 AS IDENTITAS,
              (SELECT   WM_CONCAT (FILE_NAME || '#') AS FILE_NAME
                 FROM   PDM_BERKAS_FILE
                WHERE   A.ID = PDM_BERKAS_FILE.ID_PERKARA)
                 AS NAMA_BERKAS_DAKWAAN,
              a.CREATED_TIME
       FROM      pdm_PERKARA a
              INNER JOIN
                 (  SELECT   pdm_perkara.id id_perkara,
                             wm_concat(pdm_tersangka.nama || ','
                                       || TO_CHAR (pdm_tersangka.tgl_lahir,
                                                   'dd-mm-yyyy')
                                       || ','
                                       || DECODE (pdm_tersangka.jkl,
                                                  2, 'Perempuan',
                                                  1, 'Laki-laki',
                                                  '-')
                                       || ','
                                       || pdm_tersangka.alamat
                                       || '#')
                                AS identitas,
                             wm_concat (pdm_tersangka.nama) AS nama_baru
                      FROM      pdm_tersangka
                             RIGHT JOIN
                                pdm_perkara
                             ON pdm_tersangka.id_perkara = pdm_perkara.id
                  GROUP BY   pdm_perkara.id) terdakwa_perperkara
              ON terdakwa_perperkara.id_perkara = a.id
      WHERE   1 = 1
   ORDER BY   a.TGL_SPDP DESC NULLS LAST