Archive for May 2013

Home » Archive for May 2013

Penggunaan Over Partition Pada Sql Oracle

Penggunaan Over Partition Pada Sql Oracle

Salah satu fitur unik lain yang dimiliki sql di oracle daripada sql biasa adalah keberadaan fungsi over partition. Salah satu syntax yang sering digunakan programmer oracle dalam mengolah query. Over partition merupakan syntax yang berguna untuk melakukan pemilahan atau grouping data berdasarkan string atau kriteria yang dimasukkan. Perintah yang satu ini berperan sama seperti halnya group by. Hanya saja memiliki beberapa perbedaan mendasar dari 'kakak'nya itu.

Bila group by diletakkan di bagian belakang atau paling bawah dari sript, maka tidak dengan over partition. Over partition malah diletakkan pada syntax yang masih termasuk bagian select, dimana digunakan pada field tertentu yang ingin ditampilkan. Kesamaannya, baik over dan group ini membutuhkan trigger berupa syntax counting group, seperti sum, min, max, dan sebagainya.

Syntax :

Select sum/min/max/dsb..(nama_kolom) over (partition by nama_kolom_grouping)

contoh :

Select a.nama_siswa, sum(a.nilai_UAN) over (partition by a.nama_siswa) Total_nilai from data_nilai a

Di atas adalah example dari suatu kasus.

Misalkan dalam tabel ada beberapa nama dan nilai seperti pada kolom. Pada suatu saat, kita ingin menampilkan data berupa nama siswa, yang muncul hanya SATU saja, dan juga jumlah keseluruhan nilai UAN dari siswa bersangkutan.

Contoh tampilan tabel :

Nama_siswa	Mapel	Nilai_UAN
John	MTK	10
Herman	MTK	9
Budi	MTK	8
Anduk	MTK	8
John	Bahasa Indonesia	8
Herman	Bahasa Indonesia	9
Budi	Bahasa Indonesia	7
Anduk	Bahasa Indonesia	7
John	Bahasa Inggris	9
Herman	Bahasa Inggris	8
Budi	Bahasa Inggris	8
Anduk	Bahasa Inggris	10

Dengan kata lain, kita membutuhkan perintah sum untuk menjumlahkan semua data. Tapi, jika hanya memakai sum saja, maka hasilnya akan ambigu (Itu pun jika tidak memasukkan nama_siswa dalam query). Karena seluruh kolom nilai UAN pada tabel akan dihitung jadi satu. Oleh karena itu, kita perlu suatu pembeda atau grouping. Dan pada kasus ini kita menggunakan nama siswa.

Dengan memakai over partition - sama seperti group by -, maka result yang akan ditampilkan berupa dua buah kolom, satu nama siswa, kemudian yang kedua adalah kolom berisi jumlah nilai UAN dari siswa tersebut.

Hasil dari query :

Nama_siswa	Total_nilai
John	27
Herman	26
Budi	23
Anduk	25

Penggunaan over partition, lebih mudah dalam pengaplikasian jika membutuhkan grouping yang banyak. Ditambah lagi, dalam over partition, kita tak perlu menuliskan semua field yang dimasukkan, hanya beberapa saja pun sudah bisa untuk menjalankannya. Bandingkan dengan group by yang harus menuliskan semua nama field yang masuk dalam query select.

Logika :

select a,b,c,d,e,f, sum(g) over (partition by a,b) from tabel1

Tidak perlu memasukkan semua dari a sampai f ke dalam (partition by ...). Bisa melakukan grouping secara terpilah, dan tak harus menggunakan semua field yang ditampilkan pada query.

select a,b,c,d,e,f, sum(g) from tabel1 group by a,b,c,d,e,f

Mau tidak mau harus memasukkan semua dari a sampai f untuk mentrigger sum dari g. Ada kalanya, terkadang field yang kita tampilkan, tidak diperlukan dalam proses grouping. Bahkan bila dimasukkan malah bisa mengacaukan grouping. Celakanya, padahal field tersebut pun juga harus muncul dalam result sesuai permintaan dari user. Dan jika kita memakai group by, hal ini akan menimbulkan banyak polemik karena hasil yang ditampil tak sesuai harapan. Sum yang seharusnya hanya dikelompokkan oleh 2 kolom, tapi dengan group by, dengan ‘terpaksa’ harus digolongkan dalam semua kolom yang muncul.

Karena itulah, over partition bisa menjadi salah satu alternatif untuk mengakali keribetan ini. Walau pun, jika memakai over partition, kerepotannya adalah jika kita memakai banyak sum, atau counting group lain. Mau tidak mau , kita juga harus memasangkan over partition di seluruh kolom yang memiliki counting group di dalamnya.

Insya Allah, semoga sedikit informasi ini dapat bermanfaat!

Penggunaan Substring dan Instring dalam SQL Oracle

Penggunaan Substring dan Instring Dalam SQL Oracle

Salah satu fitur yang lumayan penting dan sering digunakan dalam script pengolahan data adalah substring dan instring. Karena memang, keduanya cukup 'mahir' dalam melakukan manipulasi atau untuk 'mengakali' data, terutama jika menyangkut dengan hasil yang berformat string. Sebenarnya, apa sih inti dari dua syntax tersebut?

SUBSTRING

Substring merupakan fungsi yang menghasilkan value berupa string dengan jumlah tertentu, sesuai dengan porsi yang ditentukan oleh programmer.

Syntax :

substr(string, posisi_awal, jumlah_string yang diambil dari posisi_awal)

Penggunaan, misalkan kita ingin menampilkan nama pegawai kita, tapi hanya 10 huruf depannya, atau menampilkan nama dari kata ke 2,3 dan sebagainya.

Contoh :

substr('John Herman',1,4)

Nah, perintah di atas, berarti akan memunculkan value dari John Herman, dari huruf pertama, dengan panjang 4 huruf dari yang pertama tadi. Maka menghasilkan result John.

substr('John Herman',6,6)

Kalau yang ini, perhatikan baik-baik bilangan 6 setelah tulisan John Herman. Berarti pengambilan karakter dimulai dari yang ke-6, yaitu H. (Spasi juga dihitung karakter ya.. jadinya terbaca.)

Sampai dengan jumlah 6 karakter setelahnya. Maka outputnya adalah Herman.

INSTRING

Nah, kalo instring, agak beda. Tapi menurutku, merupakan pasangan sejoli substring. Hehehe... karena biasanya dua syntax ini sering aku combo untuk hasil menggelegar. Uh, lebay... Instring berfungsi untuk melakukan pencarian posisi suatu string. Hasilnya bukan berupa string, namun numeric. Simpelnya, mencari posisi karakter X ada pada urutan berapa.

Syntax :

instr(string, karakter/kata yang dicari, posisi awal pencarian, kata/karakter ke-berapa)

Uhmm, agak ribet. Tenang, santai aja kok.

Contohnya berikut :

instr('John Herman','J',1,1)

Jika diterjemahkan, kita mencari string 'J' pada kata 'John Herman'. Kemudian, angka 1 yang awal, bermakna kita mencari dari karakter terdepan. Paling awal. Jadi instr akan memproses atau menghitung proses pencarian dari awal, dimulai dari J o h n H e r m a n. Urut dari yang pertama. Kemudian, 1 yang kedua, artinya kita mencari kata 'J' pertama. Ingat, J yang pertama kali muncul.

Maka result value adalah 1. Karena kata J yang pertama sudah muncul di bilangan pertama.

Contoh lain :

instr('John Herman','n',1,2)

Kalau yang ini, mencari kata n dari John Herman, dari bilangan pertama.

Dimana kata n yang dicari adalah kata n kedua. Ingat, kedua.

Result value yang muncul yaitu 11.

Karena n pertama ada di no.4

Dan n kedua ada di paling buncit(11). Mulai paham 'kan?

Oh, iya. Instring tidak hanya bisa memulai pencarian dari depan saja lho. Kita bisa membaliknya. Kita pakai example n tadi lagi.

instr('John Herman','n',-1,2)

Lihat, lihat. Sekarang kok ada tanda negatif di depan 1? Tak usah bingung lama-lama. Dengan ini, maka pencarian n yang kedua tadi, dilakukan dari belakang, alias terbalik. Benar, tanda - (negatif) berarti membuat pengurutan pencarian dimulai dari belakang.

Maka result value adalah 4.

n pertama dari belakang adalah no.11.

n kedua dari belakang adalah no.4.

Meski dimulai dari belakang, tapi penghitungan tetap dari depan. Ingatlah akan hal itu. Hehehe...

Benar juga, bila string yang dicari tidak ada, maka nilai baliknya adalah 0.

Sebagai programmer database yang berhubungan langsung dengan pengolahan data, tentu kita harus jeli dan kreatif dalam membuat program. Terkadang, kita pun juga memerlukan langkah-langkah anomali untuk melakukannya. Penggabungan instring dan subtring pun pada awalnya tak berniat kucoba. Namun, karena kondisi mendesak, dan permintaan dari pengguna yang menginginkan data tampil sedemkian rupa. Mau tidak mau, kita harus mengakali dan mengotak-atik dengan menjoinkan fungsi subtring dan instring ini. Yup, yup...

Insya Allah, semoga sedikit penjelasan ini dapat membantu!

Concat Dalam SQL Oracle

CONCAT DALAM SQL ORACLE

Concat berasal dari kata bahasa Inggris yaitu concatenation, yang berarti rentetan atau rangkaian yang saling jalin-menjalin. Fungsi utama dari concat adalah menggabungkan dua buah karakter menjadi satu kesatuan kalimat. Jika dua string yang akan digabungkan memiliki tipe data yang berbeda, maka hasilnya akan memiliki format data CHAR. Jadi, meski string pertama berupa date, lalu yang kedua merupakan char biasa, maka hasilnya pun tetap merupakan jenis CHAR. Jika melibatkan karakter yang memiliki format number, maka hasilnya pun juga tetap ada unsur CHAR. Meski result value tersebut masih bisa diolah dengan proses penghitungan seperti penambahan dan pengurangan.

Syntax :

Concat(string1,string2)

Contoh :

concat('John','Herman')

Berdasarkan example, maka hasil dari perintah di atas akan menampilkan data berupa JohnHerman. String pertama dan kedua digabungkan, tanpa ada spasi atau pemisah. Karena memang concat tidak menyediakan fitur space untuk setiap character yang dijoinkan. Bila ingin menambahkan space, kita bisa menambahkan spasi setelah kata satu atau sebelum kata dua.

concat('John ','Herman')

Menambahkan space seperti yang tertera di bagian atas, maka result valuenya adalah John Herman.

Penggabungan dua buah field data menjadi satu kadang kita perlukan. Misalkan, jika saja kolom data nama depan pegawai terpisah dengan kolom nama belakang pegawai. Atau ingin menggabungkan nama lengkap dengan kode pegawai.

Nah, bila untuk menyatukan dua data sih, concat dengan syntax di atas masih bisa diandalkan. Tapi, sayangnya. Kemampuannya terbatas untuk penggabungan sebanyak dua string saja. Dengan kata lain, kita tidak bisa menjoinkan tiga string secara berurutan atau lebih, dalam satu concat. Bila ingin melakukannya, kita 'dipaksa' untuk menambah syntax concat terus menerus.

Contoh :

concat(concat(concat('John','Herman'),'Budi'),'Anduk')

Nah, nah. Lihatlah. Seandainya ingin 4 string jadi satu, maka secara berurutan, kita pun harus menuliskan concat setelah concat sebelumnya. Jadi, pertama, concat untuk proses JohnHerman, lalu concat kedua agar JohnHerman + Budi. Dan terakhir, untuk menyatukan JohnHermanBudi + Anduk.

Aduh, kelihatannya malah repot ya..

Nah, kalo ada banyak, berapa nambah concat terus dong. Wah, repot juga deh. Hmm.. tenang saja gan. Untuk itu, kita bisa melakukan tips lain kok. Yakni memakai concat dalam bentuk simbol.

Benar, kita bisa mengubah syntax concat dengan mengandalkan ||.

Untuk bisa mendapatkan tanda itu tekan shift + \.

Itu adalah tanda yang biasanya sih ada di atas enter keyboard. Lihat 'kan? ada kombinasi \ dan |.

Variasi Syntax :

string1 || string2

string1 ||' '|| string2

string1 || string2 || ' '|| string3

Wah, kalau yang ini malah ada banyak style-nya gan. Penggunaanya pun cukup simpel. Tidak perlu kurung segala seperti concat biasa.

Contoh :

John || Herman

Result ==> JohnHerman

John ||' '|| Herman

Result ==> John Herman

John ||' '|| Herman ||Budi

Result ==> John HermanBudi

Dengan tanda || ini, pemakaian kata concat tidak diperlukan lagi untuk penggabungan karakter. Selain itu pada tengah-tengah ||''||, dibagian antara tanda petik satu, bisa diisi dengan kata atau spase sebagai pemisah string sebelum dengan sesudahnya.

Pemakaian || pun lebih mudah digunakan serta banyak kelebihan daripada concat, terutama dalam hal keringkasan maupun 'keindahan' mata memandang. Bandingkan saja, lebih mudah untuk menelaah dan membongkar script yang tersusun rapi dari tanda || daripada berjubel tulisan concat(concat(concat... bla..bla..bla yang membuat kita harus memeriksa dengan teliti satu persatu pemakaiannya. Hahaha...

Upps.. Tapi, bukan berarti concat itu sudah tidak diperlukan lagi lho. Buktinya saja, di Oracle masih ada syntax concat. Hehehe..

Insya Allah, semoga sedikit informasi ini dapat bermanfaat!

Qashna Review

Penggunaan Over Partition Pada Sql Oracle

Penggunaan Substring dan Instring dalam SQL Oracle

Concat Dalam SQL Oracle

Entri Populer

Jurnal Perjalanan