Merubah nilai NULL menjadi 0 (Coalesce)

Ada contoh kasus nih… misalkan ada sebuah tabel sebagai contoh nama tabelnya adalah TEMP_BARANG dengan 3 (tiga) field antara lain BARANG, JUAL, RETUR dan kalo digenerate maka DDL-nya seperti berikut ini:

CREATE TABLE TEMP_BARANG (
BARANG VARCHAR(10),
JUAL INTEGER,
RETUR INTEGER);

Dengan data percobaan seperti berikut ini:

Barang Jual Retur
---------------------
Pensil 4 NULL
Buku 3 2

Si boss pengen laporan tiap bulannya seperti ini:

Barang Jual Retur Saldo
----------------------------
Pensil 4 0 4
Buku 3 2 1

Tanpa berpikir panjang aku bikin query yang isinya bisa dilihat dibawah ini.

SELECT BARANG, JUAL, RETUR, (JUAL - RETUR) SALDO
FROM TEMP_BARANG

Tapi setelah query aku jalankan, kok hasilnya seperti dibawah ini… anehh ??? Untuk Pensil kok gak kejumlah, sedangkan Buku kejumlah dengan benar. Masalahnya dimana ya ??

Barang Jual Retur Saldo
----------------------------
Pensil 4 NULL NULL
Buku 3 2 1

LPAD – Format angka dengan 0 didepan (Posting Jadul)

Fungsi LPAD ini sebenarnya mirip dengan fungsi FORMATFLOAT di delphi, yang akan melakukan format angka sesuai keinginan kita. Biasanya aku menggunakan fungsi ini untuk membuat periode yang terdiri dari gabungan tahun dan bulan seperti 0401. Jika dilakukan SELECT seperti biasa tanpa LPAD, maka akan menghasilkan 041, sebenarnya gak masalah jika bulan tersebut lebih besar samadengan 10 karena akan tercetak 2 digit menjadi 0410. Terus gimana caranya biar ada angka 0 didepan jika bulan tersebut lebih kecil samadengan 9 ? Gunakan fungsi LPAD dengan syntax seperti dibawah ini :

LPAD(n,m,o)

n: Angka yang akan diformat
m: Banyaknya digit yang ditampilkan
o: Angka penambah spasi, bisa 0 (nol) atau lainnya

Biar jelas, aku kasih contoh membuat sebuah kolom PERIODE yang diambil dari kolom dengan tipe DATE yg merupakan gabungan tahun dan bulan. Anggap saja ada tabel T_TESTING dengan salah satu kolom… anggap aja TGLENTRY dengan tipe DATE, untuk detilnya lihat perintah SELECT dibawah yang akan menghasilkan record dengan format YYMM (eg: 0410).

SELECT SUBSTRING(EXTRACT(YEAR FROM TGLENTRY) FROM 3 FOR 2) ||
LPAD(EXTRACT(MONTH FROM TGLENTRY),2,0)
FROM T_TESTING

Membuat crosstab query (Posting Jadul)

Umumnya setiap bulan, si bos biasanya minta laporan penjualan bulan kemaren. Dan biasanya jenis laporan yang diinginkan adalah hasil penjumlahan ataupun pengelompokkan berdasarkan kriteria tertentu, sehingga memudahkan dalam membaca laporan tersebut dengan cepat tanpa perlu mengetahui detil transaksi yang cukup banyak.

Dalam istilah database, untuk jenis laporan diatas disebut juga dengan crosstab report. Dan untuk membuat crosstab report maka diperlukan juga crosstab query, seperti apa sih modelnya crosstab query itu ?? Untuk mempermudah penjelasan, sengaja aku buat contoh sederhana, selanjutnya silahkan kembangkan sendiri sesuai dengan kebutuhan di perusahaan anda.

Misalkan ada sebuah toko serbaguna yang menjual beraneka macam dan ragam kebutuhan hidup, seperti pensil, buku dan alat komunikasi HaPe. Silahkan liat tabel dibawah ini dan anggap saja tabel dibawah ini adalah tabel transaksi penjualan dan kita namai aja TBL_JUAL.

Pelanggan Barang Qty
------------------------------
Sembara      Pensil        12
XeroisNOcute Buku          45
SyamSyah     Handphone     12
Yudha        Buku           5
NOCuteangels Handphone      7
Bento        Handphone      9
ModBLog      Pensil         9
SyamSyah     Handphone      3
SyamSyah     Book          45

Dari tabel diatas si bos minta laporan dengan format seperti ini:

Pelanggan Buku Handphone Pensil
------------------------------------
Sembara      NULL      NULL      12
XeroisNOcute   45      NULL    NULL
SyamSyah       45        15    NULL
Yudha           5      NULL    NULL
NOCuteangels NULL         7    NULL
Bento        NULL         9    NULL
ModBlog      NULL      NULL       9

Nah… bikin crosstab query-nya gimana ???