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
..	..	..

Akuisisi Oracle atas SUN: Dampak Panjang.

2009-04-24T13:07:00.002+07:00

OK saya bukan sok tau, tapi saya memprediksi akan terjadi dampak yang panjang atas akuisisi tersebut di atas. Oracle-Sun-MySQL-WordPress ..—..—, ...:( seperti pada artikel berikut ini. Kemaren di Oracle Developer Day yang diadakan di Hotel Shangri-La Rabu kemaren, saya bertanya kepada Panelis spt Blair Layton dan orang Oracle Indonesia tentang dampak panjang dari Akuisisi Oracle atas SUN. Saya bertanya tentang aset sofware yaitu: MySQL dan Solaris. “Bagaimana masa depan MySQL, apakah dia masih free dan open source seperti yang selama ini terjadi?”

Tapi, saya tidak mendapat jawaban, dan sepertinya memang hal itu adalah management issue.

Apakah JDeveloper akan menjadi standar teknologi atau IDe untuk JAVA? Jadi, tidak ada lagi Sun Solaris, tapi sebaliknya kita harus membiasakan diri untuk mengucapkan atau mendengar Oracle Solaris, hmmm…..

Oracle Acquire SUN USD $7.4 billion

2009-04-21T10:45:00.002+07:00

Oracle-Sun Merger Creates New Data Storage Player

April 20, 2009 By Paul Shread

Today's surprising news that Oracle (NASDAQ: ORCL) will acquire Sun Microsystems (NASDAQ: JAVA) means that the data storage industry suddenly has a new — and formidable — player.

"Our customers have been asking us to step up to a broader role to reduce complexity, risk and cost by delivering a highly-optimized standards-based product stack," Oracle president Charles Phillips wrote in a Letter to partners and customers. "Oracle plans to deliver these benefits by offering a broad range of products, including servers and storage, with all the integrated pieces: hardware operating system, database, middleware and applications."

The deal is much more likely to clear antitrust hurdles than Sun's proposed merger with IBM (NYSE: IBM), which fell apart two weeks ago.

But Wedbush Morgan analyst Kaushik Roy doesn't necessarily think that's a good thing.

"IBM was a better strategic fit for Sun," he said. "Now Oracle is getting into totally new markets in which they have no expertise or history. I am skeptical.

"Oracle is more likely to hold on to the entire storage business," Roy added. "Sun bought StorageTek and destroyed the company because of poor execution. But Oracle has been much better in execution, and it is very likely that the current storage group within Sun will have a better opportunity to grow."

But the deal could also mean a rocky relationship between Oracle and other storage players, like EMC (NYSE: EMC). "Oracle and EMC had a very good relationship," said Roy. "But now with Oracle and Sun selling HDS at the high end, it would compete with EMC. We will wait to see how those relationships pan out."

While Oracle could eventually wind up selling Sun's storage hardware business, most analysts see Oracle staying in the hardware business at least for now.

"Right now I'm going on the assumption that Oracle runs Sun as Sun for a while before they start restructuring," said Illuminata analyst John Webster. "On the call this morning, [Oracle CEO] Larry Ellison mentioned Sun's strong position in storage, including archival storage, so I assume that everything — even tape — stays put."

In a blog posting, Webster wondered what Oracle's involvement will mean for the Sun-NetApp (NASDAQ: NTAP) patent dispute over the ZFS file system.

Webster said the deal could renew speculation that Cisco (NASDAQ: CSCO) might acquire EMC, as Cisco will offer networking and servers but not storage and virtualization, which is offered by EMC subsidiary VMware (NYSE: VMW). And he said Brocade (NASDAQ: BRCD), Cisco's competitor in storage and network switches, could become a takeover target for Oracle or IBM (NYSE: IBM).

Stifel Nicolaus analyst Aaron Rakers said the Oracle-Sun deal could also renew takeover speculation about NetApp, and he also wondered about Pillar Data Systems, the storage company funded by Ellison.

Rakers also noted that Sun's Open Storage efforts are another question mark in the deal; they've been a rare bright spot for the company in recent quarters.

"We have often been intrigued by some of Sun's storage technologies," Rakers wrote in a research note today. "However, we have continued to see limited traction from an execution standpoint."

Enterprise Strategy Group analyst Brian Babineau said data storage "had very little to do with this deal," which he said was about Solaris, Java and MySQL. But given the amount of data stored in Oracle databases, storage vendors have reason to worry about potential competition from Oracle, he said. Up until now, Oracle has approached the storage industry as a partner, in offerings such as the HP Oracle Database Machine.

If nothing else, Babineau said the presence of Oracle could enliven the storage market. The "hasty call with no Q&A" that followed the acquisition announcement suggests just how quickly the deal got done — and just how aggressively Oracle is capable of moving.

"If you're NetApp, EMC or 3PAR, this is not a warm and fuzzy feeling," he said.

Oracle officials expect the deal to close this summer.

Help: Boosting this Query Script

2009-04-17T16:31:00.002+07:00

Help me boosting this query:

   1: SELECT kd.id,   2:        kd.value Key_Figure,   3:        sum(dto.va_bulk_curr_year) va_bulk,   4:        sum(dto.va_bag_curr_year) va_bag,   5:        sum(dto.Portland_curr_year) Portland,   6:        sum(dto.Portland_prev_year) Last_Year,   7:        sum(dto.Portland_prev_month) Last_Month   8: FROM   KeyFigure_DashBoard kd   9:        LEFT JOIN (SELECT isnull(data.id, 99) id,  10:                          sg.tcustsegm Segment,  11:                          isnull(sum(va_bulk_curr_year), 0) va_bulk_curr_year,  12:                          isnull(sum(va_bag_curr_year), 0) va_bag_curr_year,  13:                          isnull(sum(va_bulk_curr_year), 0) + isnull  14:                           (sum  15:                           (va_bag_curr_year), 0)  16:                           Portland_curr_year,  17:                          isnull(sum(va_bulk_prev_year), 0) va_bulk_prev_year,  18:                          isnull(sum(va_bag_prev_year), 0) va_bag_prev_year,  19:                          isnull(sum(Portland_prev_year), 0) Portland_prev_year,  20:                          isnull(sum(va_bulk_prev_month), 0) va_bulk_prev_month,  21:                          isnull(sum(va_bag_prev_month), 0) va_bag_prev_month,  22:                          isnull(sum(Portland_prev_month), 0)  23:                           Portland_prev_month  24:                   FROM   _TPM_TCUSTSEGM sg  25:                          LEFT JOIN (SELECT dt0.tahun,  26:                                     dt0.Semester,  27:                                     dt0.Periode,  28:                                     dt0.segment,  29:                                     dt0.id,  30:                                     dt0.Nilai_bulk va_bulk_curr_year,  31:                                     dt0.Nilai_Bag va_bag_curr_year,  32:                                     dt0.Nilai_bulk + dt0.Nilai_Bag  33:                                      Portland_curr_year,  34:                                     0 va_bulk_prev_year,  35:                                     0 va_bag_prev_year,  36:                                     0 Portland_prev_year,  37:                                     0 va_bulk_prev_month,  38:                                     0 va_bag_prev_month,  39:                                     0 Portland_prev_month  40:                                     FROM(SELECT v_bulk.Tahun,  41:                                     v_bulk.Semester,  42:                                     v_bulk.Quarter,  43:                                     v_bulk.Periode,  44:                                     v_bulk.Week,  45:                                     v_bulk.tgl,  46:                                     v_bulk.HINCOTERM,  47:                                     v_bulk.TINVTYP,  48:                                     v_bulk.Segment,  49:                                     da.IdVillage distribusi_area,  50:                                     v_bulk.Nilai Nilai_bulk,  51:                                     0 Nilai_Bag,  52:                                     v_bulk.id,  53:                                     v_bulk.tgeoarea,  54:                                     lk.lokasi, da.idVillage  55:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bulk,  56:                                     (SELECT a.Lokasi,  57:                                     ltrim(rtrim  58:                                      (a.T_ZONE_CODE))  59:                                      kode_Zona  60:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE  61:                                     FROM Ref_Area_Locator  62:                                     UNION  63:                                     SELECT Kabupaten  64:                                      lokasi,  65:                                     T_ZONE_CODE  66:                                     FROM Ref_Area_Locator  67:                                     UNION  68:                                     SELECT Kecamatan  69:                                      lokasi,  70:                                     T_ZONE_CODE  71:                                     FROM Ref_Area_Locator  72:                                     UNION  73:                                     SELECT Kelurahan  74:                                      lokasi,  75:                                     T_ZONE_CODE  76:                                     FROM Ref_Area_Locator) a  77:                                     WHERE len(ltrim  78:                                      (rtrim(a.lokasi))) !=  79:                                      0) lk,  80:                                     _DISTRIBUTOR_AREA da  81:                                     WHERE     v_bulk.hpackgtyp = '1'  82:                                     AND lk.kode_Zona = v_bulk.tgeoarea  83:                                     AND v_bulk.IDDISTRIBUTOR = da.IDDISTRIBUTOR                                      84:                                     UNION  85:                                     SELECT v_bag.Tahun,  86:                                     v_bag.Semester,  87:                                     v_bag.Quarter,  88:                                     v_bag.Periode,  89:                                     v_bag.Week,  90:                                     v_bag.tgl,  91:                                     v_bag.HINCOTERM,  92:                                     v_bag.TINVTYP,  93:                                     v_bag.Segment,  94:                                     v_bag.iddistributor,  95:                                     0 Nilai_bulk,  96:                                     v_bag.Nilai Nilai_Bag,  97:                                     v_bag.id,  98:                                     v_bag.tgeoarea,  99:                                     lk.lokasi, da.idVillage 100:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bag, 101:                                     (SELECT a.Lokasi, 102:                                     ltrim(rtrim 103:                                      (a.T_ZONE_CODE)) 104:                                      kode_Zona 105:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE 106:                                     FROM Ref_Area_Locator 107:                                     UNION 108:                                     SELECT Kabupaten 109:                                      lokasi, 110:                                     T_ZONE_CODE 111:                                     FROM Ref_Area_Locator 112:                                     UNION 113:                                     SELECT Kecamatan 114:                                      lokasi, 115:                                     T_ZONE_CODE 116:                                     FROM Ref_Area_Locator 117:                                     UNION 118:                                     SELECT Kelurahan 119:                                      lokasi, 120:                                     T_ZONE_CODE 121:                                     FROM Ref_Area_Locator) a 122:                                     WHERE len(ltrim 123:                                      (rtrim(a.lokasi))) != 124:                                      0) lk, 125:                                     _DISTRIBUTOR_AREA da 126:                                     WHERE     v_bag.hpackgtyp = '2' 127:                                     AND lk.kode_Zona = v_bag.tgeoarea 128:                                     AND v_bag.IDDISTRIBUTOR = da.IDDISTRIBUTOR 129:                                     ) dt0 130:                                     WHERE dt0.tahun = 2008 131:   132: ---------------------Tahun ini 133:                                     UNION 134:   135: ---------------------Union 136:                                     SELECT dt0.tahun, 137:                                     dt0.Semester, 138:                                     dt0.Periode, 139:                                     dt0.segment, 140:                                     dt0.id, 141:                                     0 va_bulk_curr_year, 142:                                     0 va_bag_curr_year, 143:                                     0 Portland_curr_year, 144:                                     dt0.Nilai_bulk va_bulk_prev_year, 145:                                     dt0.Nilai_Bag va_bag_prev_year, 146:                                     dt0.Nilai_bulk + dt0.Nilai_Bag 147:                                      Portland_prev_year, 148:                                     0 va_bulk_prev_month, 149:                                     0 va_bag_prev_month, 150:                                     0 Portland_prev_month 151:                                     FROM(SELECT v_bulk.Tahun, 152:                                     v_bulk.Semester, 153:                                     v_bulk.Quarter, 154:                                     v_bulk.Periode, 155:                                     v_bulk.Week, 156:                                     v_bulk.tgl, 157:                                     v_bulk.HINCOTERM, 158:                                     v_bulk.TINVTYP, 159:                                     v_bulk.Segment, 160:                                     da.IdVillage distribusi_area, 161:                                     v_bulk.Nilai Nilai_bulk, 162:                                     0 Nilai_Bag, 163:                                     v_bulk.id, 164:                                     v_bulk.tgeoarea, 165:                                     lk.lokasi, da.idVillage 166:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bulk, 167:                                     (SELECT a.Lokasi, 168:                                     ltrim(rtrim 169:                                      (a.T_ZONE_CODE)) 170:                                      kode_Zona 171:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE 172:                                     FROM Ref_Area_Locator 173:                                     UNION 174:                                     SELECT Kabupaten 175:                                      lokasi, 176:                                     T_ZONE_CODE 177:                                     FROM Ref_Area_Locator 178:                                     UNION 179:                                     SELECT Kecamatan 180:                                      lokasi, 181:                                     T_ZONE_CODE 182:                                     FROM Ref_Area_Locator 183:                                     UNION 184:                                     SELECT Kelurahan 185:                                      lokasi, 186:                                     T_ZONE_CODE 187:                                     FROM Ref_Area_Locator) a 188:                                     WHERE len(ltrim 189:                                      (rtrim(a.lokasi))) != 190:                                      0) lk, 191:                                     _DISTRIBUTOR_AREA da 192:                                     WHERE     v_bulk.hpackgtyp = '1' 193:                                     AND lk.kode_Zona = v_bulk.tgeoarea 194:                                     AND v_bulk.IDDISTRIBUTOR = da.IDDISTRIBUTOR 195:                                     UNION 196:                                     SELECT v_bag.Tahun, 197:                                     v_bag.Semester, 198:                                     v_bag.Quarter, 199:                                     v_bag.Periode, 200:                                     v_bag.Week, 201:                                     v_bag.tgl, 202:                                     v_bag.HINCOTERM, 203:                                     v_bag.TINVTYP, 204:                                     v_bag.Segment, 205:                                     v_bag.iddistributor, 206:                                     0 Nilai_bulk, 207:                                     v_bag.Nilai Nilai_Bag, 208:                                     v_bag.id, 209:                                     v_bag.tgeoarea, 210:                                     lk.lokasi, da.idVillage 211:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bag, 212:                                     (SELECT a.Lokasi, 213:                                     ltrim(rtrim 214:                                      (a.T_ZONE_CODE)) 215:                                      kode_Zona 216:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE 217:                                     FROM Ref_Area_Locator 218:                                     UNION 219:                                     SELECT Kabupaten 220:                                      lokasi, 221:                                     T_ZONE_CODE 222:                                     FROM Ref_Area_Locator 223:                                     UNION 224:                                     SELECT Kecamatan 225:                                      lokasi, 226:                                     T_ZONE_CODE 227:                                     FROM Ref_Area_Locator 228:                                     UNION 229:                                     SELECT Kelurahan 230:                                      lokasi, 231:                                     T_ZONE_CODE 232:                                     FROM Ref_Area_Locator) a 233:                                     WHERE len(ltrim 234:                                      (rtrim(a.lokasi))) != 235:                                      0) lk, 236:                                     _DISTRIBUTOR_AREA da 237:                                     WHERE     v_bag.hpackgtyp = '2' 238:                                     AND lk.kode_Zona = v_bag.tgeoarea 239:                                     AND v_bag.IDDISTRIBUTOR = da.IDDISTRIBUTOR 240:                                     ) dt0 241:                                     WHERE dt0.tahun = 2008 - 1 ---------------------Tahun Lalu 242:                                     UNION ---------------------Union 243:                                     SELECT dt0.tahun, 244:                                     dt0.Semester, 245:                                     dt0.Periode, 246:                                     dt0.segment, 247:                                     dt0.id, 248:                                     0 va_bulk_curr_year, 249:                                     0 va_bag_curr_year, 250:                                     0 Portland_curr_year, 251:                                     0 va_bulk_prev_year, 252:                                     0 va_bag_prev_year, 253:                                     0 Portland_prev_year, 254:                                     dt0.Nilai_bulk va_bulk_prev_month, 255:                                     dt0.Nilai_Bag va_bag_prev_month, 256:                                     dt0.Nilai_bulk + dt0.Nilai_Bag 257:                                      Portland_prev_month 258:                                     FROM(SELECT v_bulk.Tahun, 259:                                     v_bulk.Semester, 260:                                     v_bulk.Quarter, 261:                                     v_bulk.Periode, 262:                                     v_bulk.Week, 263:                                     v_bulk.tgl, 264:                                     v_bulk.HINCOTERM, 265:                                     v_bulk.TINVTYP, 266:                                     v_bulk.Segment, 267:                                     da.IdVillage distribusi_area, 268:                                     v_bulk.Nilai Nilai_bulk, 269:                                     0 Nilai_Bag, 270:                                     v_bulk.id, 271:                                     v_bulk.tgeoarea, 272:                                     lk.lokasi, da.idVillage 273:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bulk, 274:                                     (SELECT a.Lokasi, 275:                                     ltrim(rtrim 276:                                      (a.T_ZONE_CODE)) 277:                                      kode_Zona 278:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE 279:                                     FROM Ref_Area_Locator 280:                                     UNION 281:                                     SELECT Kabupaten 282:                                      lokasi, 283:                                     T_ZONE_CODE 284:                                     FROM Ref_Area_Locator 285:                                     UNION 286:                                     SELECT Kecamatan 287:                                      lokasi, 288:                                     T_ZONE_CODE 289:                                     FROM Ref_Area_Locator 290:                                     UNION 291:                                     SELECT Kelurahan 292:                                      lokasi, 293:                                     T_ZONE_CODE 294:                                     FROM Ref_Area_Locator) a 295:                                     WHERE len(ltrim 296:                                      (rtrim(a.lokasi))) != 297:                                      0) lk, 298:                                     _DISTRIBUTOR_AREA da 299:                                     WHERE     v_bulk.hpackgtyp = '1' 300:                                     AND lk.kode_Zona = v_bulk.tgeoarea 301:                                     AND v_bulk.IDDISTRIBUTOR = da.IDDISTRIBUTOR 302:                                     UNION 303:                                     SELECT v_bag.Tahun, 304:                                     v_bag.Semester, 305:                                     v_bag.Quarter, 306:                                     v_bag.Periode, 307:                                     v_bag.Week, 308:                                     v_bag.tgl, 309:                                     v_bag.HINCOTERM, 310:                                     v_bag.TINVTYP, 311:                                     v_bag.Segment, 312:                                     v_bag.iddistributor, 313:                                     0 Nilai_bulk, 314:                                     v_bag.Nilai Nilai_Bag, 315:                                     v_bag.id, 316:                                     v_bag.tgeoarea, 317:                                     lk.lokasi, da.idVillage 318:                                     FROM HOLCIMFIX.dbo.V_raptor_tpm v_bag, 319:                                     (SELECT a.Lokasi, 320:                                     ltrim(rtrim 321:                                      (a.T_ZONE_CODE)) 322:                                      kode_Zona 323:                                     FROM(SELECT Prov lokasi, T_ZONE_CODE 324:                                     FROM Ref_Area_Locator 325:                                     UNION 326:                                     SELECT Kabupaten 327:                                      lokasi, 328:                                     T_ZONE_CODE 329:                                     FROM Ref_Area_Locator 330:                                     UNION 331:                                     SELECT Kecamatan 332:                                      lokasi, 333:                                     T_ZONE_CODE 334:                                     FROM Ref_Area_Locator 335:                                     UNION 336:                                     SELECT Kelurahan 337:                                      lokasi, 338:                                     T_ZONE_CODE 339:                                     FROM Ref_Area_Locator) a 340:                                     WHERE len(ltrim 341:                                      (rtrim(a.lokasi))) != 342:                                      0) lk, 343:                                     _DISTRIBUTOR_AREA da 344:                                     WHERE     v_bag.hpackgtyp = '2' 345:                                     AND lk.kode_Zona = v_bag.tgeoarea 346:                                     AND v_bag.IDDISTRIBUTOR = da.IDDISTRIBUTOR 347:                                     ) dt0 348:                                     WHERE dt0.tahun = 2008 - 1 --------------------Bulan Lalu 349:                                            --AND dt0.Periode = '01' 350:                                            AND convert 351:                                      (Varchar(8), left(dt0.Tgl, 6)) = 352:                                      convert(Varchar(4), '2008' - 1) + right 353:                                      (REPLICATE('0', 1) + convert 354:                                      (Varchar(20), month(dateadd 355:                                      (month, -1, convert 356:                                      (datetime, '2007' + '02' + '01')))), 2)) Data 357:                           ON sg.tcustsegm = data.segment 358:                   WHERE  distribusi_area LIKE '%33%'-- provinsi 359:                      AND data.distribusi_area LIKE '%%' -- municipal 360:                      AND data.distribusi_area LIKE '%%' -- DIstrict 361:                      AND data.distribusi_area LIKE '%%' -- Village                                                                               362:                   GROUP BY isnull(data.id, 99), sg.tcustsegm) dto 363:         ON kd.id = dto.id 364: GROUP BY kd.id, kd.value 365: ORDER BY kd.id;

Where v_raptor_tpm is a view:

   1: SELECT asli.*   2: FROM   (SELECT left(rt.tinvdate, 4) Tahun,   3:         CASE   4:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01'   5:         AND '06' THEN   6:           '1'   7:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07'   8:         AND '12' THEN   9:           '2'  10:         END  11:          AS Semester,  12:         CASE  13:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01'  14:         AND '03' THEN  15:           '1'  16:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04'  17:         AND '06' THEN  18:           '2'  19:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07'  20:         AND '09' THEN  21:           '3'  22:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10'  23:         AND '12' THEN  24:           '4'  25:         END  26:          AS Quarter,  27:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode,  28:         CASE  29:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1'  30:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2'  31:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3'  32:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4'  33:          ELSE '5'  34:         END  35:          AS Week,  36:         rt.TINVDATE tgl,  37:         rt.hpackgtyp,  38:         rt.HINCOTERM,  39:         rt.TINVTYP,  40:         rt.tcustsegm Segment,  41:         substring(rt.tsold_to, 4, 7) iddistributor,  42:         rt.TPW_LN_21 Nilai,  43:         1 id,  44:         right(rtrim(tgeoarea), 4) tgeoarea  45:         FROM raptor_tpm rt  46:         WHERE len(rt.tinvdate) = 8  47:         UNION  48:         SELECT left(rt.tinvdate, 4) Tahun,  49:         CASE  50:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01'  51:         AND '06' THEN  52:           '1'  53:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07'  54:         AND '12' THEN  55:           '2'  56:         END  57:          AS Semester,  58:         CASE  59:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01'  60:         AND '03' THEN  61:           '1'  62:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04'  63:         AND '06' THEN  64:           '2'  65:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07'  66:         AND '09' THEN  67:           '3'  68:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10'  69:         AND '12' THEN  70:           '4'  71:         END  72:          AS Quarter,  73:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode,  74:         CASE  75:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1'  76:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2'  77:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3'  78:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4'  79:          ELSE '5'  80:         END  81:          AS Week,  82:         rt.TINVDATE tgl,  83:         rt.hpackgtyp,  84:         rt.HINCOTERM,  85:         rt.TINVTYP,  86:         rt.tcustsegm Segment,  87:         substring(rt.tsold_to, 4, 7) iddistributor,  88:         rt.TPW_LN13A Nilai,  89:         2 id,  90:         right(rtrim(tgeoarea), 4) tgeoarea  91:         FROM raptor_tpm rt  92:         WHERE len(rt.tinvdate) = 8  93:         UNION  94:         SELECT left(rt.tinvdate, 4) Tahun,  95:         CASE  96:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01'  97:         AND '06' THEN  98:           '1'  99:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 100:         AND '12' THEN 101:           '2' 102:         END 103:          AS Semester, 104:         CASE 105:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 106:         AND '03' THEN 107:           '1' 108:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 109:         AND '06' THEN 110:           '2' 111:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 112:         AND '09' THEN 113:           '3' 114:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 115:         AND '12' THEN 116:           '4' 117:         END 118:          AS Quarter, 119:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 120:         CASE 121:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 122:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 123:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 124:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 125:          ELSE '5' 126:         END 127:          AS Week, 128:         rt.TINVDATE tgl, 129:         rt.hpackgtyp, 130:         rt.HINCOTERM, 131:         rt.TINVTYP, 132:         rt.tcustsegm Segment, 133:         substring(rt.tsold_to, 4, 7) iddistributor, 134:         rt.TPW_LN_22 Nilai, 135:         3 id, 136:         right(rtrim(tgeoarea), 4) tgeoarea 137:         FROM raptor_tpm rt 138:         WHERE len(rt.tinvdate) = 8 139:         UNION 140:         SELECT left(rt.tinvdate, 4) Tahun, 141:         CASE 142:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 143:         AND '06' THEN 144:           '1' 145:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 146:         AND '12' THEN 147:           '2' 148:         END 149:          AS Semester, 150:         CASE 151:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 152:         AND '03' THEN 153:           '1' 154:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 155:         AND '06' THEN 156:           '2' 157:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 158:         AND '09' THEN 159:           '3' 160:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 161:         AND '12' THEN 162:           '4' 163:         END 164:          AS Quarter, 165:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 166:         CASE 167:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 168:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 169:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 170:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 171:          ELSE '5' 172:         END 173:          AS Week, 174:         rt.TINVDATE tgl, 175:         rt.hpackgtyp, 176:         rt.HINCOTERM, 177:         rt.TINVTYP, 178:         rt.tcustsegm Segment, 179:         substring(rt.tsold_to, 4, 7) iddistributor, 180:         rt.TPW_LN_23 Nilai, 181:         4 id, 182:         right(rtrim(tgeoarea), 4) tgeoarea 183:         FROM raptor_tpm rt 184:         WHERE len(rt.tinvdate) = 8 185:         UNION 186:         SELECT left(rt.tinvdate, 4) Tahun, 187:         CASE 188:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 189:         AND '06' THEN 190:           '1' 191:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 192:         AND '12' THEN 193:           '2' 194:         END 195:          AS Semester, 196:         CASE 197:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 198:         AND '03' THEN 199:           '1' 200:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 201:         AND '06' THEN 202:           '2' 203:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 204:         AND '09' THEN 205:           '3' 206:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 207:         AND '12' THEN 208:           '4' 209:         END 210:          AS Quarter, 211:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 212:         CASE 213:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 214:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 215:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 216:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 217:          ELSE '5' 218:         END 219:          AS Week, 220:         rt.TINVDATE tgl, 221:         rt.hpackgtyp, 222:         rt.HINCOTERM, 223:         rt.TINVTYP, 224:         rt.tcustsegm Segment, 225:         substring(rt.tsold_to, 4, 7) iddistributor, 226:         rt.TPW_LN_29 Nilai, 227:         5 id, 228:         right(rtrim(tgeoarea), 4) tgeoarea 229:         FROM raptor_tpm rt 230:         WHERE len(rt.tinvdate) = 8 231:         UNION 232:         SELECT left(rt.tinvdate, 4) Tahun, 233:         CASE 234:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 235:         AND '06' THEN 236:           '1' 237:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 238:         AND '12' THEN 239:           '2' 240:         END 241:          AS Semester, 242:         CASE 243:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 244:         AND '03' THEN 245:           '1' 246:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 247:         AND '06' THEN 248:           '2' 249:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 250:         AND '09' THEN 251:           '3' 252:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 253:         AND '12' THEN 254:           '4' 255:         END 256:          AS Quarter, 257:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 258:         CASE 259:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 260:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 261:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 262:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 263:          ELSE '5' 264:         END 265:          AS Week, 266:         rt.TINVDATE tgl, 267:         rt.hpackgtyp, 268:         rt.HINCOTERM, 269:         rt.TINVTYP, 270:         rt.tcustsegm Segment, 271:         substring(rt.tsold_to, 4, 7) iddistributor, 272:         rt.TPW_LN_31 Nilai, 273:         6 id, 274:         right(rtrim(tgeoarea), 4) tgeoarea 275:         FROM raptor_tpm rt 276:         WHERE len(rt.tinvdate) = 8 277:         UNION 278:         SELECT left(rt.tinvdate, 4) Tahun, 279:         CASE 280:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 281:         AND '06' THEN 282:           '1' 283:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 284:         AND '12' THEN 285:           '2' 286:         END 287:          AS Semester, 288:         CASE 289:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 290:         AND '03' THEN 291:           '1' 292:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 293:         AND '06' THEN 294:           '2' 295:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 296:         AND '09' THEN 297:           '3' 298:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 299:         AND '12' THEN 300:           '4' 301:         END 302:          AS Quarter, 303:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 304:         CASE 305:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 306:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 307:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 308:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 309:          ELSE '5' 310:         END 311:          AS Week, 312:         rt.TINVDATE tgl, 313:         rt.hpackgtyp, 314:         rt.HINCOTERM, 315:         rt.TINVTYP, 316:         rt.tcustsegm Segment, 317:         substring(rt.tsold_to, 4, 7) iddistributor, 318:         rt.TPW_LN_35 AS Nilai, 319:         7 id, 320:         right(rtrim(tgeoarea), 4) tgeoarea 321:         FROM raptor_tpm rt 322:         WHERE len(rt.tinvdate) = 8 323:         UNION 324:         SELECT left(rt.tinvdate, 4) Tahun, 325:         CASE 326:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 327:         AND '06' THEN 328:           '1' 329:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 330:         AND '12' THEN 331:           '2' 332:         END 333:          AS Semester, 334:         CASE 335:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 336:         AND '03' THEN 337:           '1' 338:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 339:         AND '06' THEN 340:           '2' 341:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 342:         AND '09' THEN 343:           '3' 344:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 345:         AND '12' THEN 346:           '4' 347:         END 348:          AS Quarter, 349:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 350:         CASE 351:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 352:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 353:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 354:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 355:          ELSE '5' 356:         END 357:          AS Week, 358:         rt.TINVDATE tgl, 359:         rt.hpackgtyp, 360:         rt.HINCOTERM, 361:         rt.TINVTYP, 362:         rt.tcustsegm Segment, 363:         substring(rt.tsold_to, 4, 7) iddistributor, 364:         rt.TPW_LN_36 Nilai, 365:         8 id, 366:         right(rtrim(tgeoarea), 4) tgeoarea 367:         FROM raptor_tpm rt 368:         WHERE len(rt.tinvdate) = 8 369:         UNION 370:         SELECT left(rt.tinvdate, 4) Tahun, 371:         CASE 372:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 373:         AND '06' THEN 374:           '1' 375:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 376:         AND '12' THEN 377:           '2' 378:         END 379:          AS Semester, 380:         CASE 381:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 382:         AND '03' THEN 383:           '1' 384:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 385:         AND '06' THEN 386:           '2' 387:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 388:         AND '09' THEN 389:           '3' 390:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 391:         AND '12' THEN 392:           '4' 393:         END 394:          AS Quarter, 395:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 396:         CASE 397:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 398:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 399:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 400:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 401:          ELSE '5' 402:         END 403:          AS Week, 404:         rt.TINVDATE tgl, 405:         rt.hpackgtyp, 406:         rt.HINCOTERM, 407:         rt.TINVTYP, 408:         rt.tcustsegm Segment, 409:         substring(rt.tsold_to, 4, 7) iddistributor, 410:         rt.TPW_LN_39 Nilai, 411:         9 id, 412:         right(rtrim(tgeoarea), 4) tgeoarea 413:         FROM raptor_tpm rt 414:         WHERE len(rt.tinvdate) = 8 415:         UNION 416:         SELECT left(rt.tinvdate, 4) Tahun, 417:         CASE 418:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 419:         AND '06' THEN 420:           '1' 421:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 422:         AND '12' THEN 423:           '2' 424:         END 425:          AS Semester, 426:         CASE 427:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 428:         AND '03' THEN 429:           '1' 430:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 431:         AND '06' THEN 432:           '2' 433:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 434:         AND '09' THEN 435:           '3' 436:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 437:         AND '12' THEN 438:           '4' 439:         END 440:          AS Quarter, 441:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 442:         CASE 443:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 444:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 445:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 446:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 447:          ELSE '5' 448:         END 449:          AS Week, 450:         rt.TINVDATE tgl, 451:         rt.hpackgtyp, 452:         rt.HINCOTERM, 453:         rt.TINVTYP, 454:         rt.tcustsegm Segment, 455:         substring(rt.tsold_to, 4, 7) iddistributor, 456:         rt.TPW_LN_40 Nilai, 457:         10 id, 458:         right(rtrim(tgeoarea), 4) tgeoarea 459:         FROM raptor_tpm rt 460:         WHERE len(rt.tinvdate) = 8 461:         UNION 462:         SELECT left(rt.tinvdate, 4) Tahun, 463:         CASE 464:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 465:         AND '06' THEN 466:           '1' 467:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 468:         AND '12' THEN 469:           '2' 470:         END 471:          AS Semester, 472:         CASE 473:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 474:         AND '03' THEN 475:           '1' 476:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 477:         AND '06' THEN 478:           '2' 479:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 480:         AND '09' THEN 481:           '3' 482:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 483:         AND '12' THEN 484:           '4' 485:         END 486:          AS Quarter, 487:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 488:         CASE 489:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 490:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 491:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 492:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 493:          ELSE '5' 494:         END 495:          AS Week, 496:         rt.TINVDATE tgl, 497:         rt.hpackgtyp, 498:         rt.HINCOTERM, 499:         rt.TINVTYP, 500:         rt.tcustsegm Segment, 501:         substring(rt.tsold_to, 4, 7) iddistributor, 502:         rt.TPW_LN44A AS Nilai, 503:         11 id, 504:         right(rtrim(tgeoarea), 4) tgeoarea 505:         FROM raptor_tpm rt 506:         WHERE len(rt.tinvdate) = 8 507:         UNION 508:         SELECT left(rt.tinvdate, 4) Tahun, 509:         CASE 510:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 511:         AND '06' THEN 512:           '1' 513:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 514:         AND '12' THEN 515:           '2' 516:         END 517:          AS Semester, 518:         CASE 519:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 520:         AND '03' THEN 521:           '1' 522:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 523:         AND '06' THEN 524:           '2' 525:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 526:         AND '09' THEN 527:           '3' 528:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 529:         AND '12' THEN 530:           '4' 531:         END 532:          AS Quarter, 533:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 534:         CASE 535:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 536:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 537:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 538:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 539:          ELSE '5' 540:         END 541:          AS Week, 542:         rt.TINVDATE tgl, 543:         rt.hpackgtyp, 544:         rt.HINCOTERM, 545:         rt.TINVTYP, 546:         rt.tcustsegm Segment, 547:         substring(rt.tsold_to, 4, 7) iddistributor, 548:         rt.TPW_LN_46 Nilai, 549:         12 id, 550:         right(rtrim(tgeoarea), 4) tgeoarea 551:         FROM raptor_tpm rt 552:         WHERE len(rt.tinvdate) = 8 553:         UNION 554:         SELECT left(rt.tinvdate, 4) Tahun, 555:         CASE 556:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 557:         AND '06' THEN 558:           '1' 559:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 560:         AND '12' THEN 561:           '2' 562:         END 563:          AS Semester, 564:         CASE 565:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 566:         AND '03' THEN 567:           '1' 568:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 569:         AND '06' THEN 570:           '2' 571:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 572:         AND '09' THEN 573:           '3' 574:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 575:         AND '12' THEN 576:           '4' 577:         END 578:          AS Quarter, 579:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 580:         CASE 581:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 582:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 583:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 584:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 585:          ELSE '5' 586:         END 587:          AS Week, 588:         rt.TINVDATE tgl, 589:         rt.hpackgtyp, 590:         rt.HINCOTERM, 591:         rt.TINVTYP, 592:         rt.tcustsegm Segment, 593:         substring(rt.tsold_to, 4, 7) iddistributor, 594:         rt.TPW_LN_11 AS Nilai, 595:         13 id, 596:         right(rtrim(tgeoarea), 4) tgeoarea 597:         FROM raptor_tpm rt 598:         WHERE len(rt.tinvdate) = 8 599:         UNION 600:         SELECT left(rt.tinvdate, 4) Tahun, 601:         CASE 602:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 603:         AND '06' THEN 604:           '1' 605:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 606:         AND '12' THEN 607:           '2' 608:         END 609:          AS Semester, 610:         CASE 611:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 612:         AND '03' THEN 613:           '1' 614:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 615:         AND '06' THEN 616:           '2' 617:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 618:         AND '09' THEN 619:           '3' 620:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 621:         AND '12' THEN 622:           '4' 623:         END 624:          AS Quarter, 625:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 626:         CASE 627:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 628:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 629:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 630:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 631:          ELSE '5' 632:         END 633:          AS Week, 634:         rt.TINVDATE tgl, 635:         rt.hpackgtyp, 636:         rt.HINCOTERM, 637:         rt.TINVTYP, 638:         rt.tcustsegm Segment, 639:         substring(rt.tsold_to, 4, 7) iddistributor, 640:         rt.TPW_LN_15 Nilai, 641:         14 id, 642:         right(rtrim(tgeoarea), 4) tgeoarea 643:         FROM raptor_tpm rt 644:         WHERE len(rt.tinvdate) = 8 645:         UNION 646:         SELECT left(rt.tinvdate, 4) Tahun, 647:         CASE 648:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 649:         AND '06' THEN 650:           '1' 651:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 652:         AND '12' THEN 653:           '2' 654:         END 655:          AS Semester, 656:         CASE 657:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 658:         AND '03' THEN 659:           '1' 660:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 661:         AND '06' THEN 662:           '2' 663:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 664:         AND '09' THEN 665:           '3' 666:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 667:         AND '12' THEN 668:           '4' 669:         END 670:          AS Quarter, 671:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 672:         CASE 673:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 674:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 675:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 676:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 677:          ELSE '5' 678:         END 679:          AS Week, 680:         rt.TINVDATE tgl, 681:         rt.hpackgtyp, 682:         rt.HINCOTERM, 683:         rt.TINVTYP, 684:         rt.tcustsegm Segment, 685:         substring(rt.tsold_to, 4, 7) iddistributor, 686:         rt.TPW_LN_21 AS Nilai, 687:         15 id, 688:         right(rtrim(tgeoarea), 4) tgeoarea 689:         FROM raptor_tpm rt 690:         WHERE len(rt.tinvdate) = 8 691:         UNION 692:         SELECT left(rt.tinvdate, 4) Tahun, 693:         CASE 694:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 695:         AND '06' THEN 696:           '1' 697:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 698:         AND '12' THEN 699:           '2' 700:         END 701:          AS Semester, 702:         CASE 703:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 704:         AND '03' THEN 705:           '1' 706:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 707:         AND '06' THEN 708:           '2' 709:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 710:         AND '09' THEN 711:           '3' 712:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 713:         AND '12' THEN 714:           '4' 715:         END 716:          AS Quarter, 717:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 718:         CASE 719:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 720:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 721:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 722:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 723:          ELSE '5' 724:         END 725:          AS Week, 726:         rt.TINVDATE tgl, 727:         rt.hpackgtyp, 728:         rt.HINCOTERM, 729:         rt.TINVTYP, 730:         rt.tcustsegm Segment, 731:         substring(rt.tsold_to, 4, 7) iddistributor, 732:         rt.TPW_LN_44 Nilai, 733:         16 id, 734:         right(rtrim(tgeoarea), 4) tgeoarea 735:         FROM raptor_tpm rt 736:         WHERE len(rt.tinvdate) = 8 737:         UNION 738:         SELECT left(rt.tinvdate, 4) Tahun, 739:         CASE 740:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 741:         AND '06' THEN 742:           '1' 743:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 744:         AND '12' THEN 745:           '2' 746:         END 747:          AS Semester, 748:         CASE 749:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 750:         AND '03' THEN 751:           '1' 752:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 753:         AND '06' THEN 754:           '2' 755:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 756:         AND '09' THEN 757:           '3' 758:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 759:         AND '12' THEN 760:           '4' 761:         END 762:          AS Quarter, 763:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 764:         CASE 765:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 766:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 767:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 768:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 769:          ELSE '5' 770:         END 771:          AS Week, 772:         rt.TINVDATE tgl, 773:         rt.hpackgtyp, 774:         rt.HINCOTERM, 775:         rt.TINVTYP, 776:         rt.tcustsegm Segment, 777:         substring(rt.tsold_to, 4, 7) iddistributor, 778:         rt.TPW_LN_11 AS Nilai, 779:         17 id, 780:         right(rtrim(tgeoarea), 4) tgeoarea 781:         FROM raptor_tpm rt 782:         WHERE len(rt.tinvdate) = 8 783:         UNION 784:         SELECT left(rt.tinvdate, 4) Tahun, 785:         CASE 786:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 787:         AND '06' THEN 788:           '1' 789:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 790:         AND '12' THEN 791:           '2' 792:         END 793:          AS Semester, 794:         CASE 795:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 796:         AND '03' THEN 797:           '1' 798:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 799:         AND '06' THEN 800:           '2' 801:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 802:         AND '09' THEN 803:           '3' 804:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 805:         AND '12' THEN 806:           '4' 807:         END 808:          AS Quarter, 809:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 810:         CASE 811:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 812:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 813:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 814:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 815:          ELSE '5' 816:         END 817:          AS Week, 818:         rt.TINVDATE tgl, 819:         rt.hpackgtyp, 820:         rt.HINCOTERM, 821:         rt.TINVTYP, 822:         rt.tcustsegm Segment, 823:         substring(rt.tsold_to, 4, 7) iddistributor, 824:         rt.TPW_LN_15 Nilai, 825:         18 id, 826:         right(rtrim(tgeoarea), 4) tgeoarea 827:         FROM raptor_tpm rt 828:         WHERE len(rt.tinvdate) = 8 829:         UNION 830:         SELECT left(rt.tinvdate, 4) Tahun, 831:         CASE 832:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 833:         AND '06' THEN 834:           '1' 835:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 836:         AND '12' THEN 837:           '2' 838:         END 839:          AS Semester, 840:         CASE 841:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 842:         AND '03' THEN 843:           '1' 844:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 845:         AND '06' THEN 846:           '2' 847:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 848:         AND '09' THEN 849:           '3' 850:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 851:         AND '12' THEN 852:           '4' 853:         END 854:          AS Quarter, 855:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 856:         CASE 857:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 858:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 859:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 860:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 861:          ELSE '5' 862:         END 863:          AS Week, 864:         rt.TINVDATE tgl, 865:         rt.hpackgtyp, 866:         rt.HINCOTERM, 867:         rt.TINVTYP, 868:         rt.tcustsegm Segment, 869:         substring(rt.tsold_to, 4, 7) iddistributor, 870:         rt.TPW_LN_21 AS Nilai, 871:         19 id, 872:         right(rtrim(tgeoarea), 4) tgeoarea 873:         FROM raptor_tpm rt 874:         WHERE len(rt.tinvdate) = 8 875:         UNION 876:         SELECT left(rt.tinvdate, 4) Tahun, 877:         CASE 878:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 879:         AND '06' THEN 880:           '1' 881:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 882:         AND '12' THEN 883:           '2' 884:         END 885:          AS Semester, 886:         CASE 887:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '01' 888:         AND '03' THEN 889:           '1' 890:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '04' 891:         AND '06' THEN 892:           '2' 893:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '07' 894:         AND '09' THEN 895:           '3' 896:          WHEN SUBSTRING(Convert(Varchar(8), rt.TINVDATE), 5, 2) BETWEEN '10' 897:         AND '12' THEN 898:           '4' 899:         END 900:          AS Quarter, 901:         substring(convert(Varchar(8), Rt.tinvdate), 5, 2) Periode, 902:         CASE 903:          WHEN right(rt.TINVDATE, 2) BETWEEN '01' AND '07' THEN '1' 904:          WHEN right(rt.TINVDATE, 2) BETWEEN '08' AND '14' THEN '2' 905:          WHEN right(rt.TINVDATE, 2) BETWEEN '15' AND '21' THEN '3' 906:          WHEN right(rt.TINVDATE, 2) BETWEEN '22' AND '29' THEN '4' 907:          ELSE '5' 908:         END 909:          AS Week, 910:         rt.TINVDATE tgl, 911:         rt.hpackgtyp, 912:         rt.HINCOTERM, 913:         rt.TINVTYP, 914:         rt.tcustsegm Segment, 915:         substring(rt.tsold_to, 4, 7) iddistributor, 916:         rt.TPW_LN_44 Nilai, 917:         20 id, 918:         right(rtrim(tgeoarea), 4) tgeoarea 919:         FROM raptor_tpm rt 920:         WHERE len(rt.tinvdate) = 8) asli