Path: utzoo!utgpu!water!watmath!clyde!burl!codas!cpsc6a!rtech!eric
From: eric@rtech.UUCP (Eric Lundblad)
Newsgroups: comp.databases
Subject: Re: Informix query
Message-ID: <1555@rtech.UUCP>
Date: 9 Jan 88 03:28:53 GMT
References: <60@coot.AUSTIN.LOCKHEED.COM>
Organization: Relational Technology Inc. Alameda, CA 94501
Lines: 80

From article <60@coot.AUSTIN.LOCKHEED.COM>, by chris@AUSTIN.LOCKHEED.COM (Chris Wood):
> In article <3216@ihlpf.ATT.COM>, lukas@ihlpf.ATT.COM (00704a-Lukas) writes:
>> 4)	Difficulty (outside of perform) of doing that most basic of
>> 	database applications: that of cycling through the rows, adding,
>> 	deleting, modifying as you go.
> 
>  I would like to know how to go about this also.  I am using ORACLE
> and would like to do this.  I used to do something like the following 
> in MODEL 204:
> 
> 1. Find customers where qty-ordered > 1000
> 2. For each record in 1
> 2.1 If customer.area = "NORTHEAST" then 
> 2.1.1   update region
>    set good-customers = good-customers + 1
> 2.2 find orders where orders.custno = customers.number
> 2.3 for each record in 2.2
> 2.3.1 ... add up the total orders for that customer and store them back
>           into that customers record...
> .
> .
> .
> 
> Is there some underlying mathematical "relational purist" reason why such
> procedural constructs are lacking in relational implementations?  The above
> looping code is very easy to develop and understand (at least for me) compared
> to the shenanigans I have to go through to do something like this in a 
> SQL based system. 
> 
> How about something like:
> 
> Select * from customer where customer.qty-ordered > 1000
> into junk
> for each record in junk 
> DO
> .
> .
> .
> .
> End DO
> 
> (WOW Shades of FORTRAN)
> 
> Why Can't relational systems do this?  Or better yet, is there a
> SQL/Relational system out there that does?

	Standard SQL (ANSI or DB2, take your choice) requires you to define a
cursor and a loop that fetchs the tuples. This is somewhat more involved than
what you describe, but it can be done.

	There are implementations of SQL that allow you to do the following:

exec sql select * from customer where qty-ordered > 1000
exec sql begin

	<program code that plays with the data here>

exec sql end

	There is also QUEL, a language similar to SQL (only better, ref. 
C J Date) that allows you to do: 

## retrieve (customer.all) where customer.qty-ordered > 1000
## begin

	<program code that plays with the data here>

## end

	In both cases, the program code is executed for each tuple that the
select or retrieve returns. 

		eric


As always, these opinions are mine and no one elses. 
-- 

			Eric Lundblad
		   ucbvax!mtxinu!rtech!eric