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!

5 comments:

motou said...

Thanks for your comment. I've also visited your blog. What a pity that I don't know French.

A warm hug anyway. :)

Anonymous said...

Hello. And Bye.

Anonymous said...

Good fill someone in on and this enter helped me alot in my college assignement. Thanks you on your information.

Anonymous said...

Well your article helped me truly much in my college assignment. Hats high to you post, wish look ahead in the direction of more cognate articles in a jiffy as its one of my favourite question to read.

Anonymous said...

It is useful to try everything in practice anyway and I like that here it's always possible to find something new. :)