bontor-21-04-2011

Here I come again, after a while, long while. deferrable/ not deferrable adalah mekanisme pengecekan constraints: bila deferrable, maka constraint akan dicek saat setelah perintah commit. Sedangkan not deferrable, maka constraint dicek segera setelah perintah dml dieksekusi.

Saya mempunyai suatu konstrain primary key 'PK_CTLOG_H_LIST_MEDIA' pada kolom id_media di table CTLOG_H_LIST_MEDIA

Contoh:

SELECT   table_name,
         constraint_name,
         status,
         deferrable
FROM     user_constraints
where constraint_name='PK_CTLOG_H_LIST_MEDIA'
ORDER BY table_name, constraint_name;

TABLE_NAME        |CONSTRAINT_NAME               |STATUS  |DEFERRABLE
------------------|------------------------------|--------|--------------
CTLOG_H_LIST_MEDIA|PK_CTLOG_H_LIST_MEDIA         |ENABLED |NOT DEFERRABLE

Lalu saya tes dengan query,

Sql> @ctlg_id_media;
Enter value for p_label_media: Dkmtr_20110421

ID_MEDIA  |LABEL_MEDIA
----------|-----------------------------------
C99D-1336 |Dkmtr_20110421

Artinya rekor dengan id ‘C99D-1336’ sudah ada!

Sql> insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336');
Inserting
insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336')
*
ERROR at line 1:
ORA-00001: unique constraint (SPEAKER.PK_CTLOG_H_LIST_MEDIA) violated

Artinya settingan mekanisme “NOT DEFERRABLE” efektif.

Sebaliknya jika settingan saya robah,

Sql> ALTER TABLE CTLOG_H_LIST_MEDIA
  2  ADD   CONSTRAINT PK_CTLOG_H_LIST_MEDIA
  3  PRIMARY KEY (id_media) DEFERRABLE INITIALLY DEFERRED;

Table altered.

Saya periksa dengan:

Sql> SELECT   table_name,
  2           constraint_name,
  3           status,
  4           deferrable, deferred
  5  FROM     user_constraints
  6  where constraint_name='PK_CTLOG_H_LIST_MEDIA'
  7  ORDER BY table_name, constraint_name;

TABLE_NAME                    |CONSTRAINT_NAME               |STATUS  |DEFERRABLE    |DEFERRED
------------------------------|------------------------------|--------|--------------|---------
CTLOG_H_LIST_MEDIA            |PK_CTLOG_H_LIST_MEDIA         |ENABLED |DEFERRABLE    |DEFERRED

 

Lalu saya masukkkan rekor yang ditolak tadi dengan:

Sql> insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336');
Inserting

1 row created.

Sql> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SPEAKER.PK_CTLOG_H_LIST_MEDIA) violated

 

Pengecekan akan efektif bila perintah “commit” dieksekusi. Itulah pengertian dan dampak dari “deferrable/ not deferrable”

bontor-20110110

Cumulative Summary Report Query

Pendekatan yang digunakan untuk SQL Server agak berbeda dibanding yang digunakan di Oracle, yaitu dengan menggunakan Cartesian Operator (Cross Join).

Data awal saya  adalah sbb:

Saya ingin menampilkan statistik barang dengan id dan tanggal dengan kumulatif saldo per id barang, dimana kumulatif akan di-reset ke nilai awal barang mengikuti id barang selanjutnya.

Pendekatannya adalah dengan meng-cross join tabel tersebut dengan dirinya sendiri dimana jembatannya adalah id dan non equi joinnya digunakan tgl. Jumlah kumulatifnya akan direset sesuai idnya.

Dengan query berikut,

Select a.id,
       a.tgl,a.tipe,
       Case a.tipe When 1 Then a.saldo When 0 Then -a.saldo End
          As saldo,
       sum (Case b.tipe When 1 Then b.saldo When 0 Then -b.saldo End)
          As sum_saldo
From HR.dbo.x_a a Cross Join HR.dbo.x_a b
Where a.tgl >= b.tgl And a.id = b.id
Group By a.id,
         a.tgl,a.tipe,
         Case a.tipe When 1 Then a.saldo When 0 Then -a.saldo End
Order By a.id,
         a.tgl,
         Case a.tipe When 1 Then a.saldo When 0 Then -a.saldo End;

Hasilnya menjadi spt gambar berikut: