Oracle Tips : Reset ROWNUM pada setiap group

Tips ini diperolehi dari laman web oracle dan diberikan oleh Satyanarayana Josyula, Sr. Oracle DBA at Nexus Energy Software, in Reston, Virginia. Untuk maklumat lanjut sila layari
http://www.oracle.com/technology/oramag/code/tips2004/082304.html

Kita menggunakan ROWNUM untuk menomborkan secara berturutan (seperti 1, 2, 3....) senarai rekod dari SELECT statement. Kadang-kadang terdapat keperluan untuk memaparkan laporan atau senarai rekod dengan nombor siri yang berturutan pada setiap rekod(rows) dan nombor tersebut akan bermula pada nombor 1 semula bagi kumpulan(group) yang baru (seperti contoh di bawah; lajur 4)

Contoh

Nama table : BATCHLIST
BATCH_NO STATE Jika guna ROWNUM Hasil yang dikehendaki
-------- --------- ---------------- ----------------------
A1 PERAK 1 1
A1 PERAK 2 2
A1 PERAK 3 3
B2 SELANGOR 4 1
B2 SELANGOR 5 2
C3 PAHANG 6 1
C3 PAHANG 7 2
C3 PAHANG 8 3
D4 KELANTAN 9 1

Anda boleh melakukannya dengan menggunakan SELECT STATEMENT berikut :

SELECT A.BATCH_NO,
DECODE(ROWNUM - min_seqno,0, 1, ROWNUM + 1 - min_seqno) AS seqno
FROM (SELECT * FROM BATCHLIST ORDER BY BATCH_NO) A,
(SELECT BATCH_NO, MIN(rownum) min_seqno
FROM (SELECT * FROM BATCHLIST ORDER by BATCH_NO)
GROUP BY BATCH_NO
ORDER BY BATCH_NO) B
WHERE A.BATCH_NO = B.BATCH_NO
ORDER BY A.BATCH_NO;

How to avoid windows running FDISK when start @ reboot

If your Windows keeps on running FDISK command when your start or reboot your computer, it is very irritating and frustrating. To do away this problem you might try this solution. I found this solution after facing the same problem a few months ago (after several hours googling of course!). Hopefully it will help those who need it!

1. Click the Start Button and select Run
2. In the Run dialogue box type: cmd
   
3. Windows will then open command prompt screen : type fsutil dirty query : (Replace : with drive disk you have problem with; like E:, or D:, example fsutil dirty query C:) and press enter.
   
4. IF system indicates that the disk is 'dirty' then go to step 5 else go back to step 3.
   
5. Type: CHKNTFS /X : (eg CHKNTFS /X C:) and press Enter.
6. Reboot the PC.

After rebooting the PC, proceed with the following steps

1. Click Start>Run and type cmd.
2. Windows will then open command prompt screen : Type: CHKDSK /f/r : (eg: CHKDSK /f/r C:) and press Enter.
3. After the drive has been scanned type: fsutil dirty query : (eg. fsutil dirty query C:) and press Enter
4. Message should appear volumn - : is not dirty.

That's it. Mission accomplished!

How to Install Sybase ASE 11.5 on Windows 2003

Recently, we installed Sybase ASE 11.5 on Windows 2003 for one of our projects need. Installation seems to run smoothly without any problems and creating an adapter server after that is also successful. But when we tried to start the server using Services (Administration Tools>Services) or using Sybase Central, the server is failed to start as a service.

After doing some searching and ‘googling’, I found that this step-by-step workaround is our savvier and we managed to start the server using Services & Sybase Central. The only problem is that we cannot configure the server using Sybase Utility sybconfig.exe. But it is 'okay' for us and case closed!

Step-by-step workaround

1) Download the "Windows Server 2003 Resource Kit Tools"

2) Install the "Windows Server 2003 Resource Kit Tools" into a directory like "c:\reskit".

3):\Program Files\Windows Resource Kits\Tools>cd \reskit

3) Remove the existing SQL Server service using the following command.
c:\reskit> INSTSRV YourSQLServerServiceName REMOVE
upon successful, message 'The service was successfully deleted!' will appear

4) Install the SQL Server service using the "INSTSRV" utility command
C:\reskit>instsrv YourSQLServerServiceName C:\reskit\srvany.exe
Upon successful, message 'The service was successfuly added!

Make sure that you go into the Control Panel and use the Services applet to change the Account Name and Password that this newly installed service will use for its Security Context.' will appear.

5) In the Registry Editor window, go to HKEY_LOCAL_MACHINE | SYSTEM | CurrentControlSet | Services and click your SQL Server service name).
a. Add a new key by selecting Edit | New | Key. Enter "Parameters" as the Key name.
b. Click Parameters and add a new string value by selecting Edit | New | String Value. Enter "Application" as the new String value.

c. Double-click Application. The Edit String dialog box appears. In Value Data field, enter:
c:\sybase\bin\sqlsrvr.exe -C -SYourSQLServerName -IC:\sybase\ini

6) Select Control Panel | Services. Click , then click Startup.