2008-02-28

COMPUTE clause in MSSQL

I am totally frustrated with the SQL recently. The bad guy is COMPUTE clause.

Since I am making a migration project from MSSQL 2000 server to Oracle 10i Server, a lot of SQL queries in the applications should be modified to meet the Oracle SQL requirements. What I found in the internet is:

---

> The Compute by clause of MSSQL basically allows you to get a running
> total at the bottom (end) of the report.
> In a way it is similar then using ".. group by .." with aggregate
> functions (sum) but in this case I am not trying to "... group by .."
> does not make sense in the context of the query, just want to get a
> summary (sum and count) of some columns at the end of the record.

The "standard" way to do this is to make a second query to compute the aggragates. However it is possible to combine the two if you really need the aggregates in the same result set.

> > > select A.ProdID, A.Description, A. Qty, A.Price
> > > from SoldItems as A
> > > where A.ListID = 15
> > > order by A.ProdID
> > > compute count(A.ProdID),sum(A.Price),sum(A.Qty)

SELECT ProdID, Description, Qty, Price
FROM
(SELECT A.ProdID, A.Description, A.QTY, A.Price, 1 AS Kind
FROM SoldItems AS A
WHERE A.ListID = 15
UNION ALL
SELECT count(B.ProdID), NULL AS Description, sum(B.Price), sum(B.Qty),
2 AS Kind
FROM SoldItems AS B
WHERE B.ListID = 15
) AS C
ORDER BY Kind, ProdID
;

---

After that I must stil fight with SHAPE clause... What a life!

2008-02-18

Strange problem with file copying in Windows Explorer

Since I sometimes need to use chinese in Windows, I set up my Windows XP under "Control Panel" -> "Regional and Language Options" -> "Advanced" -> "non-Unicode Program" Encoding -> "Simplified Chinese".

Now I get a big problem. I copied a zip file from a Linux box to my local computer. The copy process was ok and the zip file could be extracted too. Then I tried to copy that zip file to a directory on a machine with windows server 2003 enterprise version using Copy/Paste. The copy process seemed normal, but when I opened the file directly on the target machine and tried to extract it, WinRar told me "the zip file is corrupt!".

If I change the non-Unicode Program's Encoding to something like "German", then the problem will not happen. It seems that the windows explorer is a non-unicode program?!

My Windows is already updated to SP2. The IE version is still version 6.

Until now I haven't find any solution to this problem.