Path: utzoo!censor!comspec!daveb
From: daveb@comspec.uucp (dave berman)
Newsgroups: comp.databases
Subject: Re: Ease of SQL with an ugly schema
Summary: Change flag set {0,1} to flag set {-1,+1}
Message-ID: <1990Jul11.072718.21508@comspec.uucp>
Date: 11 Jul 90 07:27:18 GMT
References: <10698@chaph.usc.edu>
Organization: Comspec Communications Inc., Toronto Ontario Canada
Lines: 31

In article <10698@chaph.usc.edu>, ajayshah@aludra.usc.edu (Ajay Shah) writes:
> I'm pretty naive of databases and SQL, and wondered how this
> query would be represented using SQL:
>       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
> Ajay Shah, (213)747-9991, ajayshah@usc.edu

Change your original premise, of FLAG equal to 0 or 1, to FLAG equal to -1 or
+1. The new SQL becomes

SQL> SELECT TOTAL( X + FLAG*Y ) FROM tablename;

To change the entire file to the correct format simply

SQL> CHANGE FLAG TO -1 FOR FLAG = 0 IN tablename;

Disclaimer: Since your question was kinda theoretical, my answer is
theoretical as well, since it combines the syntax of a couple of different SQL
implementations I use. The concepts transfer fine, but the syntax and keywords
change a little.

Keep well,       -dave
-- 
Dave Berman
436 Perth Av #U-907   daveb@comspec.UUCP   Computer at work
Toronto Ontario       uunet!mnetor!becker!comspec!daveb
Canada M6P 3Y7        416-785-3668         Fax at work