Re: Join & Update

[Rob Berendt <rob@xxxxxxxxx>]

James,
Can you give an example of what you are trying to accomplish with an updateable 
join?  SQL came out with some stuff in V4R3.  This may or may not apply.  For 
example:

HEADERFILE
ORDER#  TOTAMT
00001           0
00002           0

LINEFILE
ORDER#  LINE#   DETAMT
00001           001     5.00
00001           002     7.00
00002           001     2.00
00002           002     3.00
00002           003     1.00

UPDATE QTEMP/HEADERFILE                            
SET TOTAMT=(SELECT SUM(DETAMT) FROM LINEFILE       
            WHERE LINEFILE.ORDER#=HEADERFILE.ORDER#
            GROUP BY ORDER#)                       

Will give you:
HEADERFILE
ORDER#  TOTAMT
00001           12
00002           6

Are you able to use SQL to do anything at all on your file, since it is a 
multiformat physical file?





jlangston@conexfreight.com on 10/18/99 12:53:40 PM
Please respond to RPG400-L@midrange.com@Internet
To:     RPG400-L@midrange.com@Internet
cc:      
Fax to: 
Subject:        Re: Join & Update

Updateable joins would make my life *SO*  much simpler.  I have one file
on my system (thank goodness it is only one) that uses a dual format.
Unfortunately,
this is our AR file and is used in something like 1/4 of our programs.  All the
other files have been converted to DDS no problem.  But, this one is the 
problem.

For this one we are still internally describing it, our only other option is to
break it
into two files then go through every single program and change them to use one,
the other, or both files.  Something we just haven't had the time to invest 
into.

An Updateable join would work perfectly, and we wouldn't have to do this.
We could just break it up into two files (which we already have created, by the
way, but can't really use) and then do an Updateable join logical on them, and
all our
programs would still work as written, and then we could change the programs to
use externally described files.

This limitation, to me, has always been the one major drawback to logical joins,
and is the only reason we would want to use one anyway.

Anomalies, smomalies, as long as it works <g>

Regards,

Jim Langston

"R. Bruce Hoffman, Jr." wrote:

> Not a dumb question, but a join operation is not updatable, period. This has
> always been a "future objective" of database, but don't hold your breath.
>
> The reason to design to third normal form is to prevent update anomolies.
> The reason to use joins is to put the data in 2nd normal form for usage.
> Thus, the join form re-introduces update anomolies to the process, hence, no
> update.
>
> -----Original Message-----
> From: Sethuraman <sethu@bc.sympatico.ca>
> To: RPG400-L@midrange.com <RPG400-L@midrange.com>
> Date: Monday, October 18, 1999 2:09 AM
> Subject: Join & Update
>
> >Hi Folks:
> >
> >A join logical file cannot be used for Update.
> >
> >What about SQL?  If a view (with joins) is created using SQL, can this view
> be
> >declared
> >as an "Update"? Or is this a dumb question?
> >
>
> ===========================================================
> R. Bruce Hoffman, Jr.
>  -- IBM Certified AS/400 Professional System Administrator
>  -- IBM Certified AS/400 Professional Network Administrator
>
> "The sum of all human knowledge is a fixed constant.
>     It's the population that keeps growing!"
>
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---