Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!dciem!nrcaer!cognos!garyp
From: garyp@cognos.uucp (Gary Puckering)
Newsgroups: comp.databases
Subject: Re: SQL help
Message-ID: <1305@smokey.UUCP>
Date: Fri, 21-Aug-87 11:18:28 EDT
Article-I.D.: smokey.1305
Posted: Fri Aug 21 11:18:28 1987
Date-Received: Sun, 23-Aug-87 10:13:03 EDT
References: <464@ems.MN.ORG>
Reply-To: garyp@cognos.UUCP (Gary Puckering)
Organization: Cognos Inc., Ottawa, Canada
Lines: 98

In article <464@ems.MN.ORG> barker@ems.MN.ORG (Bob W. Barker) writes:
>I'm trying to whip up an SQL that
>will update the cusstat record of each customer that has corresponding 
>adjustment records where the posted field = N (for NO).  I'd also like to
>change the posted field to 'Y' after doing this but this isn't a real problem
>so I won't address it here.
>
>This SQL doesn't quite work:
>
>update cusstat
>set CS_balance =
>	select CS_balance + A_dollars from cusstat, adjust
>	where CS_custname = A_custname and A_posted = 'N'/
>
>It updates the balances of all customers in cusstat.  

This problem came up in March.  You are not the only one to fall into
this trap.  Basically, SQL is rather counter-intuitive for these kinds
of problems.  It is easier to express a solution from the point of
view of the adjustment transactions as the "driver" rather than the
customer as the "driver".  But, unfortunately, SQL doesn't work like that.
(Pity!)

Here's a copy of a similar situation reported by Stu Heiss, and my response:

|Article: 103 of comp.databases
|>From: garyp@cognos.UUCP (Gary Puckering)
|Newsgroups: comp.databases
|Subject: Re: need help with sql problem (informix)
|Keywords: SQL, Informix
|Date: 25 Mar 87 15:10:39 GMT
|Reply-To: garyp@smokey.UUCP (Gary Puckering)
|Organization: Cognos Incorporated, Ottawa, Canada
|
|In article <276@jpusa1.UUCP> stu@jpusa1.UUCP (Stu Heiss) writes:
|>I'm trying to do what should be a simple update and can't believe the
|>abysmal performance.  Maybe I'm missing something obvious.  The scenario
|>is a 3 table database (name,address,accounts) and I'm updating a batch
|>from a fourth table.  Here's the code to do the name:
|>	update name_table
|>		set name = (select name from change_table where
|>			name_table.id = change_table.id)
|>		where name_table.id in
|>			(select id from change_table);
|>This seems overly complex when all I want to do is find the entries in
|>the name_table whose id match those in the change_table and update the
|>name column (field).  
|
|Wow!  This is scarey!  I hope someone can come up with an elegant SQL
|request for this.  I can't think of one.  However, I can offer a suggestion
|and a comment:
|
|1.  You *must* have an index on the id column of change_table.  Otherwise,
|	a sequential scan of change_table will be required for every row
|	read from name_table.
|
|2.  The strategy SQL has forced you into is a bad one.  You should be
|	able to drive the update request from the change_table, rather than
|	from the name_table.  For examplem, in QTP, our 4GL transaction
|	processor, you would code this request as:
|
|		access change_table link to name_table
|		output name_table update
|		  item name final name of change_table
|        go
|
|    For this request to execute efficiently, there need only be an index
|    on id of name_table.  Essentially, this request does a natural join of
|    change_table and name_table and then it updates only the name_table row.
|
|    Unfortunately, I can't see any way to do this in interactive SQL.
|
|3.  This above strategy could be accomplished within a program by using
|    cursor.  For example:
|
|		sql declare get_changes cursor for
|		  select id.change_table name.change_table
|		  into :xid :xname
|		  from change_table, name_table
|		  where id.change_table = id.name_table
|
|    Now you can code a loop which fetches the appropriate id's and names
|	into host variables xid and xname.  Within the loop you code an SQL
|	update request like:
|
|		sql update name_table
|		  set name = :xname
|		  where id = :xid
|
|
|Like I say, I hope someone else can come up with a decent solution which
|can be coded in interactive SQL.  If not, it just goes to show you that
|SQL is a lousy excuse for a 4GL.  It leads to counter-intuitive requests.
-- 

Gary Puckering        3755 Riverside Dr.
Cognos Incorporated   Ottawa, Ontario    {allegra,decvax,ihnp4,linus,pyramid}
(613) 738-1440        CANADA  K1G 3N3    !utzoo!dciem!nrcaer!cognos!garyp