Langit Biru

bontor-21-04-2011

2011-04-21T19:22:00.001+07:00

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

2011-01-10T17:43:00.001+07:00

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:

Membuat job scheduler di Linux dengan Crontab

2010-07-09T17:17:00.003+07:00

Jerman sudah kalah dari Portugal di semi-final. Sementara Belanda sukses maju ke partai final setelah mengalahkan Uruguay.

Itulah hot news World Cup 2010 RSA.

Sekarang saya mau Membuat job scheduler di Linux dengan crontab.

Langkah2nya adalah sebagai berikut:

Buat skrip untk backup database,

vd=`date +%y%m%d`
mysqldump --user=root --password=root dmis_1 > /root/backup_db/dmis_1_$vd.sql

File disimpan di server dengan nama backup_dmis.scr

Lalu service crontab dinyalakan dengan:

(none):~ # /etc/rc.d/cron start

Starting CRON daemon done

Untuk pengecekan, jalankan skrip:

(none):~ # ps auwx | grep cron

root 5095 0.0 0.0 2336 820 ? Ss 18:46 0:00 /usr/sbin/cron

root 5097 0.0 0.0 2260 656 pts/5 R+ 18:46 0:00 grep cron

Tandanya service crontab sudah jalan.

Lalu job scheduler di pasang dengan skrip
crontab -e
diketik sesuai format yang diinginkan
Lalu, bila perlu, periksa dengan

(none):~ # crontab -l

# DO NOT EDIT THIS FILE - edit the master and reinstall.

# (/tmp/crontab.XXXX2bQcMC installed on Tue Jul 6 18:58:41 2010)

# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

SHELL=/bin/sh

01 01 * * * /root/backup_dmis.scr

Hasilnya, system akan melakukan backup database pada jam 01:01 setiap hari dan menyimpannya sebagai file di folder backup. Cek hasil file back up dengan perintah seperti berikut ini:

(none):~/backup_db # ls -al
total 139896
drwxr-xr-x 4 root root     4096 Jul 9 01:01 .
drwx------ 41 root root     4096 Jul 9 08:31 ..
drwxr-xr-x 2 root root     4096 Jul 6 14:09 100706
drwxr-xr-x 2 root root     4096 Jul 6 13:40 date
-rw-r--r-- 1 root root 10797677 Jul 6 18:51 dmis_1_100706?.sql?
-rw-r--r-- 1 root root 40225610 Jul 7 01:01 dmis_1_100707?.sql?
-rw-r--r-- 1 root root 40225610 Jul 8 01:01 dmis_1_100708?.sql?
-rw-r--r-- 1 root root 40225610 Jul 9 01:01 dmis_1_100709?.sql?

How to alternate minus operator in MySQL

2010-07-01T13:07:00.001+07:00

MySQL doesnt recorgnize minus operator? How to trick an invalid minus operator in MySQL RDBMS?

I am in situation where there are 2 table dmis.ref_mst_jenis_materi and dmis_v9.ref_mst_jenis_materi which both have the identical structure .

I want to migrate record in dmis_v9.ref_mst_jenis_materi which are not existed in dmis.ref_mst_jenis_materi.

As in Oracle we are so familiarized with minus operator, but MySQL doesn't recorgnize minus operator. So, we need a little trick to deliver this task. We can use outer join - where condition is null combination.

   1: Insert Into dmis.ref_mst_jenis_materi

   2:      Select b.*

   3:        From    dmis.ref_mst_jenis_materi a

   4:             Right Join

   5:                dmis_v9.ref_mst_jenis_materi b

   6:             On     b.id_kelompok = a.id_kelompok

   7:                And b.id_kategori = a.id_kategori

   8:                And b.id_Jenis = a.id_Jenis

   9:       Where a.Nm_Jenis_Materi Is Null

  10:    Order By b.id_kelompok, b.id_kategori, b.id_Jenis;

The highlighted point in the query is combination of outer join - where condition is null combination usage, as in line 4 and line 9.

The point is: “Widen your perspective to solve problems”.

World Cup Fever

2010-06-11T08:17:00.001+07:00

Deman Piala Dunia Hari ini perhelatan Piala Dunia akan dimulai, pertandingan pertama adalah Afrika Selatan melawan Meksiko yang diadakan jam 1600 waktu setempat atau jam 2100 WIB. Perhelatan akan berlangsung selama 1 bulan dari 11-Juni-2010 s/d 11-Juli-2010. Bila Anda ingin mengetahui jadwal pertandingan atau segala sesuatu tentang Piala Dunia, Anda bisa mencari di mesin Google. Ya, tentu saja Anda sudah tahu akan hal itu. Yang saya perhatikan, bahwa Google juga mengalami deman Piala Dunia, themes-nya kental dengan nuansa Piala Dunia, seperti di halaman depannya: dan juga: Selamat menonton Piala Dunia.

bontor-20100422

2010-04-22T11:56:00.001+07:00

My client has records from this query

   1: SELECT id_assessment,   2:        [A1] = (SELECT Penampungan_T4   3:                FROM dm_trx_detail_penampungan   4:                WHERE Penampungan_Jenis = 1   5:                      AND id_assessment = m.id_assessment),   6:        [A2] = (SELECT Penampungan_T4   7:                FROM dm_trx_detail_penampungan   8:                WHERE Penampungan_Jenis = 2   9:                      AND id_assessment = m.id_assessment),  10:        [A3] = (SELECT Penampungan_T4  11:                FROM dm_trx_detail_penampungan  12:                WHERE Penampungan_Jenis = 3  13:                      AND id_assessment = m.id_assessment),  14:        [B1] = (SELECT Sarana_Tenda_peleton  15:                FROM dm_trx_detail_penampungan  16:                WHERE Penampungan_Jenis = 1  17:                      AND id_assessment = m.id_assessment),  18:        [B2] = (SELECT Sarana_Tenda_peleton  19:                FROM dm_trx_detail_penampungan  20:                WHERE Penampungan_Jenis = 2  21:                      AND id_assessment = m.id_assessment),  22:        [B3] = (SELECT Sarana_Tenda_peleton  23:                FROM dm_trx_detail_penampungan  24:                WHERE Penampungan_Jenis = 3  25:                      AND id_assessment = m.id_assessment),  26:        [C1] = (SELECT Tempat_Tempat_ibadah  27:                FROM dm_trx_detail_penampungan  28:                WHERE Penampungan_Jenis = 1  29:                      AND id_assessment = m.id_assessment),  30:        [C2] = (SELECT Tempat_Tempat_ibadah  31:                FROM dm_trx_detail_penampungan  32:                WHERE Penampungan_Jenis = 2  33:                      AND id_assessment = m.id_assessment),  34:        [C3] = (SELECT Tempat_Tempat_ibadah  35:                FROM dm_trx_detail_penampungan  36:                WHERE Penampungan_Jenis = 3  37:                      AND id_assessment = m.id_assessment)  38: FROM dm_trx_detail_penampungan m  39: GROUP BY id_assessment;

as follow:

id_assessment	A1	A2	A3	B1	B2	B3	C1	C2	C3
010101100400001	Jl 1	Jl 2	Jl 3	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
010101100400002	Jl 1	Jl 2	Jl 3	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
020602100400001	Jl 1	Jl 2		FALSE	FALSE		FALSE	FALSE

My client wants to consider null value on bit type column as ‘FALSE’. After a while, I was thinking that I can apply nvl2-like function in Oracle RDBMS. Unfortunately such function doesn’t exist in MSSQL Server.

I got an alternate function case, by this case-modif function I can solve the task with the query below:

   1: SELECT id_assessment,   2:        CASE   3:         WHEN (SELECT Penampungan_T4   4:               FROM dm_trx_detail_penampungan   5:               WHERE Penampungan_Jenis = 1   6:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN   7:          (SELECT Penampungan_T4   8:           FROM dm_trx_detail_penampungan   9:           WHERE Penampungan_Jenis = 1  10:                 AND id_assessment = m.id_assessment)  11:         ELSE  12:          'FALSE'  13:        END  14:         AS A1,  15:        CASE  16:         WHEN (SELECT Penampungan_T4  17:               FROM dm_trx_detail_penampungan  18:               WHERE Penampungan_Jenis = 2  19:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  20:          (SELECT Penampungan_T4  21:           FROM dm_trx_detail_penampungan  22:           WHERE Penampungan_Jenis = 2  23:                 AND id_assessment = m.id_assessment)  24:         ELSE  25:          'FALSE'  26:        END  27:         AS A1,  28:        CASE  29:         WHEN (SELECT Penampungan_T4  30:               FROM dm_trx_detail_penampungan  31:               WHERE Penampungan_Jenis = 3  32:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  33:          (SELECT Penampungan_T4  34:           FROM dm_trx_detail_penampungan  35:           WHERE Penampungan_Jenis = 3  36:                 AND id_assessment = m.id_assessment)  37:         ELSE  38:          'FALSE'  39:        END  40:         AS A1,  41:        CASE  42:         WHEN (SELECT Sarana_Tenda_peleton  43:               FROM dm_trx_detail_penampungan  44:               WHERE Penampungan_Jenis = 1  45:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  46:          (SELECT Sarana_Tenda_peleton  47:           FROM dm_trx_detail_penampungan  48:           WHERE Penampungan_Jenis = 1  49:                 AND id_assessment = m.id_assessment)  50:         ELSE  51:          'FALSE'  52:        END  53:         AS B1,  54:        CASE  55:         WHEN (SELECT Sarana_Tenda_peleton  56:               FROM dm_trx_detail_penampungan  57:               WHERE Penampungan_Jenis = 2  58:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  59:          (SELECT Sarana_Tenda_peleton  60:           FROM dm_trx_detail_penampungan  61:           WHERE Penampungan_Jenis = 2  62:                 AND id_assessment = m.id_assessment)  63:         ELSE  64:          'FALSE'  65:        END  66:         AS B2,  67:        CASE  68:         WHEN (SELECT Sarana_Tenda_peleton  69:               FROM dm_trx_detail_penampungan  70:               WHERE Penampungan_Jenis = 3  71:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  72:          (SELECT Sarana_Tenda_peleton  73:           FROM dm_trx_detail_penampungan  74:           WHERE Penampungan_Jenis = 3  75:                 AND id_assessment = m.id_assessment)  76:         ELSE  77:          'FALSE'  78:        END  79:         AS B3,  80:        CASE  81:         WHEN (SELECT Tempat_Tempat_ibadah  82:               FROM dm_trx_detail_penampungan  83:               WHERE Penampungan_Jenis = 3  84:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  85:          (SELECT Tempat_Tempat_ibadah  86:           FROM dm_trx_detail_penampungan  87:           WHERE Penampungan_Jenis = 3  88:                 AND id_assessment = m.id_assessment)  89:         ELSE  90:          'FALSE'  91:        END  92:         AS C1,  93:        CASE  94:         WHEN (SELECT Tempat_Tempat_ibadah  95:               FROM dm_trx_detail_penampungan  96:               WHERE Penampungan_Jenis = 3  97:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN  98:          (SELECT Tempat_Tempat_ibadah  99:           FROM dm_trx_detail_penampungan 100:           WHERE Penampungan_Jenis = 3 101:                 AND id_assessment = m.id_assessment) 102:         ELSE 103:          'FALSE' 104:        END 105:         AS C2, 106:        CASE 107:         WHEN (SELECT Tempat_Tempat_ibadah 108:               FROM dm_trx_detail_penampungan 109:               WHERE Penampungan_Jenis = 3 110:                     AND id_assessment = m.id_assessment) = 'TRUE' THEN 111:          (SELECT Tempat_Tempat_ibadah 112:           FROM dm_trx_detail_penampungan 113:           WHERE Penampungan_Jenis = 3 114:                 AND id_assessment = m.id_assessment) 115:         ELSE 116:          'FALSE' 117:        END 118:         AS C3 119: FROM dm_trx_detail_penampungan m 120: GROUP BY id_assessment;

which returns rows below:

id_assessment	A1	A11	A12	B1	B2	B3	C1	C2	C3
010101100400001	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	TRUE
010101100400002	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	TRUE
020602100400001	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE

bontortp-20100330

2010-03-30T16:09:00.001+07:00

Sql> SELECT is_number (TRIM (SUBSTR (file_name, -3))) flag_nomor,

  2         TO_NUMBER ( TRIM (SUBSTR (file_name, -3))) nomor

  3  FROM   MST_DETAIL_FILE

  4  WHERE  id = 11 AND is_number (TRIM (SUBSTR (file_name, -3))) = 1;

FLAG_NOMOR|     NOMOR

----------|----------

         1|         0

         1|         1

         1|         2

         1|         3

         1|         4

         1|         5

         1|         6

..

..

..

         1|        85

         1|        86

         1|        87

         1|        88

         1|        89

         1|        90

         1|        91

         1|        92

         1|        93

94 rows selected.

Then I want to filterize the row between 51 and 93:

Sql> SELECT *

  2  FROM   (SELECT is_number (TRIM (SUBSTR (file_name, -3))) flag_nomor,

  3                 TO_NUMBER (TRIM (SUBSTR (file_name, -3))) nomor

  4          FROM   MST_DETAIL_FILE

  5          WHERE  id = 11 AND is_number (TRIM (SUBSTR (file_name, -3))) = 1)

  6  WHERE  nomor >= 51 AND nomor <= 96;

               TO_NUMBER (TRIM (SUBSTR (file_name, -3))) nomor

ERROR at line 3:

ORA-01722: invalid number

I wonder how it encounter error but the first query doesn’t.

bontor-20100224

2010-02-24T08:18:00.001+07:00

What type of programmer are you?

I came accross this images somewhere in internet, but admit it is not mine. Very inspirating.

So, what programmer are you?

bontor-20100222

2010-02-22T14:47:00.001+07:00

Weird….

I CTAS

Sql> Create table hr.my_employees as

  2  select * from hr.employees

  3  where employee_id=198;

Table created.

and I use Oracle Join

Sql> SELECT a.EMPLOYEE_ID, a.FIRST_NAME

  2  FROM   employees a, my_employees m

  3  WHERE  a.EMPLOYEE_ID = m.EMPLOYEE_ID;

EMPLOYEE_ID|FIRST_NAME

-----------|--------------------

        198|Donald

and inner join:

Sql> SELECT a.EMPLOYEE_ID, a.FIRST_NAME

  2  FROM      employees a

  3         JOIN

  4            my_employees b

  5         ON (a.EMPLOYEE_ID = b.EMPLOYEE_ID);

EMPLOYEE_ID|FIRST_NAME

-----------|--------------------

        198|Donald

It said than The natural join identifies columns with common names between the two tables. But when I use NATURAL JOIN (SQL:1999 Syntax), it returns no rows like this:

Sql> select EMPLOYEE_ID,FIRST_NAME from employees natural join my_employees;

no rows selected

Do I miss something?

bontortp-20100215

2010-02-15T16:55:00.001+07:00

How come?

I logged to database as system,

Dag> conn system@orcl11g

Enter password:

Connected.

then I CTAS as follow:

Dag> Create table hr.test_table as  2  SELECT CUST_FIRST_NAME,  3         CUST_LAST_NAME,  4         CUST_EMAIL,  5         DATE_OF_BIRTH,  6         CASE TO_NUMBER (TO_CHAR (DATE_OF_BIRTH, 'DD'))  7              - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')))  8            WHEN -2  9            THEN 10               'Two days ago' 11            WHEN -1 12            THEN 13               'One day ago' 14            WHEN 1 15            THEN 16               'Two days ago' 17            WHEN 2 18            THEN 19               'Two days in the future' 20            WHEN 4 21            THEN 22               '4 days in the future' 23            WHEN 5 24            THEN 25               '5 days in the future' 26            ELSE 27               'Within 7 days from today' 28         END 29            AS azz 30  FROM   oe.customers 31  WHERE  TO_NUMBER (TO_CHAR (DATE_OF_BIRTH, 'DD')) 32         - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD'))) BETWEEN -2 33                                                     AND  7 34  ORDER BY TO_CHAR (DATE_OF_BIRTH, 'DDMM') 35  ; Table created.

It succeed.

But When I tried to create view the same way originated from oe to hr schema, I encountered error

Dag> Create view hr.v_birthday_min2_plus7  2  as  3  SELECT CUST_FIRST_NAME,  4         CUST_LAST_NAME,  5         CUST_EMAIL,  6         DATE_OF_BIRTH,  7         CASE TO_NUMBER (TO_CHAR (DATE_OF_BIRTH, 'DD'))  8              - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')))  9            WHEN -2 10            THEN 11               'Two days ago' 12            WHEN -1 13            THEN 14               'One day ago' 15            WHEN 1 16            THEN 17               'Two days ago' 18            WHEN 2 19            THEN 20               'Two days in the future' 21            WHEN 4 22            THEN 23               '4 days in the future' 24            WHEN 5 25            THEN 26               '5 days in the future' 27            ELSE 28               'Within 7 days from today' 29         END 30            AS azz 31  FROM   oe.customers 32  WHERE  TO_NUMBER (TO_CHAR (DATE_OF_BIRTH, 'DD')) 33         - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD'))) BETWEEN -2 34                                                     AND  7 35  ORDER BY TO_CHAR (DATE_OF_BIRTH, 'DDMM') 36  ;FROM   oe.customers          *ERROR at line 31:ORA-00942: table or view does not exist

I didn’t unterstand the error:

FROM   oe.customers

ERROR at line 31:

ORA-00942: table or view does not exist

Which table? It exists on oe schema

Report Saldo Aset

2010-02-09T16:00:00.002+07:00

Saya mempunyai data transaksi menurut tanggal (tgl_buku) seperti di bawah ini.

KD	MONTHS	SEMESTER	RPH_ASET	TGL_BUKU	NO_REG_ASSIGN	JNS_TRN
5010101001	7	2	500000000	7/18/2007	Y	503
5010101004	8	2	500000000	8/10/2007		503
5010101005	9	2	300000000	9/11/2007		502
5010101004	4	1	50000000	4/15/2008		503
5010101003	8	2	750000000	8/10/2008	Y	503
5010101003	8	2	70000000	8/12/2008		502
5010101003	11	2	251000000	11/17/2008		503

Objektif:

Menampilkan kumulatif untuk masing-masing saldo awal, mutasi tambahan, mutasi kurang dan saldo sekarang, yang dikelompokkan menurut kode barang (kd)

Dengan ketentuan sebagai berikut:

Saldo awal adalah semua nilai aset (RPH_Aset) tanggal sebelumnya (back date),
Mutasi tambah adalah nilai aset yang ada di periode pelaporan sekarang,
Mutasi kurang adalah nilai aset yang ada di periode pelaporan sekarang,
Kolom no_reg_assign akan menjadi indikator negatif nilai aset untuk tiap masa baik periode berjalan ataupun periode sebelumnya. Jika transaksi ber-no_reg_assign =Y maka transaksi tersebut merupakan mutasi berkurang. Sebaliknya jika null, akan menadi mutasi bertambah.
fddf
dfdfd

Sehingga diharapkan bentuk laprannya akan menjadi seperti tampilan berikut:

KD_BARA|URAIAN         |SALDO_AWAL|MUTASI_TAMBAHAN|MUTASI_KURANG|     SALDO
-------|---------------|----------|---------------|-------------|----------
5010101|Konstruksi     | 850000000|     1071000000|   -750000000|1171000000
       |Dalam          |          |               |             |
       |Pengerjaan     |          |               |             |

Solusi:

Maka queri solusinya adalah sebagai berikut:

   1: undef p_tahun;   2: undef p_semester;   3: col uraian for a15 wor;   4: SELECT a1.kd_barang, B1.UR_SKEL Uraian,   5:        SUM (a1.saldo_awal + a1.minus_saldo_awal) saldo_awal,   6:        SUM (a1.Mutasi_Tambahan) Mutasi_Tambahan,   7:        SUM (a1.Mutasi_kurang) Mutasi_kurang,   8:        --SUM (Saldo) Saldo,   9:        sum(a1.saldo_awal + a1.minus_saldo_awal+a1.Mutasi_Tambahan+a1.Mutasi_kurang) Saldo  10: FROM   (  11: SELECT kd_barang,  12:                tahun,  13:                bulan,  14:                semester,  15:                saldo_awal AS saldo_awal,  16:                mutasi_tambah AS Mutasi_Tambahan,  17:                minus_saldo_awal,  18:                Mutasi_kurang AS Mutasi_kurang,  19:                saldo_awal + mutasi_tambah + Mutasi_kurang AS Saldo  20:         FROM   (  21: SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL  22:                           kd_barang,  23:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun,  24:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan,  25:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6)  26:                           semester,  27:                        a.RPH_ASET saldo_awal,  28:                        0 minus_saldo_awal,  29:                        0 mutasi_tambah,  30:                        0. mutasi_kurang,  31:                        A.TGL_BUKU,  32:                        b.no_reg_assign,  33:                        a.jns_trn  34:                 FROM   akt_master a, kdp b, akt_sskel s  35:                 WHERE      a.no_reg = b.no_reg  36:                        AND b.KODEBARANG = s.kd_brg  37:                        AND A.TGL_BUKU <= last_day(to_date(&&p_tahun||decode (&&p_semester,1,1,2,7),'RRRRMM'))  38:                        AND b.no_reg_assign IS NULL   39:                 UNION                  40:                 SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL kd_barang,  41:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun,  42:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan,  43:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6)  44:                           semester,  45:                        a.RPH_ASET saldo_awal,  46:                        0 minus_saldo_awal,  47:                        0 mutasi_tambah,  48:                        0. mutasi_kurang,  49:                        A.TGL_BUKU,  50:                        b.no_reg_assign,  51:                        a.jns_trn  52:                 FROM   akt_master a, kdp b, akt_sskel s  53:                 WHERE      a.no_reg = b.no_reg  54:                        AND b.KODEBARANG = s.kd_brg  55:                        AND A.TGL_BUKU <= last_day(to_date(&&p_tahun||decode (&&p_semester,1,1,2,7),'RRRRMM'))  56:                        AND b.no_reg_assign IS NOT NULL                                               57:                 UNION                  58:                 SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL kd_barang,  59:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun,  60:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan,  61:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6)  62:                           semester,  63:                        0 saldo_awal,  64:                        -a.RPH_ASET minus_saldo_awal,  65:                        0 mutasi_tambah,  66:                        0. mutasi_kurang,  67:                        A.TGL_BUKU,  68:                        b.no_reg_assign,  69:                        a.jns_trn  70:                 FROM   akt_master a, kdp b, akt_sskel s  71:                 WHERE      a.no_reg = b.no_reg  72:                        AND b.KODEBARANG = s.kd_brg  73:                        AND A.TGL_BUKU <= last_day(to_date(&&p_tahun||decode (&&p_semester,1,1,2,7),'RRRRMM'))                         74:                        AND b.no_reg_assign IS NOT NULL   75:                 UNION                               76:                 SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL kd_barang,   77:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun,  78:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan,  79:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6)  80:                           semester,  81:                        0 saldo_awal,  82:                        0 minus_saldo_awal,  83:                        a.RPH_ASET mutasi_tambah,  84:                        0. mutasi_kurang,  85:                        A.TGL_BUKU,  86:                        b.no_reg_assign,  87:                        a.jns_trn  88:                 FROM   akt_master a, kdp b, akt_sskel s  89:                 WHERE      a.no_reg = b.no_reg  90:                        AND b.KODEBARANG = s.kd_brg  91:                        AND b.no_reg_assign IS NULL  92:                        and EXTRACT (YEAR FROM A.TGL_BUKU)=&&p_tahun  93:                        AND CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) = &&p_semester  94:                 UNION                  95:                 SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL kd_barang,  96:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun,  97:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan,  98:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6)  99:                           semester, 100:                        0 saldo_awal, 101:                        0 minus_saldo_awal, 102:                        a.RPH_ASET mutasi_tambah, 103:                        0. mutasi_kurang, 104:                        A.TGL_BUKU, 105:                        b.no_reg_assign, 106:                        a.jns_trn 107:                 FROM   akt_master a, kdp b, akt_sskel s 108:                 WHERE      a.no_reg = b.no_reg 109:                        AND b.KODEBARANG = s.kd_brg 110:                        AND b.no_reg_assign IS not NULL 111:                        and EXTRACT (YEAR FROM A.TGL_BUKU)=&&p_tahun 112:                        AND CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) = &&p_semester 113:                 UNION 114:                 SELECT KD_GOL || KD_BID || KD_KEL || KD_SKEL kd_barang, 115:                        EXTRACT (YEAR FROM A.TGL_BUKU) tahun, 116:                        EXTRACT (MONTH FROM A.TGL_BUKU) bulan, 117:                        CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) 118:                           semester, 119:                        0 saldo_awal, 120:                        0 minus_saldo_awal, 121:                        0 mutasi_tambah, 122:                        -a.RPH_ASET mutasi_kurang, 123:                        A.TGL_BUKU, 124:                        b.no_reg_assign, 125:                        a.jns_trn 126:                 FROM   akt_master a, kdp b, akt_sskel s 127:                 WHERE      a.no_reg = b.no_reg 128:                        AND b.KODEBARANG = s.kd_brg 129:                        AND b.no_reg_assign IS NOT NULL 130:                        and EXTRACT (YEAR FROM A.TGL_BUKU)=&&p_tahun 131:                        AND CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) = &&p_semester) 132:                        ) a1, akt_skel b1 133: where a1.kd_barang = B1.KD_GOL||B1.KD_BID||B1.KD_KEL||B1.KD_SKEL      134: GROUP BY a1.kd_barang, B1.UR_SKEL;                        135: cle col;

Dimana &&p_tahun=parameter tahun dan &&p_semester=parameter semester.

Hal yang agak rumit dari queri di atas adalah bagaimana mengolah dan menampilkan data saldo awal dengan menggunakan parameter yang sama dengan periode berjalan yaitu parameter tahun dan periode/semester berjalan.

Hal ini diakali dengan memberdayakan fungsi-fungsi seperti ceil, extract (year from <date>) seperti pada ilustrasi berikut ini:

130::                        and EXTRACT (YEAR FROM A.TGL_BUKU)=&&p_tahun

131:                        AND CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) = &&p_semester)

112:                        AND CEIL (EXTRACT (MONTH FROM A.TGL_BUKU) / 6) = &&p_semester

Maka reportnya adalah sebagai berikut:

SQL> @r_kdp_skel_1.sql
Enter value for p_tahun: 2008
Enter value for p_semester: 1

KD_BARA|URAIAN         |SALDO_AWAL|MUTASI_TAMBAHAN|MUTASI_KURANG|     SALDO
-------|---------------|----------|---------------|-------------|----------
5010101|Konstruksi     | 800000000|       50000000|            0| 850000000
       |Dalam          |          |               |             |
       |Pengerjaan     |          |               |             |

Elapsed: 00:00:00.04
SQL> @r_kdp_skel_1.sql
Enter value for p_tahun: 2007
Enter value for p_semester: 2

KD_BARA|URAIAN         |SALDO_AWAL|MUTASI_TAMBAHAN|MUTASI_KURANG|     SALDO
-------|---------------|----------|---------------|-------------|----------
5010101|Konstruksi     |         0|     1300000000|   -500000000| 800000000
       |Dalam          |          |               |             |
       |Pengerjaan     |          |               |             |

Elapsed: 00:00:00.03

Installing Flashplayer Plugins in Centos Linux

2010-01-26T17:04:00.002+07:00

Hello dear,

It’s been long since the last time I post an article to this blog.

Now, here I’m back on stage again. I’m going to show you how to make Flash images, like ones usually find on youtube runnning on Opera browser.

You must be thinking, “What is so special about it?". Of course nothing, just I think in Linux, to make it running, we have to do it with some tricks.

Hereby are the steps:

Download the plugin from Opera website or somewhere else,
copy the .so file into opera plugins, with the script below:

cp /home/bontor/installer/libflashplayer.so /user/lib/opera/plugins
Restart your Opera Browser
Done!

Anyway, may be you’re wondering how to figure out the opera plugins location path? This is how we do to figure it out:

Open you Opera Browser,
Navigate to plugins folder by type : “Opera: About” on “Address Bar”
Press <Enter>
Then it will show some important information, including path for plugins.

Absolutely easy, right?

PS:

This tips has been tested and working on Centos 5.1 and Opera 10.10
.so is a Shared Library file type. It contains functions and headers that may be referenced by multiple C/C++ source files. For detail description bout this, navigate to this.

How to trace troubled session?

2009-12-05T11:18:00.001+07:00

I want to modify a table structure, with the syntax below:

alter table WBH_PKITWAN rename to WBH_PKITWAN_old;

but then it raised error:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

My suggestion, there are sessions which are accessing the table. So I figure out the session whith this query:

Column Name ID Data Type Null?

   1: SELECT SADDR,

   2:        SID,

   3:        SERIAL#,

   4:        OSUSER,

   5:        MACHINE

   6: FROM   v$session

   7: WHERE  username = 'SDAB';

8:

   9: SADDR           SID    SERIAL# OSUSER                         MACHINE

  10: -------- ---------- ---------- ------------------------------ ------------------

  11: 3432B2FC        142        197 Matador                        WORKGROUP\BONTOR

  12: 3432FE1C        146         17 Matador                        WORKGROUP\BONTOR

  13: 3433493C        150       1107 DAG2003\Administrator          WORKGROUP\DAG2003

A suspicious session is (sid, serial) = (150, 1107).

So, I kill the session with the DCL query below:

alter system kill session '148,1459';

Unix AIX: baca supaya tahu, Belajar supaya pintar.

2009-11-26T09:19:00.002+07:00

Berikut ini adalah links topik tentang AIX Variant UNIX:

Unix Command

IBM AIX Official Documentation

AIX Unix OS

solaris/ksh

comp.unix.aix Frequently Asked Questions

comp.unix.aix Frequently Asked Questions Full Page

AIX Forum

Tool

X_Window_System

09.09.09

2009-09-09T19:32:00.004+07:00

I realized the door was open and I was standing in the doorway, but my inertia was keeping something good from happening. I thought about a passage in the Book of Mormon that says “a natural man is an enemy to God,” and the principle behind it. If you drift along, seeking only the pleasure of the moment and staying safely within your natural comfort zone, you do not accomplish much. Good things happen when you push yourself outside of your comfort zone, doing what is difficult but what you know deep inside is the right thing to do.

Saya mulai berkenalan dengannya 9 tahun yang lalu, di Rumbai Riau. Saya menjalani KP di t4 tersebut.
Saya tidak ingin mendapatkan sesuatu dengan gampang dan tidak berkesan, sebaliknya, saya lebih tertarik untuk mencoba hal yang agak berkelok, dan membutuhkan effort yang tidak seadanya. Sehingga saya memutuskan untuk menyelesaikan Kerja Praktek di salah satu perusahaan minyak di daerah tersebut.

Sampai sekarang, setelah lebih kurang 9 tahun saya mengenalnya, dan saya bahkan bekerja dengannya, saya belum memahami sepenuhnya dia, cara kerjanya.

Satu hal yang membuat saya tetap bergairah dengannya adalah bahwa dia selalu menyimpan banyak resources dan teknologi yang belum bisa saya pahami.

Those questions at 1700 Somewhere

2009-08-20T12:28:00.004+07:00

Tell me anything about Oracle 10g Architecture

ORA-1555

What is Archivelog,

What is Redo log

synonim

What is the different of both truncate vs delete?

AWR, bagaimana Anda memanfaatkan report ini untuk mengetahui masalah dalam database?

What is the size of database u were working on? 1 tera?

Tell me anything about block size, (table block size)

Export/import

(unix) Copy one folder and subfolder to other location

Write down indexing hints, something like select /* hints *%&%^ */ from...

Tell some oracle dB packages you are familiar with.

The difference betweeen Unique and Primary Key

Indexing, Btree, Bitmap

There is a mandatory file should be exists on clients to estabilish connection to Oracle dB server, what is it?

.. ..

Is DML not Allowed in Trigger?

2009-08-05T10:17:00.002+07:00

I was spending quite lots a time to working of some kind of automation where table will triggerring trigger to create table. But anyhow, it doesn't work.

Does anyone have a clue?

   1: Create or replace Procedure X_DDL_Gen_Table (p_table_name Varchar2)   2:   -- ------------------------------------------------------------   3:   -- Versi 1.0    4:   -- Created 2009.08.05 by Bontor TP.   5:   -- Fungsi:   6:   -- DDL Klone table temp dengan paramater table asal     7:   -- ------------------------------------------------------------   8: is   9:    CURSOR c_list_tab_col (v_table_name Varchar2)  10:    IS  11:         SELECT   COLUMN_ID,  12:                  column_name,  13:                  DATA_TYPE,  14:                  DATA_TYPE_MOD,  15:                  data_length  16:           FROM   user_tab_columns  17:          WHERE   table_name = v_table_name--'AKT_MASTER'                                          --  18:       ORDER BY   COLUMN_ID;  19:    20:    TYPE t_tab_kol  21:    IS  22:       TABLE OF c_list_tab_col%ROWTYPE  23:          INDEX BY BINARY_INTEGER;  24:    25:    t_kl          t_tab_kol;  26:    27:    v_kol         VARCHAR2 (2000);  28:    st_ddl        VARCHAR2 (2200);  29:    --m_inserting   INTEGER := 0;  30:    n_cn          INTEGER;  31:    n_cacah_kol   INTEGER;  32:    v_jeda_koma   VARCHAR2 (4);  33:    v_tipe_data   VARCHAR2 (50);  34: BEGIN  35:    OPEN c_list_tab_col(p_table_name);  36:    37:    FETCH c_list_tab_col BULK COLLECT INTO   t_kl;  38:    39:    v_kol := '';  40:    41:    SELECT   MAX (COLUMN_ID)  42:      INTO   n_cacah_kol  43:      FROM   user_tab_columns  44:     WHERE   table_name = p_table_name;  45:    46:    n_cn := 0;  47:    48:    FOR i IN t_kl.FIRST .. t_kl.LAST  49:    LOOP  50:       n_cn := n_cn + 1;  51:    52:       IF n_cn < n_cacah_kol  53:       THEN  54:          v_jeda_koma := ', ';  55:       ELSE  56:          v_jeda_koma := '';  57:       END IF;  58:    59:       IF t_kl (i).data_type = 'DATE'  60:       THEN  61:          v_tipe_data := '';  62:       ELSE  63:          v_tipe_data := '(' || t_kl (i).data_length || ')';  64:       END IF;  65:    66:       v_kol :=  67:          v_kol || t_kl (i).column_name || ' ' || t_kl (i).data_type || v_tipe_data || v_jeda_koma;  68:    END LOOP;  69:    70:    st_ddl := 'Create table '||p_table_name||'_TEMP '|| '(' || CHR (10) || v_kol || CHR (10) || ')';  71:    --m_inserting := 1;  72:    73:    --IF m_inserting = 1  74:    --THEN  75:       BEGIN  76:          EXECUTE IMMEDIATE st_ddl;  77:    78:          pl (st_ddl);  79:       EXCEPTION  80:          WHEN OTHERS  81:          THEN  82:          dbms_output.put(st_ddl);  83:             pl (SQLCODE || ', ' || SQLERRM);  84:       END;  85:    --END IF;  86: END X_DDL_Gen_Table;

Then I call this Proc from trigger below:

   1: CREATE OR REPLACE TRIGGER SIMAK_TEST.Trg_Gen_Tab_Temp   2: BEFORE INSERT ON Gen_Tab_Temp FOR EACH ROW   3: -- ------------------------------------------------------------   4: -- Versi 1.0    5: -- Created 2009.08.05 by Bontor TP.   6: -- Fungsi:   7: -- Eksekusi Prosedur X_DDL_Gen_Table     8: -- ------------------------------------------------------------   9: DECLARE  10:    11: m_inserting integer :=0;  12: n_cn integer; n_cacah_kol integer;v_jeda_koma Varchar2(4);  13: v_tipe_data Varchar2(50);  14: BEGIN  15:    16: IF INSERTING THEN  17:       BEGIN  18:          X_DDL_Gen_Table ('TB_DT_TEKNIS_FASLUT');  19:          pl (:new.table_name);  20:       EXCEPTION  21:          WHEN OTHERS  22:          THEN  23:             pl (SQLCODE || ', ' || SQLERRM);  24:       END;  25: END IF;  26:    27: END Trg_Gen_Tab_Temp;

I put the trigger before insert mode on a table say “Gen_Tab_Temp”, I insert a row:

   1: Dag> insert into GEN_TAB_TEMP(table_name) values ('TB_DT_TEKNIS_FASLUT');   2: Create table TB_DT_TEKNIS_FASLUT_TEMP (   3: NO_REG VARCHAR2(25), KD_KELOMPOK CHAR(1), KD_KOMODITI CHAR(2), JENIS CHAR(50), STRUKTUR_KONSTRUKSI VARCHAR2(200), TIPE_KONSTRUKSI CHAR(1), PANJANG_DIMENSI NUMBER(22), LEBAR_DIMENSI NUMBER(22), PASANG_   4: LAUT NUMBER(22), SURUT_LAUT NUMBER(22), DAYA_MUAT NUMBER(22), DAYA_DUKUNG NUMBER(22), DAYA_BENTUR NUMBER(22), JENIS_LISTRIK VARCHAR2(50), FREKUENSI_LISTRIK NUMBER(22), TEGANGAN NUMBER(22), KAPASITAS_L   5: ISTRIK NUMBER(22), JUML_TANGGA_DATAR NUMBER(22), JUML_TANGGA_TEGAK NUMBER(22), FUEL_HYDRANT_AIR_LAUT NUMBER(22), MOBILE_PMK NUMBER(22), PMK_PORTABLE NUMBER(22), KEMAMPUAN_AIR_TAWAR NUMBER(22), TEKANAN   6: _AIR_TAWAR NUMBER(22), KUALITAS_AIR_TAWAR VARCHAR2(50), JUML_KRAN NUMBER(22), TONGKANG NUMBER(22), KETERANGAN VARCHAR2(100), CREATED_DATE DATE, CREATED_BY VARCHAR2(15), UPDATED_DATE DATE, UPDATED_BY V   7: ARCHAR2(15)   8: )-4092, ORA-04092: cannot COMMIT in a trigger   9: TB_DT_TEKNIS_FASLUT  10:    11: 1 row created.

DML is done successful, row is inserted, But not for DML. And, see… there is a error on line 8: which says

-4092, ORA-04092: cannot COMMIT in a trigger.

So, can’t we use DDL on trigger?

Customizing SQL*PlusW : Changing The Font

2009-07-28T12:44:00.002+07:00

Are you boresome with the appearance of your SQL*PlusW? Try tis out.

I came accross some interesting tips on customizing windows version of SQL*Plus from a site http://www.oracleadvice.com. (Unfortunately the site has been expired).

Here the tips:

Go to registry, then to node HKLM>SOFTWARE>ORACLE>{Oracle_Home}.

Then Create a new registry value called SQLPLUS_FONT of type REG_EXPAND_SZ and set it to your favourite fixed-width font, Eg. Courier.

Create a new registry value called SQLPLUS_FONT_SIZE of type REG_EXPAND_SZ and set it to the size you want (for me small is beautiful, so I set it to 12).

When you done with those steps, the registry structure on the node will be seen like figure below:

Your GUI on SQL*Plus will be seen like this:

How to add image link in Comments Pane?

2009-06-01T08:20:00.002+07:00

I want to add this pic in comments Pane on Blogger

When I did so, the error occurs as follow:

   1: Your HTML cannot be accepted: Tag is not allowed: <img alt="Barcelona" src="http://www.fcbarcelona.cat/web/english/splashhome/promo_fcb-eng.jpg" border="0" />

Is it possible to do so? Anyone has idea?

Barca wins UEFA Championship 2009

2009-05-28T09:13:00.002+07:00

Pep enthused by best season if not best team

AFP - 28 May 2009 00:55

ROME (AFP) - Barcelona coach Pep Guardiola hailed his team's achievement this season in winning the treble as the greatest of all but he stopped short of calling his team themselves the best after their 2-0 Champions League final victory over holders Manchester United here on Wednesday.

Barca added European club football's premier trophy to their domestic league and cup double, a feat United themselves had already achieved in 1999, with goals from Samuel Eto'o on 10 minutes and Lionel Messi 20 minutes from time.

But the Catalans' coach, who also won the European Cup as a player with Barca in 1992, said the manner of his team's triumph in scoring more than 150 goals this season made their campaign stand out.

"I'm leaving tomorrow, right away, I can't do anything else," joked the 38-year-old, who became the youngest ever Champions League winning coach in his first season as a boss.

"We're very, very happy, we're delirious and I'd like to congratulate the whole club.

"We're not the best team in history but we have played the best season in history. To win three titles and how we did it.

"We had many problems (tonight) and there was so much pressure but Samuel's goal was great and then Messi went back into midfield because we wanted to be superior in possession and with also Thierry Henry in midfield; we had some problems but I'm very happy."

Guardiola revealed that he had learned from Barca's defeat to United in last season's semi-final and switched around his attacking triumvirate accordingly.

Back then they were stifled over the two legs and failed to score a goal but this time Guardiola brought Messi in off the right flank into the middle and switched Cameroon striker Eto'o out wide.

"At the begining of the game we had a few problems, we didn't expect United to pressure us so high up the pitch," he revealed.

"We had problems in our build-up play and we have to recognise that they had a big chance with (Cristiano) Ronaldo's free-kick (on two minutes).

"But after (Andres) Iniesta passed for Eto'o to score we tried to keep the ball more and bring it inside more.

"Last year we saw that Leo (Messi) had difficulties with (United left-back Patrice) Evra so we put him inside with Xavi and Iniesta and we had one more player in the middle."We had chances and they did too but in the end we had control of the ball. For us it's a disaster if we don't have the ball so we have to have it and keep it and move it well.

"They tried to keep the ball with (Nemanja) Vidic, (Rio) Ferdinand and (Michael) Carrick, who's a very good player.

"They tried to keep it and also tried to counter with Ronaldo and (Wayne) Rooney but we controlled the game very well.

"We told our defence that it was important when we were attacking for them to pay close attention to Ronaldo and Rooney and (Carlos) Tevez and (Dimitar) Berbatov in the second half and they did this."

Unpivoting (Part -1)

2009-05-13T15:23:00.002+07:00

Setelah melanglangbuana kesana kemari, saya menemukan salah satu cara untuk boosting performance query Ini. Metode yang saya pakai adalah unpivoting.

Pengantar

Saya punya tabel

   1: SELECT T_ZONE_CODE zone_kd, kelurahan, kecamatan, kabupaten, Prov

   2: FROM Ref_Area_Locator

   3: where T_ZONE_CODE is not null

   4: order by T_ZONE_code;

   1: zone_kd kelurahan kecamatan  kabupaten Prov

   2: ..

   3: AG14    {null}    3403041    3403     {null}

   4: AG15    {null}    3204090    3204     {null}

   5: AG16    {null}    3204110    3204     {null}

   6: AG17    {null}    3204080    3204     {null}

   7: AG18    {null}    3204160    3204     {null}

   8: AG19    {null}    {null}    {null}    {null}

   9: AG20    {null}    {null}    {null}    {null}

  10: AG21    {null}    3204030   {null}    {null}

  11: AH01    {null}    {null}    {null}    {null}

  12: AH02    {null}    {null}    {null}    {null}

  13: AH03    {null}    {null}     3205     {null}

  14: AH04    {null}    3205280    3205     {null}

  15: AH05    {null}    3203060    3203     {null}

  16: AH06    {null}    {null}    {null}    {null}

  17: AH07    {null}    3206110    3206     {null}

  18: ..

Saya ingin menyusun kolom kelurahan, kecamatan, kabupaten, Prov menjadi satu kolom, sehingga data kolom menjadi row.

Maka pendekatannya adalah dengan query sebagai berikut:

   1: SELECT lokasi, zone_kd

   2: FROM(SELECT T_ZONE_CODE zone_kd, kelurahan, kecamatan, kabupaten, Prov

   3:      FROM Ref_Area_Locator

   4:      WHERE 1 = 1) p

   5:      UNPIVOT (lokasi FOR T_ZONE_CODE

   6:              IN (Prov, kabupaten, kecamatan, kelurahan)) AS unpivot_Area

   7: WHERE isnumeric(lokasi) = 1

   8: ORDER BY 1, 2;

Yang menghasilkan row seperti pada penggalan ilustrasi berikut:

   1: lokasi    zone_kd

   2: ..          ..

   3: 3171       AA03

   4: 3171       AA05

   5: 3171       AB01

   6: 3171       DJ23

   7: 3171       XA01

   8: 3171010    DJ23

   9: 3171040    XA01

  10: 3171040001 XA01

  11: 3172       AA02

  12: 3172       AC22

  13: 3172012    AC22

  14: 3173       AA01

  15: 3174       AA04

  16: 3174       AB15

  17: 3174020    AB15

  18: ..          ..

Bandingkan juga dengan cara tradisional, seperti skrip d bawah ini:

   1: SELECT a.Lokasi, LTrim(RTrim(a.T_ZONE_CODE)) kode_Zona

   2: FROM(SELECT Prov lokasi, T_ZONE_CODE FROM Ref_Area_Locator

   3:     UNION

   4:     SELECT Kabupaten lokasi, T_ZONE_CODE FROM Ref_Area_Locator

   5:     UNION

   6:     SELECT Kecamatan lokasi, T_ZONE_CODE FROM Ref_Area_Locator

   7:     UNION

   8:     SELECT Kelurahan lokasi, T_ZONE_CODE FROM Ref_Area_Locator) a

   9: WHERE isnumeric(lokasi) = 1

  10: ;

11:

  12: 0:00:00.125

Dari segi performance, untuk komposisi data seperti di atas memang tidak ada perbedaan yang signifikan. Tapi saya belum mencoba untuk data rekor yang besar.

Unpivoting (Part 2)

2009-05-12T09:36:00.002+07:00

This is the best solution I found for Boosting Query Script I tried to figure out few days ago:

   1: SELECT fiscper, key_Figure, Nilai   2: FROM(SELECT rt.FISCPER,   3:             rt.TPW_LN_11,   4:             rt.TPW_LN_15,   5:             rt.TPW_LN_21,   6:             rt.TPW_LN_22,   7:             rt.TPW_LN_23,   8:             rt.TPW_LN_29,   9:             rt.TPW_LN_31,  10:             rt.TPW_LN_35,  11:             rt.TPW_LN_36,  12:             rt.TPW_LN_39,  13:             rt.TPW_LN_40,  14:             rt.TPW_LN_44,  15:             rt.TPW_LN_46,  16:             rt.TPW_LN_50,  17:             rt.TPW_LN13A,  18:             rt.TPW_LN44A  19:      FROM raptor_tpm rt  20:      WHERE rtrim(ltrim(tgeoarea)) = 'ID34CA33') p  21:      UNPIVOT (nilai FOR key_Figure  22:              IN (TPW_LN_11,  23:                  TPW_LN_15,  24:                  TPW_LN_21,  25:                  TPW_LN_22,  26:                  TPW_LN_23,  27:                  TPW_LN_29,  28:                  TPW_LN_31,  29:                  TPW_LN_35,  30:                  TPW_LN_36,  31:                  TPW_LN_39,  32:                  TPW_LN_40,  33:                  TPW_LN_44,  34:                  TPW_LN_46,  35:                  TPW_LN_50,  36:                  TPW_LN13A,  37:                  TPW_LN44A)) AS unpvt;

The basic idea is utilizing unpivoting function in query in SQL Server 2005.

fiscper	key_Figure	Nilai
2007005	TPW_LN_11	2295183.6
2007005	TPW_LN_15	2530439.91
2007005	TPW_LN_21	2295183.6
2007005	TPW_LN_22	2
2007005	TPW_LN_23	8
2007005	TPW_LN_29	1894747.72
2007005	TPW_LN_31	5
2007005	TPW_LN_35	6
2007005	TPW_LN_36	3
2007005	TPW_LN_39	1857695.02
2007005	TPW_LN_40	10
2007005	TPW_LN_44	1847854.42
2007005	TPW_LN_46	7
2007005	TPW_LN_50	1297841.62
2007005	TPW_LN13A	2295183.6
2007005	TPW_LN44A	12
2007006	TPW_LN_11	306024.48
2007006	TPW_LN_15	337391.98
2007006	TPW_LN_21	306024.48
2007006	TPW_LN_22	2
..	..	..