Path: utzoo!attcan!uunet!cs.utexas.edu!swrinde!mips!pacbell.com!pacbell!hoptoad!unisoft!mtxinu!sybase!jeffl@sybase.Sybase.COM
From: jeffl@sybase.Sybase.COM (Jeff Lichtman)
Newsgroups: comp.databases
Subject: Re: Ease of SQL with an ugly schema
Message-ID: <10115@sybase.sybase.com>
Date: 14 Jul 90 05:25:07 GMT
References: <10698@chaph.usc.edu>
Sender: news@Sybase.COM
Lines: 36

> 	There is a table containing fields X Y and FLAG.  FLAG
> 	takes values zero or one.  One wishes to create the
> 	summation over the table of a variable Z where Z is defined
> 	as follows:
> 
> 		If (flag = 0) then Z := X - Y
> 			else Z := X + Y

	select (select sum(X - Y) from TAB where FLAG = 0) +
		(select sum(X + Y) from TAB where FLAG = 1)

A potential problem here is that the FLAG column isn't very selective.
Even if you have an index on that column, the query optimizer may
decide not to use the index for at least one of the subqueries.  So,
unless you're careful, you could end up with two full scans of the data.

Suppose you redefined your data, so that the FLAG column contained either
1 or -1 instead of 1 or 0.  That would allow you to write the query like this:

	select sum(X + Y * FLAG) from TAB

This way, you scan the table exactly once.

If you can't use -1 instead of 0 in the FLAG column, you could write the
query like this:

	select sum(X + 2.0 * (Y * (FLAG - 0.5))) from TAB

You may have to massage this query to make it work, since different database
systems have different rules for arithmetic conversion.

I hope this helps!
---
Jeff Lichtman at Sybase
{mtxinu,pacbell}!sybase!jeffl  -or- jeffl@sybase.com
"Saints should always be judged guilty until they are proved innocent..."