Looping Dynamic Query - PostgreSQL

for loopawalrr in

select * from "temporary".tmp_proses_rr_awal 

where userid = user_id and status_mapping = 0 order by kode_urut

loop

for loopeod in

select * from "temporary".tmp_tanggal_kolom_rr where userid = user_id

loop

EXECUTE  

'update "temporary".tmp_proses_rr_awal tmp

set '||loopeod.tgl ||' = (select sum('||loopeod.tanggal||')

from rpt_summary_eod eod

left join acc_account_number acn on eod.id_akun=acn.id

where eod.bulan_tahun = '''||tahunbulan||'''

and eod.id_bpr = '||var_id_bpr||' 

and acn.nomor_akun LIKE '''||loopawalrr.nomor_akun || '%'')

where userid = '||user_id||' and status_mapping = 0 and nomor_akun = '''||loopawalrr.nomor_akun||''';';

end loop;

end loop;

Function Yang Outputnya Seperti Tabel - PostgreSQL

 CREATE OR REPLACE FUNCTION "public"."nominatif_deposito"("bpr" int4, "cab" int4, "startdate" date, "enddate" date, "tipe" int4)
  RETURNS TABLE("noreg" text, "norek" text, "tanggal_aplikasi" date, "nama_nasabah" text, "alamat" text, "kodeproduk" text, "suku_bunga" numeric, "carabayar" text, "jatuh_tempo" date, "saldoawal" numeric, "bunga" numeric, "pajak" numeric, "saldoakhir" numeric, "debet" numeric, "kredit" numeric, "ao" text, "namaproduk" text) AS $BODY$

Select Nama Kolom
 from 

Ket: Bagian ini adalah query nya,

$BODY$
  LANGUAGE sql VOLATILE
  COST 100

----------------------

select * from nominatif_deposito(4,15,'2020-08-01','2020-08-31',1);

Looping Untuk Menampilkan Tanggal 1 Sampai Dengan Tanggal AKhir Bulan - PostgreSQL

OUTPUT
userid      tanggal            tglaja
12345 2020-08-01 1
12345 2020-08-02 2
12345 2020-08-03 3
12345 2020-08-04 4
12345 2020-08-05 5
12345 2020-08-06 6
12345 2020-08-07 7
12345 2020-08-08 8
12345 2020-08-09 9
12345 2020-08-10 10
12345 2020-08-11 11
12345 2020-08-12 12
12345 2020-08-13 13
12345 2020-08-14 14
12345 2020-08-15 15
12345 2020-08-16 16
12345 2020-08-17 17
12345 2020-08-18 18
12345 2020-08-19 19
12345 2020-08-20 20
12345 2020-08-21 21
12345 2020-08-22 22
12345 2020-08-23 23
12345 2020-08-24 24
12345 2020-08-25 25
12345 2020-08-26 26
12345 2020-08-27 27
12345 2020-08-28 28
12345 2020-08-29 29
12345 2020-08-30 30
12345 2020-08-31 31

CARANYA:
DO $$
DECLARE
x date;
y integer;
awaltgl date;
akhirtgl date;

begin
delete from "temporary".tmp_list_tanggal_com where userid = 12345;
delete from "temporary".tmp_range_tanggal_com where userid = 12345;

insert into "temporary".tmp_range_tanggal_com 
select 
12345 userid,
( ( date_trunc( 'MONTH', CAST ( concat ( '2020-', '08-', '01' ) AS DATE ) ) ) :: DATE ) tglawal, 
((date_trunc( 'MONTH', CAST ( concat ( '2020-', '08-', '01' ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE ) tglakhir;

select tglawal into awaltgl from "temporary".tmp_range_tanggal_com where userid = 12345; 
select tglakhir into akhirtgl from "temporary".tmp_range_tanggal_com where userid = 12345; 
x :=awaltgl;
y :=1;

loop
insert into "temporary".tmp_list_tanggal_com
VALUES (12345, x, y);
x :=(x + INTERVAL '1 day') :: date;
y := y+1;
exit when x > akhirtgl;
end loop;
end $$;

Function Yang Memangil Functin - PostgreSQL

 CREATE OR REPLACE FUNCTION "public"."fnc_laproa"("user_id" int4, "var_id_bpr" int4, "var_id_bpr_cabang" int4, "tahundate" text, "bulandate" text)

 RETURNS "pg_catalog"."text" AS $BODY$

DECLARE 

 titles TEXT DEFAULT '';

  finalreport   record;

loopneraca "temporary".tmp_proses_roa_awal%ROWTYPE;

BEGIN

---

delete from "temporary".tmp_proses_roa_awal where userid = user_id;

delete from "temporary".tmp_userid;

--create table "temporary".tmp_proses_roa_awal as

insert into "temporary".tmp_proses_roa_awal

(userid, tglawalbulan, tglakhirbulan, totalaset, labasebelumpajak)

select 

user_id userid,

tglawalbulan, (date_trunc('month', tglawalbulan::date) + interval '1 month' - interval '1 day')::date tglakhirbulan,

0 totalaset,

0 labasebelumpajak

from (

select unnest(array[satu, dua, tiga, empat, lima, enam, tujuh, delapan, sembilan, sepuluh, sebelas, duabelas]) tglawalbulan from (

select 

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '11 month'):: date) satu,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '10 month'):: date) dua,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '9 month'):: date) tiga,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '8 month'):: date) empat,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '7 month'):: date) lima,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '6 month'):: date) enam,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '5 month'):: date) tujuh,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '4 month'):: date) delapan,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '3 month'):: date) sembilan,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '2 month'):: date) sepuluh,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '1 month'):: date) sebelas,

( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) duabelas) xxx) tglawalakhir;

for loopneraca in

select * from "temporary".tmp_proses_roa_awal where userid = user_id

loop

--- CARA PEMANGGILAN FUNCTION ---

insert into "temporary".tmp_userid

select fnc_neraca_atmr(loopneraca.userid, var_id_bpr, var_id_bpr_cabang, to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyymm'));

--- CARA PEMANGGILAN FUNCTION ---

update "temporary".tmp_proses_roa_awal 

set totalaset=(select COALESCE(sum(saldoawal+debet-kredit),0) saldo from "temporary".tmp_neraca_atmr where userid = user_id and left(nomor_akun,1)='1')

where userid = user_id and tglakhirbulan=loopneraca.tglakhirbulan;

end loop;

select userid into finalreport from "temporary".tmp_proses_roa_awal where userid = user_id limit 1;

   RETURN finalreport;

END; $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

Insert Menggunakan Variabel - SQL Server

set @newuser='dwi.haryatno' -- user yang baru
set @fromuser='asmadi@gmail.com' -- copy dari user (sama jabatan ( Cnth: admin))
set @Location='10006' -- pilih lokasi user
set @company=1 -- pilih company
set @Aplication=7 --application module
set @domain='@gmail.com'
set @newuser=@newuser+@domain


set @Counter=0;
        Select @UserModulePrivilegeId=max(cast(UserModulePrivilegeId as bigint))+1 from dbo.UserModulePrivilege where SubString(UserModulePrivilegeId,1,8)=CONVERT(varchar(8),getdate(),112);
        if (@UserModulePrivilegeId is null)
           set @UserModulePrivilegeId=CONVERT(varchar(8),getdate(),112)+'000000'
--print @UserModulePrivilegeId


insert into [DBFrameWork].[dbo].[UserCompany]
SELECT distinct @newuser
      ,@company
      ,SUSER_NAME()
      ,getdate()
      ,[AuditActivity]
  FROM [DBFrameWork].[dbo].[UserCompany]
  where UserName=@fromuser


insert into [DBFrameWork].[dbo].[UserModulePrivilege]
SELECT distinct @UserModulePrivilegeId+ROW_NUMBER() OVER (Order by @UserModulePrivilegeId) as ROW
      ,@newuser
      ,[ModuleId]
      ,[PrivilegeId]
      ,SUSER_NAME()
      ,getdate()
      ,[AuditActivity]
  FROM [DBFrameWork].[dbo].[UserModulePrivilege]
  where UserName=@fromuser
  AND PrivilegeId='RP'
  and ModuleId in (select ModuleId from [DBFrameWork].[dbo].[Module] where ApplicationId=@Aplication
  and ModuleId in ('200','201','203','204','205','206','207','208','210'))

insert into [DBFrameWork].[dbo].[UserLocation]
SELECT distinct @newuser
      ,@company
      ,@Location
      ,SUSER_NAME()
      ,getdate()
      ,[AuditActivity]
  FROM [DBFrameWork].[dbo].[UserLocation]
    where UserName=@fromuser

Store Procedure Yang Terdapat Function - Oracle

CREATE OR REPLACE PROCEDURE public."SP_FNC_NERACA_REPORT"
(  
   SP_Date IN NVARCHAR2,
   UserId IN NUMBER,
       branchcode IN NVARCHAR2
)
IS
  DTSaldoAwal DATE;
  NeracaDate DATE;
  OldDate DATE;
  ID_COA1 NUMBER;
  ID_COA2 NUMBER;
  SaldoAwal NUMBER;
  Var_LabaRugiBerjalan NUMBER; --2011-05-28--
  Var_LabaRugiDitahan NUMBER; --2011-05-28--


BEGIN

NeracaDate := TO_DATE(SP_Date,'DD/MM/YYYY');

-- 1. CLEAR OLD DATA --
DELETE temporary.FNC_PL_BS WHERE USR_ID = UserId;


-- 2. GET ALL NERACA COA --
INSERT INTO temporary.FNC_PL_BS(USR_ID, LVL, ID_COA, NAMA_COA, TOP_ID_COA, HDR_DTL, SALDO, LAST_SALDO)
     SELECT  UserId, LEVEL L, A.ID_COA, LPAD('   ',4*(LEVEL-1)) || A.CD_COA ||' - '|| A.NAMA_COA, A.TOP_ID_COA, A.HDR_DTL, 0, 0
     FROM
         ( SELECT ID_COA, CD_COA, NAMA_COA, TOP_ID_COA, HDR_DTL
   FROM DT_COA  START WITH TOP_ID_COA IS NULL AND BS_PL='BS' CONNECT BY PRIOR ID_COA = TOP_ID_COA
) A
START WITH A.TOP_ID_COA IS NULL CONNECT BY PRIOR A.ID_COA = A.TOP_ID_COA;


-- 3. GET ALL JURNAL di bulan NERACA --
     IF branchcode <> '0' THEN
         UPDATE temporary.FNC_PL_BS SET SALDO = NVL(
             (SELECT SALDO
              FROM
                 ( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) SALDO
                   FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
                   WHERE A.TGL_FNC_GJ <= NeracaDate  AND B.USR_ID = UserId  AND A.ID_COA = B.ID_COA  AND A.ID_COA = C.ID_COA AND
                   CD_FNC_GJ LIKE '%-'||branchcode||'%'
                   GROUP BY A.ID_COA
                 ) A
             WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
         WHERE  temporary.FNC_PL_BS.USR_ID = UserId;
ELSE
          UPDATE temporary.FNC_PL_BS SET SALDO = NVL(
                     (SELECT SALDO
                      FROM
                         ( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) SALDO
                           FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
                           WHERE A.TGL_FNC_GJ <= NeracaDate  AND B.USR_ID = UserId  AND A.ID_COA = B.ID_COA  AND A.ID_COA = C.ID_COA
                           GROUP BY A.ID_COA
                         ) A
                     WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
           WHERE  temporary.FNC_PL_BS.USR_ID = UserId;   
     END IF;

-- 4. GET ALL JURNAL di bulan sebelumnya --
OldDate := TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY')-1;
     IF branchcode <> '0' THEN
        UPDATE temporary.FNC_PL_BS SET LAST_SALDO = NVL(
             (SELECT LAST_SALDO
              FROM
                 ( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) LAST_SALDO
                   FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
                   WHERE A.TGL_FNC_GJ <= OldDate  AND B.USR_ID = UserId  AND A.ID_COA = B.ID_COA  AND A.ID_COA = C.ID_COA AND
                   CD_FNC_GJ LIKE '%-'||branchcode||'%'
                   GROUP BY A.ID_COA
                 ) A
             WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
         WHERE  temporary.FNC_PL_BS.USR_ID = UserId;   
     ELSE
         UPDATE temporary.FNC_PL_BS SET LAST_SALDO = NVL(
             (SELECT LAST_SALDO
              FROM
                 ( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) LAST_SALDO
                   FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
                   WHERE A.TGL_FNC_GJ <= OldDate  AND B.USR_ID = UserId  AND A.ID_COA = B.ID_COA  AND A.ID_COA = C.ID_COA
                   GROUP BY A.ID_COA
                 ) A
             WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
         WHERE  temporary.FNC_PL_BS.USR_ID = UserId;
  END IF;



SELECT TO_DATE(PREF_VAL,'DD-MM-YYYY') INTO DTSaldoAwal FROM SY_PREF WHERE PREF_KEY='A1A1';
SELECT PREF_VAL2 INTO ID_COA1 FROM SY_PREF WHERE PREF_KEY='COA_BS1';
SELECT PREF_VAL2 INTO ID_COA2 FROM SY_PREF WHERE PREF_KEY='COA_BS2';


/* -------------------- 1. RUGI/LABA bulan ini -------------------- */
Sp_Fnc_Rugilaba(TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY'), NeracaDate, UserId, branchcode);

-- 1.1 L/R BERJALAN ATAU L/R pada bulan tgl Neraca --
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN SALDO ELSE -SALDO END) INTO Var_LabaRugiBerjalan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);

  UPDATE temporary.FNC_PL_BS SET SALDO = Var_LabaRugiBerjalan WHERE USR_ID = UserId AND ID_COA = ID_COA1;

-- 1.2 L/R DITAHAN ATAU AKUMULASI LABA-RUGI awal Tahun sampai tgl Neraca 
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN LAST_SALDO ELSE -LAST_SALDO END) INTO Var_LabaRugiDitahan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);

UPDATE temporary.FNC_PL_BS SET SALDO = SALDO + Var_LabaRugiDitahan WHERE USR_ID = UserId AND ID_COA = ID_COA2;
 
-- 1.3 Pasiva ditambah LABA-RUGI Berjalan --2011-05-28--
UPDATE temporary.FNC_PL_BS SET SALDO = SALDO + Var_LabaRugiDitahan
WHERE USR_ID = UserId AND ID_COA = (SELECT ID_COA FROM DT_COA WHERE CD_COA='2000');

 
 
/* -------------------- 2. RUGI/LABA bulan sebelumnya -------------------- */
OldDate := TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY') - 1;
Sp_Fnc_Rugilaba(TO_DATE('01/'||TO_CHAR(OldDate,'MM/YYYY'),'DD/MM/YYYY'), OldDate, UserId, branchcode);

-- 2.1 L/R BERJALAN ATAU L/R pada bulan tgl Neraca --
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN SALDO ELSE -SALDO END) INTO Var_LabaRugiBerjalan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);

  UPDATE temporary.FNC_PL_BS SET LAST_SALDO = Var_LabaRugiBerjalan WHERE USR_ID = UserId AND ID_COA = ID_COA1;

-- 1.2 L/R DITAHAN ATAU AKUMULASI LABA-RUGI awal Tahun sampai tgl Neraca 
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN LAST_SALDO ELSE -LAST_SALDO END) INTO Var_LabaRugiDitahan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);

UPDATE temporary.FNC_PL_BS SET LAST_SALDO = LAST_SALDO + Var_LabaRugiDitahan WHERE USR_ID = UserId AND ID_COA = ID_COA2;
 
-- 1.3 Pasiva ditambah LABA-RUGI Berjalan --2011-05-28--
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = LAST_SALDO + Var_LabaRugiDitahan
WHERE USR_ID = UserId AND ID_COA = (SELECT ID_COA FROM DT_COA WHERE CD_COA='2000');

-- 5.1 HITUNG HEADER YG MASIH ADA TOP HEADER-NYA --
FOR REC IN
  (
  SELECT A.TOP_ID_COA, A.ID_COA, A.LVL, B.DBT_CRDT
    FROM temporary.FNC_PL_BS A, DT_COA B
    WHERE A.USR_ID = UserId  AND A.HDR_DTL = 'H' AND A.TOP_ID_COA IS NOT NULL  AND A.ID_COA = B.ID_COA
ORDER BY A.LVL DESC
  )
  LOOP
     FOR REC2 IN
   (
    SELECT SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
     CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
  ELSE
     CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
END
   ) SALDO,  
SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
     CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
  ELSE
     CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
END
   ) LAST_SALDO
        FROM
  ( SELECT A2.ID_COA, A2.LVL, A2.SALDO, A2.LAST_SALDO
    FROM temporary.FNC_PL_BS A2
    WHERE A2.USR_ID = UserId --AND A2.ID_COA <> ID_COA1
    START WITH A2.USR_ID = UserId AND A2.TOP_ID_COA = REC.TOP_ID_COA AND A2.ID_COA = REC.ID_COA CONNECT BY PRIOR A2.ID_COA = A2.TOP_ID_COA
  ) A3, DT_COA B3
WHERE
  A3.LVL = rec.LVL + 1 AND A3.ID_COA = B3.ID_COA
   )
   LOOP
    UPDATE temporary.FNC_PL_BS SET SALDO = REC2.SALDO, LAST_SALDO = REC2.LAST_SALDO  WHERE USR_ID =UserId AND ID_COA = REC.ID_COA;
   END LOOP;
  END LOOP;


  --  5.2 HITUNG TOP HEADER-NYA --
  FOR REC IN
  (
  SELECT A.ID_COA, A.LVL, B.DBT_CRDT
FROM temporary.FNC_PL_BS A, DT_COA B
WHERE A.USR_ID = UserId AND A.HDR_DTL ='H' AND A.TOP_ID_COA IS NULL AND A.ID_COA = B.ID_COA
  )
  LOOP
     FOR REC2 IN
   ( 
       SELECT SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
     CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
  ELSE
     CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
END
  ) SALDO,
  SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
     CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
  ELSE
     CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
END
  ) LAST_SALDO
        FROM
  ( SELECT A2.ID_COA, A2.LVL, A2.SALDO, A2.LAST_SALDO
    FROM temporary.FNC_PL_BS A2
    WHERE A2.USR_ID = UserId
    START WITH A2.USR_ID = UserId AND A2.TOP_ID_COA IS NULL AND A2.ID_COA = REC.ID_COA CONNECT BY PRIOR A2.ID_COA = A2.TOP_ID_COA
  ) A3, DT_COA B3
WHERE
A3.LVL = rec.LVL + 1 AND A3.ID_COA = B3.ID_COA
)
LOOP
UPDATE temporary.FNC_PL_BS SET SALDO = REC2.SALDO, LAST_SALDO = REC2.LAST_SALDO WHERE USR_ID = UserId AND ID_COA = REC.ID_COA;
END LOOP;
  END LOOP;



-- HAPUS YG SALDO BULAN INI DAN YG LALU = NOL
DELETE temporary.FNC_PL_BS WHERE USR_ID = UserId AND NVL(SALDO,0) = 0 AND NVL(LAST_SALDO,0) = 0;
COMMIT;


END Sp_Fnc_Neraca_Report;
/

Menambahkan karakter (LPAD dan RPAD) - PostgreSQL

Digunakan untuk menambahkan karakter tertentu dengan cara terpola:

SELECT rpad(42::text, 4, '0')    -> 4200
SELECT rpad(423::text, 4, '0')  -> 4230
SELECT lpad(42::text, 4, '0')    -> 0042
SELECT lpad(142::text, 4, '0')  -> 0142

Keterangan:
4 = Panjang karakter yang ingin dihasilkan
0 = Karakter yang ditambahkan

Function Yang Terdapat Looping - PostrgreSQL

Contoh penggunaan looping pada function

CREATE OR REPLACE FUNCTION "public"."fnc_tes"("user_id" int4)
RETURNS "pg_catalog"."text" AS $BODY$
DECLARE
titles TEXT DEFAULT '';
finalreport   record;
tesloop      "temporary".tmp_tes%ROWTYPE;

BEGIN
FOR tesloop IN
select * from "temporary".tmp_tes WHERE "row_number"<>4

loop
update "temporary".tmp_tes
set nilai = nilai+2
where row_number=tesloop.row_number;
end loop;

select nilai into finalreport from "temporary".tmp_tes;

RETURN finalreport;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100

Gabungkan Beberapa Baris Menjadi Satu Baris - PostgreSQL

Manipulasi Data
Dari:
Rumah Tinggal
Tanah

Menjadi:
Rumah Tinggal, Tanah

SELECT string_agg ( j.nama, ', ' )
FROM adm_data_jaminan_permohonan_kredit jam
JOIN m_jaminan j ON jam.id_jaminan = j.ID
WHERE jam.id_permohonan = 556

Looping Dalam Looping (3) - PostgreSQL

delete from temporary.tmp_bukubesar_final where iduser = 564;

do $$
DECLARE nomorakun VARCHAR;

DECLARE rownumber BIGINT ;
DECLARE debet1 BIGINT ;
DECLARE kredit1 BIGINT ;
DECLARE saldoakhir1 BIGINT ;
DECLARE tipeakun INTEGER ;

loopakun cursor for select nomor_akun from acc_account_number where "level"=3 and nomor_akun between '1.100.01.001' and '1.270.02.001' order by nomor_akun;

hitungsaldo CURSOR FOR SELECT
    ROW_NUMBER,
    debet,
    kredit,
    saldoakhir
FROM
    temporary.tmp_bukubesar where iduser = 564
ORDER BY
    ROW_NUMBER ;

begin
open loopakun;
loop

FETCH  next from loopakun INTO nomorakun;    
    EXIT WHEN NOT FOUND;

--- tampung data detail tiap akun
delete from temporary.tmp_bukubesar where iduser = 564;
INSERT INTO temporary.tmp_bukubesar SELECT
    ROW_NUMBER () OVER (

        ORDER BY
            tanggal_jurnal,
            kodejurnal
    ),
    564,
    kodejurnal,
    tanggal_jurnal,
    debet,
    kredit,
    saldoakhir,
    nomor_rekening,
    keterangan,
    nomorakun
FROM
    (
        SELECT
            '' kodejurnal,
            (
                SELECT
                    CAST ('2020-04-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 = nomorakun
                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 < '2020-04-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 < '202004'
                        AND acc_end_of_month_detail.id_bpr = 1
                        AND acc_end_of_month_detail.id_bpr_cabang  = 1
                    )
                    AND acc_end_of_month.id_bpr = 1
                    AND acc_end_of_month.id_bpr_cabang  = 1
                )
                             AND acc_end_of_month_detail.id_bpr = 1
                AND acc_end_of_month_detail.id_bpr_cabang  = 1
            ) + (
                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 = nomorakun
                        AND acc_end_of_month_detail.ending_date <= '2020-04-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 < '202004'
                            AND acc_end_of_month_detail.id_bpr = 1
                            AND acc_end_of_month_detail.id_bpr_cabang  = 1
                        )
                        AND acc_end_of_month_detail.id_bpr = 1
                        AND acc_end_of_month_detail.id_bpr_cabang  = 1
                    )
                             AND CAST (tanggal_jurnal AS DATE) < '2020-04-01'
                AND acc_account_number.nomor_akun = nomorakun
                AND acc_journal.id_bpr = 1
                AND acc_journal.id_bpr_cabang  = 1
            ) saldoakhir,
            '-' nomor_rekening,
            'Saldo Awal' keterangan
        UNION ALL
            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 = 1
            AND aj.id_bpr_cabang  = 1
            AND aj.tanggal_jurnal >= '2020-04-01'
            AND aj.tanggal_jurnal < CAST ('2020-04-02' AS DATE) + INTERVAL '1 day'
            AND acn.nomor_akun = nomorakun
            ORDER BY
                tanggal_jurnal
    ) bukubesar
ORDER BY
    tanggal_jurnal,
    kodejurnal;
    --- tampung data detail tiap akun
   
    open hitungsaldo;
    loop

    FETCH NEXT FROM    hitungsaldo INTO rownumber,    debet1,    kredit1,    saldoakhir1 ;
    EXIT WHEN NOT FOUND ;
   
    SELECT
    tipe_akun INTO tipeakun
    FROM    acc_account_number WHERE    nomor_akun = nomorakun;
   
    --- hitung saldo akhir pada setiap detail akun
IF tipeakun IN (0, 3) THEN
    UPDATE temporary.tmp_bukubesar
SET saldoakhir = (
    SELECT
        saldoakhir
    FROM
        temporary.tmp_bukubesar
    WHERE
        ROW_NUMBER < rownumber AND iduser = 564
    ORDER BY
        ROW_NUMBER DESC
    LIMIT 1
) + debet1 - kredit1
WHERE
    ROW_NUMBER = rownumber
AND ROW_NUMBER > 1
AND iduser = 564 ;
END IF ;

IF tipeakun IN (1, 2) THEN
    UPDATE temporary.tmp_bukubesar
SET saldoakhir = (
    SELECT
        saldoakhir
    FROM
        temporary.tmp_bukubesar
    WHERE
        ROW_NUMBER < rownumber AND iduser = 564
    ORDER BY
        ROW_NUMBER DESC
    LIMIT 1
) - debet1 + kredit1
WHERE
    ROW_NUMBER = rownumber
AND ROW_NUMBER > 1
AND iduser = 564 ;
END
IF ;
    --- hitung saldo akhir pada setiap detail akun


END LOOP;
CLOSE hitungsaldo ;

--- insert data bukber tiap akun ke tabel final
insert into temporary.tmp_bukubesar_final
select * from temporary.tmp_bukubesar where iduser = 564 and noakun=nomorakun order by tanggal_jurnal, kodejurnal;

END LOOP;
CLOSE loopakun ;
END $$;


select * from temporary.tmp_bukubesar_final where iduser = 564 order by noakun, tanggal_jurnal, kodejurnal;

Ubah Jam di Linux melalui Terminal - Linux

UBAH JAM DI LINUX

First make sure NTP is not enabled:
sudo timedatectl set-ntp 0
Note: jika 0 maka jam dapat diubah dan jika 1 maka tidak dapat diubah

then set the time:
sudo timedatectl set-time "2017-05-30 18:17:16"

make sure your timezone is correct too, e.g:
sudo timedatectl set-timezone Europe/Paris

Looping Update Tanggal (Day) - PostgreSQL

Jika ingin update data tanggal, tapi hanya harinya saja yang diganti
DO $$
    Declare idaspk int;   
        DECLARE htempo int;
        DECLARE hcair int;
    cur1 CURSOR for select id, haritempo, haricair from dbadata.temp_idsimulasi_bedatanggal;   
 begin 
OPEN Cur1 ; 
Loop     


    FETCH  next from Cur1 INTO idaspk, htempo, hcair ;    
    EXIT WHEN NOT FOUND;
   
    update adm_kredit_angsuran
    set tanggal_jatuh_tempo=cast(CONCAT(DATE_PART('YEAR',tanggal_jatuh_tempo),'-',DATE_PART('MONTH',tanggal_jatuh_tempo),'-',htempo) as date), tanggal_mulai_tempo=cast(CONCAT(DATE_PART('YEAR',tanggal_mulai_tempo),'-',DATE_PART('MONTH',tanggal_mulai_tempo),'-',htempo) as date)
    where batasan_permohonan_kredit=idaspk and angsuran_ke > 1;


end loop ;  
CLOSE Cur1; 
END $$;

Looping dalam looping (2) - PostgreSQL

DO $$
    Declare idmaset int;   
      declare idkrtaset int;   
        Declare bulanke int;   
        DECLARE idasetinv int;   
        Declare jwl date;

    cur1 CURSOR for select ID from m_aset_inventaris
                                    where id_bpr = 2 and id_bpr_cabang = 1;   
   
    cur2 cursor for select id, depresiasi_bulan_ke, id_aset_inventaris, jwl_depresiasi
                                    from m_kartu_aset_inventaris where id_aset_inventaris=idmaset
                                    order by depresiasi_bulan_ke;
   
 begin 
OPEN Cur1 ; 
Loop    

    FETCH  next from Cur1 INTO idmaset;    
    EXIT WHEN NOT FOUND;
   
    open cur2;
        loop
       
        FETCH  next from Cur2 INTO idkrtaset, bulanke, idasetinv, jwl;    
    EXIT WHEN NOT FOUND;


    update m_kartu_aset_inventaris
    set jwl_depresiasi=((select jwl_depresiasi from m_kartu_aset_inventaris where depresiasi_bulan_ke < bulanke  and id_aset_inventaris=idasetinv order by depresiasi_bulan_ke desc limit 1) +(interval '1 MONTH'))
    where id_aset_inventaris=idasetinv and depresiasi_bulan_ke=bulanke and depresiasi_bulan_ke >1;
   
        END LOOP;
  CLOSE cur2;
   
end loop ;  
CLOSE Cur1; 
END $$;

Looping Di Dalam Looping - PostgreSQL

NOTE: CONTOH UNTUK LOOPING YG KEDUA, DENGAN VARIABEL LEBIH DARI 1, JIKA HANYA 1 VARIABEL BISA TANPA SEPERTI INI
UPDATE ANGSURAN YANG TELAH DIBAYAR
 
DO $$
DECLARE norek VARCHAR;
DECLARE bulanke int;
declare byrangsuran record;

DECLARE nofas1 VARCHAR;
DECLARE ke int;
DECLARE tgl date;

cur1 CURSOR for
    SELECT REPLACE(nofas,' ','') nofas, max(angsurke) angske FROM "KRDANGS" where tglbyr is not null group by nofas ORDER BY nofas;

cur2 cursor for
    select REPLACE(nofas,' ','') nofas, angsurke, tglbyr
    from "KRDANGS" WHERE REPLACE(nofas,' ','')=norek
    order by REPLACE(nofas,' ',''), angsurke;
   
begin
open cur1;
loop

    FETCH  next from Cur1 INTO norek, bulanke;    
    EXIT WHEN NOT FOUND;
   
    open cur2;
        loop
       
        FETCH  next from Cur2 INTO nofas1, ke, tgl;    
    EXIT WHEN NOT FOUND;
   
    update "KRTKRD"
    SET "TGL_ANGS"=(case when tgl is not null then tgl
                                            else "TGL_JWL" end),
            "PKANGS"="POKOK", "BNG_ANGS"="BUNGA"               
    WHERE "NO_REK"=norek and "ANGS_KE"=ke
    and "ANGS_KE"<=bulanke;
   
    END LOOP;
  CLOSE cur2;
   
   

END LOOP;
CLOSE cur1;
END $$;

Looping SQL Server (Contoh 2) - SQL Server

CONTOH PENGGUNAAN LOOPING
CASE UNTUK UPDATE TANGGAL  BAYAR YANG NULL PADAHAL ANGSURAN DI ATASNYA ADA TGL PEMBAYARANNYA
declare @norek varchar(15)
declare @tglmax date
declare @maxangs int
declare SalesCursor2 cursor for
                        SELECT xx.NO_REK, xx.TGLMAX, max(yy.ANGS_KE) ANGSMAX FROM (
                        select NO_REK, MAX(TGL_ANGS) TGLMAX
                        from #tmp_krtkrd
                        GROUP BY NO_REK) xx
                        inner join #tmp_krtkrd yy on xx.NO_REK=yy.NO_REK and xx.TGLMAX=yy.TGL_ANGS
                        and xx.TGLMAX is not null
                        group by xx.NO_REK, xx.TGLMAX
                        order by xx.NO_REK;

open SalesCursor2
fetch next from SalesCursor2 into @norek, @tglmax, @maxangs
WHILE @@FETCH_STATUS = 0
    begin
    update #tmp_krtkrd
    set TGL_ANGS=TGL_JWL
    where NO_REK=@norek and ANGS_KE < @maxangs and TGL_ANGS is null;

    FETCH NEXT FROM SalesCursor2 INTO @norek, @tglmax, @maxangs
END  
CLOSE SalesCursor2  
DEALLOCATE SalesCursor2;

Looping di dalam Looping - PostgreSQL

--- generate kartu provisi
DO $$
        DECLARE bulan_ke INTEGER; 
        declare hkrtpro record;
        declare noreg VARCHAR;
        declare norek VARCHAR;
        declare cur2 cursor for select cno, nofas from migrasi.tmp_krtpro order by cno;
 begin 

 open cur2;
 loop

     fetch next from cur2 into noreg, norek;
    EXIT WHEN NOT FOUND;
                   
            select cno,nofas, jk_wktawal, tgl_awal, jt_tempo, provisi
            into hkrtpro
            from migrasi.tmp_krtpro where cno=noreg and nofas=norek;   
           
             bulan_ke :=1;

      loop               
            -- insert data kartunya
            insert into migrasi."KRTPRO"
            ("NO_REG",  "NO_REK",  "KODE" ,  "JANGKA",  "KE",  "TGL_DROP",  "TGL_JWL",  "ACR_PROV" ,  "ACR_ADM",  "PROVISI",  "ADM" ,  "PROSES",  "TGL_PROSES",  "USRUPD")       
            select
            cno, nofas,
            '', hkrtpro.jk_wktawal, bulan_ke, hkrtpro.tgl_awal,(hkrtpro.tgl_awal + interval '1 MONTH'),  round((hkrtpro.provisi/hkrtpro.jk_wktawal),0), 0, hkrtpro.provisi, 0, false, null, null
            from migrasi.tmp_krtpro
            where cno=hkrtpro.cno and nofas=hkrtpro.nofas;

            bulan_ke := bulan_ke + 1;
            exit when bulan_ke > hkrtpro.jk_wktawal;
            end loop;
           
            -- update jika ada total provisi kartu beda dengan headernya
             update migrasi."KRTPRO"
 set  "ACR_PROV" = "ACR_PROV" + ((select provisi from migrasi.tmp_krtpro where nofas=norek) - (select sum("ACR_PROV") from migrasi."KRTPRO" where "NO_REK"=norek))
 where "NO_REK" = norek and "KE" = (select "KE" from migrasi."KRTPRO" where "NO_REK"= norek order by "KE" desc limit 1);
           
 end loop ;

close cur2;
END $$;

Looping Angka Urut - SQL Server

if OBJECT_ID('tempdb.dbo.#kartudeposito', 'U') is not null
drop table #kartudeposito;

create table #kartudeposito (NO_REK varchar(15), SIKLUS int, ANGS_KE int, BUNGA numeric, PAJAK numeric, TGL_JWL date, POKOK numeric, RECID int);


declare @norek varchar(15)
declare @jangka int
declare @jangka2 int
declare @Counter int
declare @tgl datetime

-- untuk looping noreknya
declare SalesCursor cursor for
    SELECT YY.no_rekening, yy.tgl_jttempo
        FROM DEPOSITO_BUNGA YY
        JOIN #TEMP_DEP XX ON YY.no_rekening=XX.NO_REK and xx.freq_pemb=xx.JANGKA

SET @Counter = 1

open SalesCursor
fetch next from SalesCursor into @norek,@tgl
WHILE @@FETCH_STATUS = 0
    begin

        -- baru untuk recid nya (angka urut)
        insert into #kartudeposito
        SELECT YY.no_rekening, 1 siklus,
        null angsuran_ke,
        yy.nilai, (yy.nilai*20/100) pajak, yy.tgl_jttempo, xx.SALDO, @Counter
        FROM DEPOSITO_BUNGA YY
        JOIN #TEMP_DEP XX ON YY.no_rekening=XX.NO_REK and xx.freq_pemb=xx.JANGKA
        and yy.no_rekening=@norek and yy.tgl_jttempo=@tgl

        SET @Counter = @Counter + 1
       
   
FETCH NEXT FROM SalesCursor INTO @norek,@tgl
END  
CLOSE SalesCursor  
DEALLOCATE SalesCursor;

Looping Di Dalam Looping - SQL Server

if OBJECT_ID('tempdb.dbo.#tempkrtdep', 'U') is not null
drop table #tempkrtdep;

create table #tempkrtdep(no_rek varchar(15), angsuran_ke int, tgltempo date, nominalpokok int, bunga varchar(5));

declare @norek varchar(15)
declare @jangka int
declare @jangka2 int
declare @Counter int

-- untuk looping noreknya
declare SalesCursor cursor for
    select NO_REK,JANGKA from TEMP_DEP ;


open SalesCursor
fetch next from SalesCursor into @norek,@jangka
WHILE @@FETCH_STATUS = 0
    begin

    -- untuk looing jangkanya
    declare SalesCursor2 cursor for select @jangka
    SET @Counter = 1

    open SalesCursor2
    fetch next from SalesCursor2 into @jangka2
    WHILE @Counter <= @jangka2
    begin

        insert into #tempkrtdep
        select
        NO_REK,@Counter, TGL_DUE, NOMINAL, BUNGA
        from TEMP_DEP where NO_REK=@norek

        SET @Counter = @Counter + 1
        CONTINUE;
   
    fetch next from SalesCursor into @jangka2
    end
    close SalesCursor2
    deallocate SalesCursor2
   

FETCH NEXT FROM SalesCursor INTO @norek,@jangka
END  
CLOSE SalesCursor  
DEALLOCATE SalesCursor

SELECT * FROM #tempkrtdep order by no_rek, angsuran_ke

Tanggal - SQL Server

SELECT tgltempo,
(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(tgltempo)-1),tgltempo),101)) awal_bulan,
(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,tgltempo))),DATEADD(mm,1,tgltempo)),112)) akhir_bulan,
(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(tgltempo)),tgltempo),101)) akhir_bulan_sblmnya,
(year(tgltempo)) tahun,
(month(tgltempo)) bulan,
(day(tgltempo)) hari,
DATEADD(MONTH, 1, tgltempo) tambah_bulan,
DATEADD(MONTH, -1, tgltempo) kurang_bulan,
DATEADD(year, 1, tgltempo) tambah_tahun,
DATEADD(year, -1, tgltempo) kurang_tahun,
DATEADD(day, 1, tgltempo) tambah_hari,
DATEADD(day, -1, tgltempo) kurang_hari,
(day(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,tgltempo))),DATEADD(mm,1,tgltempo)),101))) jml_hari_dibulan
FROM #tempkrtdep order by no_rek, angsuran_ke

Looping Insert dengan beberapa kondisi atau fungsi - PostgreSQL

DO $$
DECLARE
   iterator INTEGER; 
    noreg varchar;
    idnsb int;
          cur2 cursor for SELECT "NO_REG", CAST("RECID" AS INT) FROM migrasi.tmp_nasabah;
BEGIN
    iterator :=10000; -- atur sequence m_nasabah_perorangan_personal
    open cur2;
Loop   
    fetch next from cur2 into noreg, idnsb;
    EXIT WHEN NOT FOUND;
   
INSERT INTO m_nasabah_perorangan_personal
(id_entry,id_update,d_entry,d_update,jenis_kelamin,nama_ibu_kandung,nama_pasangan,no_identitas_pasangan,perjanjian_pisah_harta,status_perkawinan,tgl_lahir,tempat_lahir,tgl_lahir_pasangan,id_agama,id_kota_kabupaten_lahir,id_kota_kabupaten_lahir_pasangan,id_nasabah_perorangan,id_pendidikan,id_propinsi_lahir,id_propinsi_lahir_pasangan,id_nasabah_perorangan_personal)
select
'MIGRASI','MIGRASI',NOW(),NOW(),CAST(XX."JNS_KELAMI" AS INT),xx."IBU_KANDUN",xx."PSG_NAMA",xx."KTP_PSG",1,CAST(xx."STATUS" AS INT), CAST(xx."TGL_LAHIR" AS DATE),xx."TMP_LAHIR",
(case when xx."TGL_LAHIRPASANGAN"='' then null
            else (CAST(xx."TGL_LAHIRPASANGAN" AS DATE)) end) tgllahirpsg,
(case when xx."AGAMA"= '1' then 94
             when xx."AGAMA"= '2' then 93
              when xx."AGAMA"= '3' then 106
                 when xx."AGAMA"= '4' then 107
                  when xx."AGAMA"= '5' then 297
                     when xx."AGAMA"= '6' then 109
                      when xx."AGAMA"= '7' then 673 end) agama,
(case when yy.idkabupaten is null then (select idkabupaten from migrasi.temp_alamat_bpr)
            else yy.idkabupaten end ) idkabupaten,null, CAST(xx."RECID" AS INT),
(case when xx."GELAR"='0100' then 389
            when xx."GELAR"='0101' then 390
            when xx."GELAR"='0102' then 391
            when xx."GELAR"='0103' then 392
            when xx."GELAR"='0104' then 393
            when xx."GELAR"='0105' then 394
            when xx."GELAR"='0106' then 395
            when xx."GELAR"='0109' then 396
            when xx."GELAR"='' then 389
            else 4 end) id_pendidikan,
(case when yy.idpropinsi    is null then (select idpropinsi from migrasi.temp_alamat_bpr)   
            else yy.idpropinsi end) idpropinsi, null, iterator
from migrasi.tmp_nasabah xx
left join migrasi.tmp_alamat yy on xx."NO_REG"=yy."NO_REG"
where CAST(xx."RECID" AS INT)=idnsb;

    iterator := iterator + 1;

   END LOOP;
         close cur2;
END $$;

Note: Perhatikan tipe data field source dan destination, harus sama

Contoh Penggunaan Fungsi di Syntax - SQL Server

select
concat('01',tab.cno) NO_REG,
tab.noac NO_REK,
(case when tab.jenisprd=1 then 16
      else 17 end) KODE,
convert(varchar, getdate(), 23) TGL_REG,
tab.status STATUS,
(case when tab.jenisprd=1 then 2500
      else 5000 end) SLS_ADM,
convert(int,tab.sldakhir) SALDO,
convert(int,tab.minsld) SALDO_MIN,
convert(int,tab.nlhold) SLD_JMN,
convert(int,tab.sldawal) SLD_AWL,
3 BUNGA,
(case when retcepe.flgqq=1 then ''
      when retcepe.flgqq=2 then retcepe.aliasnm
      else retcepe.aliasnm end) NAMA_QQ,
'' RECID
from SHDBLC tab
left join RET100 retcepe on tab.noac=retcepe.noac

Beberapa Penggunaan Fungsi Syntax - Oracle

SELECT THN, BLN_THN, KODE, COUNTER_MAX, COUNTER, PANJANG,
        CASE WHEN PANJANG=4 THEN TO_CHAR(COUNTER)
                WHEN PANJANG=3 THEN TO_CHAR(0||COUNTER)
                WHEN PANJANG=2 THEN TO_CHAR(0||0||COUNTER)
                WHEN PANJANG=1 THEN TO_CHAR(0||0||0||COUNTER)
        END AS KODE_NEW
FROM ( SELECT  TO_CHAR(TGL_SLS_AR,'YYYY') THN, TO_CHAR(TGL_SLS_AR,'MM/YYYY') BLN_THN,
        SUBSTR(CD_SLS_AR,1,8) KODE,
        MAX(SUBSTR(CD_SLS_AR,9,LENGTH(CD_SLS_AR)-0)) COUNTER_MAX,
        (TO_CHAR((MAX(SUBSTR(CD_SLS_AR,9,LENGTH(CD_SLS_AR)-0))) )+1) COUNTER,
        LENGTH(TO_CHAR((MAX(SUBSTR(CD_SLS_AR,9,LENGTH(CD_SLS_AR)-0))) )+1) PANJANG
        FROM SLS_AR
        GROUP BY TO_CHAR(TGL_SLS_AR,'YYYY'),TO_CHAR(TGL_SLS_AR,'MM/YYYY') ,SUBSTR(CD_SLS_AR,1,8)
        ORDER BY TO_CHAR(TGL_SLS_AR,'YYYY') ,TO_CHAR(TGL_SLS_AR,'MM/YYYY')
        )

Field Tersembunyi Dalam Database - Oracle

SELECT
ROWNUM,
ID,
ROWID AS KODE,
SYS_GUID()
FROM NAMATABEL

Looping - Oracle

Konsep sederhana
BEGIN
FOR SV IN
(
SELECT ID_BRG, ID_GDG, SUM(QTY_SLS_INV_DTL) AS QTY_SV FROM SLS_INV_DTL
GROUP BY ID_BRG, ID_GDG
)
LOOP
        UPDATE T_HPP_SUMMARY
        SET QTY_SV=SV.QTY_SV
        WHERE ID_BRG=SV.ID_BRG AND ID_GDG=SV.ID_GDG;
 COMMIT;
 END LOOP;     
 END;

Concat atau Gabungan - Oracle

Gabungkan beberapa row dari suatu field menjadi satu
SELECT cd_sls_inv,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(id_brg,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM   (SELECT cd_sls_inv,
               id_brg,
               ROW_NUMBER () OVER (PARTITION BY cd_sls_inv ORDER BY id_brg) AS curr,
               ROW_NUMBER() OVER (PARTITION BY cd_sls_inv ORDER BY id_brg) -1 AS prev
        FROM sls_inv_dtl where cd_sls_inv='SVC/1807/0011')
GROUP BY cd_sls_inv
CONNECT BY prev = PRIOR curr AND cd_sls_inv = PRIOR cd_sls_inv
START WITH curr = 1;

HASIL:
SVC/1807/0011    1,13,20,34,47

Tanggal - Oracle

AMBIL ATAU LIHAT DATA BERDASARKAN BULAN DAN TAHUN
SELECT
TO_CHAR(A.TGL_PUR_RCV,'MM/YYYY') BULAN_RCV,
A.CD_PUR_RCV,
TO_CHAR(B.TGL_PUR_INV,'MM/YYYY') BULAN_INV,
A.TGL_PUR_RCV,
B.CD_PUR_INV,
B.TGL_PUR_INV
FROM PUR_RCV A, PUR_INV B
WHERE A.CD_PUR_RCV=B.CD_PUR_RCV
AND A.TGL_PUR_RCV BETWEEN TO_DATE('01/01/2018','DD/MM/YYYY') AND TO_DATE('31/01/2018','DD/MM/YYYY')
ORDER BY A.TGL_PUR_RCV

HASIL:
01/2018    PR/1712/0034    01/2018    03/01/2018    PVC/1801/0001    03/01/2018 

MELIHAT DATA PERBULAN DALAM 1 TAHUN
SELECT TO_CHAR(TGL,'MM/YYYY') BULAN, SUM(TOTAL) TOTAL FROM (
SELECT A.TGL, A.CD_TRANS, SUM( NVL(B.VAL_BRG,0) * B.QTY) TOTAL  FROM IVR_VAL_OUT A, IVR_VAL_OUT2 B
WHERE A.CD_VAL_OUT=B.CD_VAL_OUT
AND SUBSTR(A.CD_TRANS,1,2)='SV'
AND A.TGL BETWEEN TO_DATE('01/01/2017','DD/MM/YYYY') AND TO_DATE('31/12/2017','DD/MM/YYYY')
GROUP BY  A.TGL,A.CD_TRANS
ORDER BY A.CD_TRANS)
GROUP BY TO_CHAR(TGL,'MM/YYYY')
ORDER BY BULAN

CONVERT
TO_CHAR(REC_DT,'DD/MM/YYYY')<>TO_CHAR(DT_SRL,'DD/MM/YYYY')
TO_DATE('31/12/2017','DD/MM/YYYY')

Cara Restore Database PostgreSQL - PostgreSQL

Contoh kita igin restore database di lokal:
1.create new database (jika sudah ada yg lama bisa di rename dulu, baru create yang baru) (contoh Ini dilakukan di Navicat)
    1.1 untuk rename, query rename database (tab1)
          ALTER DATABASE bpr RENAME TO bpr_rt;
         dan kill user (tab2)
         SELECT pg_terminate_backend(pid)
         FROM pg_stat_activity
         WHERE pid <> pg_backend_pid() AND datname = 'bpr';
         tanpa memilih databasenya. jalankan tab1 dan tab2. untuk tab2 sampe tidak ada yg muncul
    1.2. kemudian new database (dalam hal ini nama DB nya bpr) owner:pilihownernya tablespace:pg_default
2. Buka CMD di ===> C:\PostgreSQL\pg95\bin
3. Jalankan sintax di bawah ini:
pg_restore --host localhost --port 5432 --username "ukabima" -W --dbname "bpr" --role "pilihownernya" --verbose "D:UKABIMA\DATABASE DAN BACKUP\db_backup_ebpr__20200108100001.backup"
Ket: D:UKABIMA\DATABASE DAN BACKUP\db_backup_ebpr__20200108100001.backup" ==> adalah letak dan nama file databasenya
4. masukan password dari pilihownernya
5. tunggu sampai selesai

Looping IF dan Update Tanggal - PostgreSQL

CASE:
Kita ingin mengubah tanggalnya aja, tanpa mengubah tahun dan bulannya

DO $$
    Declare idkrt int;   
        Declare bulanke int;   
        DECLARE idaset int;   
        Declare jwl date;
        DECLARE jmlhari int;
        DECLARE tanggal int;
    cur1 CURSOR for select id,
                                                depresiasi_bulan_ke,
                                                id_aset_inventaris,
                                                jwl_depresiasi,
                                             DATE_PART('days',
        DATE_TRUNC('month', jwl_depresiasi)
        + '1 MONTH'::INTERVAL
        - '1 DAY'::INTERVAL
    ) jumlah_hari   
                                    from m_kartu_aset_inventaris where id_aset_inventaris in (2659, 2658)  order by id_aset_inventaris, depresiasi_bulan_ke;   
 begin 
OPEN Cur1 ; 
Loop     


    FETCH  next from Cur1 INTO idkrt, bulanke, idaset, jwl, jmlhari;    
    EXIT WHEN NOT FOUND;
   
select (date_part('day', tanggal_beli)) into tanggal from m_aset_inventaris where id=idaset;
   
    if jmlhari >= tanggal then
    update m_kartu_aset_inventaris
    set jwl_depresiasi=cast(CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',tanggal) as date)
    where id_aset_inventaris=idaset and depresiasi_bulan_ke=bulanke;
    END IF;
   
    if jmlhari < tanggal then
        update m_kartu_aset_inventaris
    set jwl_depresiasi=cast(CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',jmlhari) as date)
    where id_aset_inventaris=idaset and depresiasi_bulan_ke=bulanke;
    END IF;

end loop ;  
CLOSE Cur1; 
END $$;

Cek Size Database dan Tabel - SQL Server

CEK SIZE DATABASE
SELECT
sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space] 
FROM sys.databases  
JOIN sys.master_files ON sys.databases.database_id=sys.master_files.database_id 
GROUP BY sys.databases.name 
ORDER BY sys.databases.name

CEK SIZE TABLE
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY p.rows desc

Cek Size Database dan Tabel - PostgreSQL

CEK SIZE DB DALAM SUATU SERVER
select
t1.datname AS db_name, 
pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

CEK SATUAN
Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));
Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

CEK SIZE TABEL
SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

CEK JUMLAH ROW TABLE 1
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

CEK JUMLAH ROW TABLE 2
select table_schema,
       table_name,
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema,
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t