SELECT

    TRIM(nasabah.NASABAH_ID) AS cif
    ,CASE
        WHEN nasabah.TGL_BUKA = '0000-00-00' THEN '2008-05-03'
        ELSE nasabah.TGL_BUKA
    END AS tanggal_registrasi_cif
    ,TRIM(nasabah.NAMA_NASABAH) AS nama_nasabah
	,CASE
		WHEN nasabah.JENIS_NASABAH='1' THEN 'Individu'
		ELSE 'Non Invidividu'
	END AS jenis_nasabah
    ,TRIM(nasabah.ALAMAT_DOMISILI) AS alamat
    ,TRIM(nasabah.KOTA) AS kota
    ,TRIM(nasabah.NO_HP) AS no_handphone
    ,TRIM(nasabah.EMAIL) AS email
    ,TRIM(nasabah.no_id) AS nik
    ,nasabah.tgllahir AS tanggal_lahir
    ,TRIM(kodegroup1_nasabah.DESKRIPSI_GROUP1) AS agama

    ,TRIM(tabung.NO_REKENING) AS no_rekening
    ,CONCAT('8545',TRIM(tabung.no_va)) AS no_rekening_va
    ,TRIM(kodejenistabungan.KODE_JENIS_TABUNGAN) AS kode_jenis_tabungan
    ,TRIM(kodejenistabungan.DESKRIPSI_JENIS_TABUNGAN) AS jenis_tabungan
    ,tabung.TGL_REGISTRASI AS tgl_registrasi
    ,TRIM(kodegroup1tabung.KODE_GROUP1) AS kode_marketing
    ,TRIM(kodegroup1tabung.DESKRIPSI_GROUP1) AS marketing
    ,tabung.SETORAN_MINIMUM AS setoran_minimum
    ,tabung.SETORAN_PER_BLN AS setoran_per_bulan
    ,tabung.SALDO_NOMINATIF AS saldo_nominatif
    ,tabung.SALDO_BLOKIR AS saldo_blokir
    ,tabung.SALDO_AKHIR AS saldo_akhir
    ,tabung.STATUS_AKTIF AS status_aktif
    ,TRIM(tabung.NO_REK_KREDIT) AS rekening_autodebet
    ,TRIM(tabung.and_or) AS and_or_flag
    ,tabung.JKW AS jangka_waktu
    ,CASE
        WHEN tabung.TGL_JT = '0000-00-00' THEN '9999-12-31'
        ELSE tabung.TGL_JT
    END AS tgl_jatuh_tempo
    ,TRIM(kodegroup2tabung.DESKRIPSI_GROUP2) AS deskripsi_kodegroup2
    ,TRIM(kodegroup3tabung.DESKRIPSI_GROUP3) AS deskripsi_kodegroup3
    ,TRIM(kodegroup4tabung.DESKRIPSI_GROUP4) AS deskripsi_kodegroup4
    ,TRIM(kodegroup5tabung.DESKRIPSI_GROUP5) AS deskripsi_kodegroup5

    ,tabtrans.TABTRANS_ID AS tabtrans_id
    ,tabtrans.TGL_TRANS AS tabtrans_tgl_trans
    ,TRIM(tabtrans.NO_REKENING) AS tabtrans_no_rekening
    ,TRIM(kodetranstabungan.KODE_TRANS) AS tabtrans_kode_trans
    ,TRIM(kodetranstabungan.DESKRIPSI_TRANS) AS tabtrans_deskripsi_trans
    ,tabtrans.SALDO_TRANS AS tabtrans_saldo_trans
    ,TRIM(tabtrans.MY_KODE_TRANS) AS tabtrans_my_kode_trans
    ,TRIM(tabtrans.KUITANSI) AS tabtrans_kuitansi
    ,TRIM(tabtrans.KETERANGAN) AS tabtrans_keterangan

FROM tabtrans
JOIN tabung ON tabtrans.NO_REKENING = tabung.NO_REKENING
LEFT JOIN kodetranstabungan ON tabtrans.KODE_TRANS = kodetranstabungan.KODE_TRANS
LEFT JOIN kodejenistabungan ON tabung.JENIS_TABUNGAN = kodejenistabungan.KODE_JENIS_TABUNGAN
LEFT JOIN nasabah ON tabung.NASABAH_ID = nasabah.nasabah_id
LEFT JOIN kodegroup1tabung ON tabung.KODE_GROUP1 = kodegroup1tabung.KODE_GROUP1
LEFT JOIN kodegroup2tabung ON tabung.KODE_GROUP2 = kodegroup2tabung.KODE_GROUP2
LEFT JOIN kodegroup3tabung ON tabung.KODE_GROUP3 = kodegroup3tabung.KODE_GROUP3
LEFT JOIN kodegroup4tabung ON tabung.KODE_GROUP4 = kodegroup4tabung.KODE_GROUP4
LEFT JOIN kodegroup5tabung ON tabung.KODE_GROUP5 = kodegroup5tabung.KODE_GROUP5
LEFT JOIN kodegroup1_nasabah ON nasabah.NASABAH_GROUP1 = kodegroup1_nasabah.NASABAH_GROUP1
ORDER BY tabtrans.TABTRANS_ID
LIMIT 100
;
