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
(SELECT A.ProdID, A.Description, A.QTY, A.Price, 1 AS Kind
FROM SoldItems AS A
WHERE A.ListID = 15
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!