Function backup tabel ke CSV - PostgreSQL

CREATE OR REPLACE FUNCTION "audittable"."backup_logs"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
   
    DECLARE
    filename text :='/home/ukabima/backups/log_backups/loggedtransactions_' || CURRENT_DATE ||'.txt';
   
  BEGIN
     IF ((SELECT count(*) from audittable.tbl_loggedtransactions) >500000) THEN

EXECUTE'COPY audittable.tbl_loggedtransactions TO ''' || filename || '''';

-- COPY audittable.tbl_loggedtransactions TO '/home/ukabima/backups/log_backups/mamanoseng3_loggedtransactions.csv' DELIMITER ',' CSV HEADER ;
       
DELETE FROM audittable.tbl_loggedtransactions;
END IF;
return NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

Looping - SQL Server

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

-----------
 DECLARE @TotalOscars INT
DECLARE @FilmOscars INT
SET @TotalOscars = 0
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OFFilmCumulativeOscars
OPEN FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
UPDATE tblFilm
SETFilmCumulativeOscars = @TotalOscars
WHERE CURRENT OFFilmCursor
FETCH NEXT FROMFilmCursor INTO@FilmOscars
END
CLOSE FilmCursor
DEALLOCATE FilmCursor

Rollback untuk SP - SQL Server

-- CONTOH TABEL HANYA BISA DI ISI DENGAN 5 KARAKTER, MAKA AKAN ROLLBACK JIKA ADA YG DIISI LBH DARI 5 KARAKTER
BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
       
        INSERT INTO [dbo].[TES1]
        VALUES ('ABCDF')
       
        INSERT INTO [dbo].[TES2]
        VALUES ('ABCDF')
       
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION
            END
        END CATCH
    END

Trigger - PostgreSQL

CREATE TABLE account (
id serial primary key,
name text,
debt int,
balance int
);

CREATE TABLE account_audit(
id serial primary key,
db_user text NOT NULL default session_user,
operation text,
account_id int,
account_name text,
debt int,
balance int,
created_at timestamp with time zone default current_timestamp
);

-------------
CREATE FUNCTION account_audit_func()
RETURNS TRIGGER
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
  INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
       (TG_OP, NEW.*);
  RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
   INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
        (TG_OP, NEW.*);
   RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
   INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
        (TG_OP, OLD.*);
   RETURN OLD;
END IF;
END;
$$
LANGUAGE plpgsql;

----
CREATE TRIGGER account_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON account
FOR EACH ROW EXECUTE PROCEDURE account_audit_func();

----
-- ,lastupdate,aksi,hostname,username)
CREATE OR REPLACE FUNCTION zz_mbpr_triger()
  RETURNS trigger AS
$BODY$
BEGIN
 IF NEW.id_entry <> OLD.id_entry THEN
 INSERT INTO zz_mbpr_hist(id,id_entry,bpr,lastupdate,aksi,hostname,username)
 VALUES(old.id, OLD.id_entry,OLD.last_name,old.bpr,now(),'UPDATE', HOST,USER);
 END IF;

-----
CREATE TRIGGER trg_acc_account_number_UPDATE_DELETE
AFTER UPDATE OR DELETE ON public.acc_account_number
FOR EACH ROW EXECUTE PROCEDURE AuditTable.trg_AuditDML();

Tanggal - PostgreSQL

UBAH FORMAT TANGGAL
CONTOH DI DATABASE FORMATNYA YYYY/MM/DD, KITA UBAH JADI DD/MM/YYYY
select a.nomor_register_aplikasi no_rekening, a.nama_nasabah nama_nasabah,
concat(b.jangka_waktu,' ','B') JK, b.plafon,
TO_CHAR(b.tanggal_pencairan,'DD/MM/YYYY') Tgl_Pencairan, b.provisi from adm_permohonan_kredit a,
adm_simulasi_permohonan_kredit b
where a.id=id_kredit
and a.nomor_register_aplikasi='K060000136'

select TO_CHAR(d_entry, 'YYYY-MM-dd') tgl_mulai from mbl_kolektor_setoran_group
where TO_CHAR(d_entry, 'YYYY-MM-dd')=TO_CHAR(now(), 'YYYY-MM-dd')

CONVERT NOW()
select * from adm_kredit_angsuran where batasan_permohonan_kredit=91898  AND tanggal_jatuh_tempo <= (CAST(NOW() AS DATE))

MENAMBAHKAN TANGGAL
(select a.tanggal,b.id, a.kolektibilitas, a.par, a.baki, a.tgk_pokok, a.tgk_bunga, a.tot_hari  from rpt_history_kol_n_ppap_kredit a
join adm_permohonan_kredit b on a.kredit_id=b.id
where a.tanggal = CAST('2019-06-16' as date) + INTERVAL '1 day'
and b.id_bpr=7 and b.id_bpr_cabang=18)

AMBIL TANGGAL AWAL DAN AKHIR BULAN
select
( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) ) :: DATE ) xx,
((date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE ) yyy

AMBIL TAHUN, BULAN DAN TANGGAL AJA
select (date_part('year', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)
select date_part('month', tanggal_beli), date_part('month', now()) from m_aset_inventaris
select (date_part('day', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)

CEK UMUR
select age(timestamp '1995-02-11')

JIKA UPDATE TANGGAL
set jwl_depresiasi=cast(CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',tanggal) as date)

MENDAPATKAN JUMLAH HARI DARI SUATU BULAN
SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');

SELECT DATE_PART('days', DATE_TRUNC('month', NOW()) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL)

GABUNGAN
select
jwl_depresiasi,
DATE_PART('days', DATE_TRUNC('month', jwl_depresiasi) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) jumlah_hari,
DATE_PART('YEAR',jwl_depresiasi) tahun,
DATE_PART('MONTH',jwl_depresiasi) bulan,
CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',31) gabungan
from m_kartu_aset_inventaris
where id_aset_inventaris = 2658 order by depresiasi_bulan_ke

FORMAT
--tanggal_jurnal >= '2019-06-01'
--AND tanggal_jurnal < CAST ( '2019-06-30' AS DATE ) + INTERVAL '1 day'
CAST ( tanggal_jurnal AS DATE ) >= ( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', '01' ) AS DATE ) ) ) :: DATE )
                AND CAST ( tanggal_jurnal AS DATE ) <= ((date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', date_part( 'days', now()) ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE )

BETWEEN
SELECT
apd.id,
apd.nomor_permohonan no_register,
apd.tanggal_permohonan,
apd.nomor_register no_rek,
apd.nama_nasabah,
mkpd.kode_deposito kode,
apd.nominal saldo,
au.sso_id ao
FROM adm_permohonan_deposito apd
join m_kriteria_produk_deposito mkpd on apd.id_produk_deposito=mkpd.id_kriteria_produk_deposito
join app_user au on apd.account_officer=au.id
where apd.id_bpr=3 and apd.id_bpr_cabang=3
and apd.tanggal_permohonan between cast('2019-07-01' as date) and cast('2019-07-31' as date)

System - PostgreSQL

SELECT * FROM pg_catalog.pg_tables

select * FROM pg_stat_activity ORDER BY query_start desc

String/Karakter - PostgreSQL

select * from acc_account_number  where level=1
and left(nomor_akun,1)='2'

-- HILANGKAN KARAKTER DARI BELAKANG
update m_karyawan
set alamat_tinggal=substring(alamat_tinggal,1,(length(alamat_tinggal)-2))


--- LIHAT 2 KARAKTER TERKAHIR
select
nama_nasabah,
substring(nama_nasabah,(length(nama_nasabah)-1),2) akhir
from trx_transaksi xx
where nama_nasabah<>''
limit 100

HASILNYA:
PT BHAKTI MITRA SELARAS            AS
JUMIRAH                    AH
REVRA VENI                NI
SARNI                    NI
RADEN HAJI HAYE TEDJA DJUHAERAH        AH
SUPRIYONO                NO

Row_Number - PostgreSQL

SELECT *, row_number() over (order by count) as idn
FROM temp_urut

COUNT    URUT    IDN
1    9    1
2    9    2
3    9    3
4    9    4
5    9    5

Menambahkan rownumber berdasarkan order  by
INSERT INTO "temporary".tmp_bukubesar
SELECT
ROW_NUMBER () OVER (

ORDER BY
tanggal_jurnal,
d_entry
),
user_id userid,
kodejurnal,
tanggal_jurnal,
d_entry,
debet,
kredit,
saldoakhir,
nomor_rekening,
keterangan,
nomorakun
FROM

Replace - PostgreSQL

update adm_data_jaminan_permohonan_kredit
set keterangan=REPLACE(keterangan,'²','2')
where keterangan like '%²%'

update adm_permohonan_kredit
set nomor_perjajian_kerdit=REPLACE(nomor_perjajian_kerdit,'@','A')
where nomor_perjajian_kerdit like '%@%'

Alter Database - PostgreSQL

ALTER DATABASE bpr RENAME TO bpr_2

Pivot - PostgreSQL

--- convert kolom to row
---data asli
sandipos0101    sandipos0201    sandipos0202 dst
16            5    100         5    4    83    98    10

-- menjadi
sandi    rasio
0101    16
0201    5
0202    100
0203    5
0401    4
0402    83
0501    98
0502    10

SELECT
   unnest(array['0101', '0201', '0202', '0203', '0401', '0402', '0501', '0502']) AS sandi,
   unnest(array[sandipos0101, sandipos0201, sandipos0202,sandipos0203,sandipos0401,sandipos0402,sandipos0501,sandipos0502]) AS rasio
FROM lapbul_rasio_keuangan_triwulanan
ORDER BY sandi
--- convert kolom to row
select
pen2.id_bpr,
pen2.id_bpr_cabang,
sum(case when pen2.pendidikan='S - 3' then COALESCE(kar2.ttl,0) end) s3,
sum(case when pen2.pendidikan='S - 2' then COALESCE(kar2.ttl,0) end) s2,
sum(case when pen2.pendidikan='S - 1' then COALESCE(kar2.ttl,0) end) s1,
sum(case when pen2.pendidikan='Diploma 3' then COALESCE(kar2.ttl,0) end) d3,
sum(case when pen2.pendidikan='Tanpa Gelar' then COALESCE(kar2.ttl,0) end) slta,
sum(case when pen2.pendidikan='Lainnya ' then COALESCE(kar2.ttl,0) end) Lainnya
from (
select pen.id_bpr, cab.id_bpr_cabang, pen.id, pen.pendidikan from (
select
(select id id_bpr from m_bpr where id=4),
id, pendidikan
from m_pendidikan pen where pen.kode_pendidikan in ('03','04','05','06','99','00')) pen
join m_bpr_cabang cab on pen.id_bpr=cab.id_bpr) pen2
left join (select
id_bpr,
id_bpr_cabang,
kode_pendidikan, count(*) ttl
from m_karyawan where id_bpr=4 and status_karyawan='true'and jenis_karyawan='Tetap' and nama_jabatan not like 'ADMIN%'
group by id_bpr,
id_bpr_cabang, kode_pendidikan) kar2 on pen2.id_bpr=kar2.id_bpr and pen2.id_bpr_cabang=kar2.id_bpr_cabang and pen2.id=kar2.kode_pendidikan
group by pen2.id_bpr,
pen2.id_bpr_cabang

Pivot Gabungkan isi Filed - PostgreSQL

AWALNYA:
111583    Agunan Lainnya
111583    Kendaraan Bermotor

HASILNYA:
111583    Agunan Lainnya,Kendaraan Bermotor

select id_permohonan, ARRAY_TO_STRING(ARRAY_AGG(nama), ',') AS nama from (
select adjpk.id_permohonan, --adjpk.nomor_register_jaminan,
mj.nama
from adm_data_jaminan_permohonan_kredit adjpk
join m_jaminan mj on adjpk.id_jaminan=mj.id
--where id_permohonan=102384
where id_permohonan=111583
order by adjpk.id_permohonan) xx
GROUP BY id_permohonan

Pembulatan Angka - PostgreSQL

    round((case when adjpk.nilai_jaminan<>0 then ((tabkredit.baki*100)/(COALESCE(adjpk.nilai_jaminan,0)))
                        else adjpk.nilai_jaminan
                        end),2) ratio

Coalesce - PostgreSQL

select * from m_bpr where COALESCE(sandi_kantor,'0')='0'

Looping Pakai IF - PostgreSQL

DO $$
  Declare rownumber bigint;   
    Declare debet1 bigint;   
        Declare kredit1 bigint;   
        Declare saldoakhir1 bigint;
        declare tipeakun INTEGER;

    cur1 CURSOR for select row_number, debet, kredit, saldoakhir from "temporary".tmp_bukubesar order by row_number;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO rownumber, debet1, kredit1, saldoakhir1;    
    EXIT WHEN NOT FOUND;
   
    select tipe_akun into tipeakun from acc_account_number where nomor_akun='2.330';
   
    if tipeakun in (0,3) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) + debet1 - kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
    if tipeakun in (1,2) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) - debet1 + kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
end loop ;  
CLOSE Cur1; 
END $$;

Looping Pakai Row_Number dan Concat - PostgreSQL

-- UPDATE NO_KTP AGAR 16 DIGIT, ANGKA TERKAHIR AMBIL DARI ROW_NUMBER
DO $$
DECLARE
    urut VARCHAR;
    NOREG VARCHAR;
        CUR1 CURSOR FOR SELECT "NO_REG", row_number() over (order by "NO_REG") as urutan FROM (   
select "NO_REG","NO_KTP",LENGTH("NO_KTP") AS KTP from "NSB_P") A
WHERE KTP<>16;
BEGIN
OPEN CUR1;
LOOP
    FETCH  next from CUR1 INTO NOREG, urut;   
    EXIT WHEN NOT FOUND;
   
        IF LENGTH(urut)=1 THEN
        UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('030100000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
   
    IF LENGTH(urut)=2 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('03010000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
       
        IF LENGTH(urut)=3 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('0301000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
    IF LENGTH(urut)=4 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('030100000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
        IF LENGTH(urut)=5 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('03010000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;


END LOOP;
CLOSE CUR1;
END $$;

Looping Angka Urut - PostgreSQL

DO $$
DECLARE
   iterator INTEGER;  -- we can init at declaration time
     idref integer;
    idsandi varchar;
          cur2 cursor for select id_daftar_ref, sandi from  m_referensi_lapbul_ojk_detail_backup where id is NULL;
BEGIN
    iterator :=19;
    open cur2;
Loop   
    fetch next from cur2 into idref, idsandi;
    EXIT WHEN NOT FOUND;   
 
       UPDATE m_referensi_lapbul_ojk_detail_backup
     set id=iterator
         where id is NULL and id_daftar_ref=idref and sandi=idsandi;
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
         close cur2;
END $$;

-------------------------
DO $$
DECLARE
   iterator INTEGER;  -- we can init at declaration time
BEGIN
    iterator :=1;
   WHILE iterator < 999
   LOOP
     insert into temp_urut (count)
     values (iterator);
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
END $$;


----- update angka urut pakai row_number
with new_numbers as (
   select COUNT,
          row_number() over (order by COUNT) as urutan
   from temp_urut
)
update temp_urut
  set no_urut = nn.urutan
from new_numbers nn
where nn.COUNT = temp_urut.COUNT;

-----
DO $$
DECLARE
   iterator INTEGER;
    urut INTEGER;
     cur1 cursor for select count from temp_urut;
BEGIN
iterator :=1;
OPEN Cur1 ;
Loop   
    FETCH  next from Cur1 INTO urut;   
    EXIT WHEN NOT FOUND;
   
     UPDATE temp_urut
     set no_urut=concat('000',iterator)
         where count=urut;
     iterator := iterator + 1;
  END LOOP;
    CLOSE Cur1; 
END $$;

hasilnya
1    0001
2    0002
3    0003
4    0004
5    0005

Length - PostgreSQL

SELECT * FROM (   
select "NO_REG","NO_KTP",LENGTH("NO_KTP") AS KTP from "NSB_P") A
WHERE KTP<16

Koma - PostgreSQL

update m_kriteria_produk_kredit_biaya
set denda=0.2
where denda=0

--- PADA DATABASE, YANG TAMPIL 0,2
--- KALAU MW SELECT ATAU UPDATE PAKAI 0.2

 --Membuat dua angka di belakang koma (2.50, 3.57 dll)
cast(trunc(cast(aca.suku_bunga as decimal),2) as varchar) AS sukuBunga,

Karakter Enter - PostgreSQL

-- CEK FIELD YANG MENGANDUNG KARAKTER ENTER
select * from m_karyawan where alamat_tinggal LIKE ('%' || chr(10) || '%')

-- HILANGKAN KARAKTER ENTER
update m_karyawan
set alamat_tinggal=substring(alamat_tinggal,1,(length(alamat_tinggal)-2))
where alamat_tinggal LIKE ('%' || chr(10) || '%')

-- HILANGKAN KARAKTER ENTER
update adm_data_jaminan_permohonan_kredit
set alamat_agunan=REPLACE(alamat_agunan,''|| chr(10) ||'','')
where alamat_agunan LIKE ('%' || chr(10) || '%')

Join Update - PostgreSQL

update TEMP_NSB_P_CECK
set "KELURAHAN"=m_kode_pos.kelurahan, "KECAMATAN"=m_kode_pos.kecamatan, "KOTA"=m_kode_pos.Kabupaten
from m_kode_pos
    where TEMP_NSB_P_CECK."KODEPOS"=m_kode_pos.kodepos


UPDATE m_nasabah_perorangan_personal b
SET id_kota_kabupaten_lahir=a.id_kota_kab, id_propinsi_lahir=a.id_propinsi
from temp_data_lahir a
where b.id_nasabah_perorangan=a.id_nasabah_perorangan

Join Like - PostgreSQL

select DISTINCT "NO_REG", "NAMA", "TMP_LAHIR", id_kota_kab,nm_kota_kab, id_propinsi
FROM nsb_all nsb
join m_kota_kab on nm_kota_kab like '%'||nsb."TMP_LAHIR"||'%'
WHERE "TMP_LAHIR"<>''
AND "NO_REG"='060100000468'
ORDER BY "NO_REG", "TMP_LAHIR"

Insert Into - PostgreSQL

insert into m_kode_pos2
(id,id_kelurahan,provinsi,kabupaten,kecamatan,kelurahan,kodepos,id_entry)
select id,id_kelurahan,nm_propinsi,nm_kota_kab,nm_kec,nm_kelurahan,kodepos,'syarif_it' id_entry from temp_kodepos_new

insert into m_kode_pos2
(id,id_kelurahan,provinsi,kabupaten,kecamatan,kelurahan,kodepos,id_entry)
select no,idkelurahan,nm_provinsi,nm_kota_kab,nm_kec,nm_kelurahan,kodepos,'syarif_it' id_entry from temp_kodepos_new2


---- jika tipe data integer dan insertnya tidak ada data maka, di isi dengan null
insert into m_data_direksi_komisaris
values
(1,4,1164,1,'2017-03-03','2020-03-03','S-438/KO.031/2018','2018-04-30','1','2023-12-31',1,'2009-07-25','UPN Veteran',    'Training of trainner solidarity grup landing','2009-08-07','PT UKABIMA',null,null,null,'2','','syarif_it', 'syarif_it', now(), NOW()),
(2,4,1165,2,'2017-03-03','2020-03-03','S-438/KO.031/2018','2018-04-30','1','2023-12-31',1,'2015-09-01','STIE AUB','Satuan pengawas internal','2010-03-02','PT UKABIMA',null,null,null,'1','','syarif_it', 'syarif_it', now(), NOW()),
(3,4,2100,3,'2016-08-23','2019-08-23','S-438/KO.031/2018','2018-04-30','1','2023-12-31',3,'2017-03-03','Universitas Diponegoro','Penyusunan SOP','2014-03-03','PT UKABIMA',1,1,1,'','1','syarif_it', 'syarif_it', now(), NOW());

Index - PostgreSQL

CREATE INDEX adm_kredit_angsuran_tgl_amortisasi_provisi_idx
ON adm_kredit_angsuran (tgl_amortisasi_provisi);

CREATE INDEX adm_permohonan_kredit_tgl_lunas_idx
ON adm_permohonan_kredit (tgl_lunas);

DROP INDEX adm_permohonan_kredit_tgl_lunas_idx;
DROP INDEX adm_kredit_angsuran_tgl_amortisasi_provisi_idx;

--- untuk lap tabungan akrual
CREATE INDEX trx_histori_akrual_tabungan_tanggal_idx
ON trx_histori_akrual_tabungan (tanggal);

Hitung Selisih Angka dengan Setelahnya - PostgreSQL

CREATE TABLE TEMP_ID_IDPK AS
SELECT ID FROM adm_permohonan_kredit ORDER BY ID ASC

CREATE TABLE TEMP_RANGE_IDPK
( AWAL INT, AKHIR INT, SELISIH INT)


DO $$
    Declare ik1 INTEGER;    
    cur1 CURSOR for SELECT * FROM TEMP_ID_IDPK ORDER BY ID ASC;
begin   
OPEN Cur1 ; 
Loop   
    FETCH  next from Cur1 INTO ik1;    
    EXIT WHEN NOT FOUND;
        INSERT INTO TEMP_RANGE_IDPK
        (awal, akhir)
        select ik1, id
        from TEMP_ID_IDPK WHERE ID >(SELECT ID FROM TEMP_ID_IDPK WHERE ID=ik1)
            ORDER BY ID ASC LIMIT 1; 
end loop ;  
CLOSE Cur1; 
END $$;

update TEMP_RANGE_IDPK
set selisih=akhir-awal;


select * from TEMP_RANGE_IDPK
where selisih >100
order by selisih desc

-------------------------
CREATE TABLE TEMP_ID_AKA1 AS
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) ROWNUMBER FROM adm_kredit_angsuran  ORDER BY ID ASC

CREATE TABLE TEMP_ID_AKA2 AS
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) ROWNUMBER FROM adm_kredit_angsuran WHERE ID<>1 ORDER BY ID ASC

SELECT *, sum(id1+1) awalid_kosong, sum(id2-1) akhirid_kosong FROM (
SELECT A.ID ID1, B.ID ID2, SUM(B.ID-A.ID) RANGEID FROM TEMP_ID_AKA1 A, TEMP_ID_AKA2 B
WHERE A.ROWNUMBER=B.ROWNUMBER
GROUP BY A.ROWNUMBER,A.ID, B.ID
ORDER BY A.ID ASC) A
WHERE RANGEID>100
group by id1, id2, rangeid
ORDER BY RANGEID DESC

Grant User - PostgreSQL

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO andri_it

Tanggal Hari Ini - PostgreSQL

(SELECT NOW() :: DATE)

select now();

SELECT LOCALTIME

SELECT CURRENT_TIMESTAMP

Create Table - PostgreSQL

-- CREATE TABLE + ADD CONSTRAINT
CREATE TABLE kategori2
(
   masterkey bigserial,
   nama_kategori character varying(255),
   CONSTRAINT kategori_pk PRIMARY KEY (masterkey)
);

INSERT INTO kategori(masterkey, nama_kategori) VALUES (1, 'Smartphone');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (2, 'Laptop');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (3, 'Notebook');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (4, 'Tablet');

Copy Tabel ke HDD

DO $$
    Declare waktu VARCHAR;
    cur1 CURSOR for SELECT (NOW() :: DATE) as VARCHAR;
       
begin   
OPEN Cur1 ; 
Loop   
    FETCH  next from Cur1 INTO waktu;    
    EXIT WHEN NOT FOUND;
    COPY audittable.tbl_loggedtransactions TO 'D:/loggedtransactions_2.csv' DELIMITER ',' CSV;
end loop ;  
CLOSE Cur1; 
END $$;

Convert To atau Cast - PostgreSQL

--- INTEGER TO VARCHAR
select "NILAI", CAST("NILAI" AS VARCHAR) NILAI2 from "KRD_JMN" limit 100

TO_CHAR(apk.tanggal_permohonan, 'YYYYMMDD') tgl_mulai,

--- merubah float menjadi varchar: di database tampil 2,54 diubah agar 2.54
select
'D01' flag,
nama,
alamat,
jenis,
no_identitas,
status_pemegang_saham,
nominal,
cast(presentase_kepemilikan as varchar)
from m_data_pemilik_bpr

Concat - PostgreSQL

concat(b.jangka_waktu,' ','B')
HASILNYA: 24 B

Cek Field Tabel - PostgreSQL

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'krtkrd';

SELECT DISTINCT "table_name"
FROM information_schema.COLUMNS
WHERE TABLE_NAME like 'brs_%';

Case When - PostgreSQL

select a.nomor_register_aplikasi,
c.cicilan_pokok, c.cicilan_bunga, c.tanggal_jatuh_tempo, c.pem_pokok, c.pem_bunga, c.tgl_pembayaran,
(case when c.status_aplikasi=1 then 'Lunas' else 'Belum Lunas' end) status
from adm_permohonan_kredit a, adm_simulasi_permohonan_kredit b, adm_kredit_angsuran c
where a.id=b.id_kredit
and b.id=c.batasan_permohonan_kredit
and c.tanggal_jatuh_tempo >= '2019-06-01'
and a.id_bpr=9
order by a.nomor_register_aplikasi, c.tanggal_jatuh_tempo

---
select (case when a.overdue=0 then 'current'
when a.overdue between 1 and 30 then 'kol1.1'
when a.overdue between 31 and 60 then 'kol1.2'
when a.overdue between 61 and 90 then 'kol1.3'
when a.overdue between 91 and 120 then 'kol2.1'
when a.overdue between 121 and 150 then 'kol2.2'
when a.overdue between 151 and 180 then 'kol2.3'
when a.overdue between 181 and 270 then 'kol3.1'
when a.overdue between 271 and 365 then 'kol3.2'
when a.overdue between 366 and 545 then 'kol4.1'
when a.overdue between 546 and 720 then 'kol4.2'
when a.overdue >=721 then 'kol4.3'
end) koll
from zz_brs_kk_jan a

---- Penjumlahan yang ada nilai menambahkan dan mengurangkan ----
select userid, id_atmr, sum(case when tmpatmr.plusminus = 0 then (nominal*1)
            when tmpatmr.plusminus = 1 then (nominal*(-1)) end)nominalasli,
sum(ppap_khusus) ppap_khusus,
(sum(case when tmpatmr.plusminus = 0 then (nominal*1)
 when tmpatmr.plusminus = 1 then (nominal*(-1)) end)-sum(ppap_khusus)) nominalminusppap,
ma.persen_bobot,
((sum(case when tmpatmr.plusminus = 0 then (nominal*1)
  when tmpatmr.plusminus = 1 then (nominal*(-1)) end)-sum(ppap_khusus))*ma.persen_bobot/100) nilaiatmr
from "temporary".tmp_proses_atmr_awal tmpatmr
left join m_atmr ma on tmpatmr.id_atmr=ma.id
WHERE userid = user_id
group by userid, id_atmr, ma.persen_bobot
order by id_atmr;

Angka Di Belakang Koma - PostgreSQL

SELECT TRUNC(67.456,1) AS "Truncate upto 1 decimal";  -->    67.4

SELECT TRUNC(67.456) AS "Truncate"; --> 67

Alter Table - PostgreSQL

ALTER TABLE temp_urut
ADD NO_URUT INTEGER;

-- ubah type data field
alter table temp_urut
ALTER COLUMN no_urut TYPE varchar(16);

--- add
alter table zz_mbpr_hist
add COLUMN lastupdate TIMESTAMP,
add COLUMN aksi varchar,
add COLUMN hostname VARCHAR,
add COLUMN username VARCHAR;

-- drop
alter table zz_mbpr_hist
drop COLUMN lastupdate,
drop COLUMN aksi,
drop COLUMN hostname,
drop COLUMN username;

-- add constraint
ALTER TABLE kategori2 ADD CONSTRAINT kategori_pk2 PRIMARY KEY (masterkey);

-- Add PK
ALTER TABLE m_sandi_mapping
    ADD CONSTRAINT fk_acc_account_number FOREIGN KEY (id_akun_ebpr) REFERENCES acc_account_number (id);

Gabungan Quey PostreSQL

-- delete history iduser
delete from "temporary".tmp_bukubesar where iduser = 123;


-- tampung data
insert into "temporary".tmp_bukubesar
select ROW_NUMBER () OVER (ORDER BY tanggal_jurnal, kodejurnal),
123, kodejurnal, tanggal_jurnal, debet, kredit, saldoakhir, nomor_rekening, keterangan from (
SELECT '' kodejurnal, (select CAST ( '2019-01-01' AS DATE ) - INTERVAL '1 day' ) tanggal_jurnal, null debet, null kredit,
    (
    SELECT COALESCE
        ( SUM ( acc_end_of_month_detail.end_balanace ), 0 ) AS saldoeom
    FROM
        acc_end_of_month_detail
        JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
    WHERE
        acc_account_number.nomor_akun LIKE'2.330%'
        AND acc_end_of_month_detail.ending_date = (
        SELECT COALESCE
            ( MAX ( acc_end_of_month.tgl_eom ), '2000-01-01' )
        FROM
            acc_end_of_month
        WHERE
            acc_end_of_month.tgl_eom < '2019-01-01'
            AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
            AND acc_end_of_month.id_bpr = 3
            AND acc_end_of_month.id_bpr_cabang = 3
        )
        AND acc_end_of_month_detail.id_bpr = 3
        AND acc_end_of_month_detail.id_bpr_cabang = 3
        ) + (
    SELECT COALESCE
        (
            SUM (
            CASE
                   
                    WHEN acc_account_number.tipe_akun = 0
                    OR acc_account_number.tipe_akun = 3 THEN
                        ( acc_journal_detail.debet_amount - acc_journal_detail.credit_amount ) ELSE ( acc_journal_detail.credit_amount - acc_journal_detail.debet_amount )
                    END
                    ),
                    0
                )
            FROM
                acc_journal_detail
                JOIN acc_account_number ON acc_journal_detail.id_akun = acc_account_number.
                ID JOIN acc_journal ON acc_journal.ID = acc_journal_detail.id_jurnal
            WHERE
                CAST ( tanggal_jurnal AS DATE ) > (
                SELECT COALESCE
                    ( ( MAX ( acc_end_of_month_detail.ending_date ) ), '2000-01-01' )
                FROM
                    acc_end_of_month_detail
                    JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
                WHERE
                    acc_account_number.nomor_akun LIKE'2.330%'
                    AND acc_end_of_month_detail.ending_date <= '2019-01-01'
                    AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
                    AND acc_end_of_month_detail.id_bpr = 3
                    AND acc_end_of_month_detail.id_bpr_cabang = 3
                )
                AND CAST ( tanggal_jurnal AS DATE ) < '2019-01-01'
                AND acc_account_number.nomor_akun LIKE'2.330%'
                AND acc_journal.id_bpr = 3
            AND acc_journal.id_bpr_cabang = 3
    ) saldoakhir,'-' nomor_rekening,'saldo awal' keterangan
    union
    SELECT
    aj.kode_jurnal,
    aj.tanggal_jurnal,
    ajd.debet_amount,
    ajd.credit_amount,
    0 saldoakhir,
    ajd.nomor_rekening,
    aj.deskripsi
FROM
    acc_journal_detail ajd
    JOIN acc_journal aj ON ajd.id_jurnal = aj."id"
    JOIN acc_account_number acn ON ajd.id_akun = acn."id"
WHERE
    aj.id_bpr = 3
    AND aj.tanggal_jurnal >= '2019-01-01'
    AND aj.tanggal_jurnal < CAST ( '2019-08-02' AS DATE ) + INTERVAL '1 day'
    AND acn.nomor_akun LIKE'2.330%'
    AND aj.id_bpr_cabang = 3
ORDER BY tanggal_jurnal) bukubesar
order by tanggal_jurnal, kodejurnal;



--hitung saldoakhir 
DO $$
  Declare rownumber bigint;   
    Declare debet1 bigint;   
        Declare kredit1 bigint;   
        Declare saldoakhir1 bigint;
        declare tipeakun INTEGER;

    cur1 CURSOR for select row_number, debet, kredit, saldoakhir from "temporary".tmp_bukubesar order by row_number;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO rownumber, debet1, kredit1, saldoakhir1;    
    EXIT WHEN NOT FOUND;
   
    select tipe_akun into tipeakun from acc_account_number where nomor_akun='2.330';
   
    if tipeakun in (0,3) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) + debet1 - kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
    if tipeakun in (1,2) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) - debet1 + kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
end loop ;  
CLOSE Cur1; 
END $$;