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