RE: Updating with the column from another file

Thanks, I was trying to do it with one request, like this.

update oldfile a
set a.profession = ( select profession from
custmast join custdos on
Custmast.cust = custdos.cust where custdos.dossier = a.dossier )
where exists ( select custdos.dossier from custdos where custdos.dossier= a.dossier )

This worked if the customer had only one dossier.

But I prefer the good old RPG way :
CHAIN dossier custdos;
IF %FOUND;
CHAIN custdos.cust custmast;
IF %FOUND;
profession = custmast.profession;

ENDIF;
ENDIF;




-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de rob@xxxxxxxxx
Envoyé : vendredi 18 juillet 2008 16:34
À : Midrange Systems Technical Discussion
Objet : Re: Updating with the column from another file

With
select $customer, profession
from qtemp.custmast
$CUSTOMER PROFESSION
4 6

and
select $customer, $dossier
from qtemp.custdoss
$CUSTOMER $DOSSIER
4 2
4 7

then start with
select $dossier, profession
from qtemp.custdoss d join qtemp.custmast m
on d.$customer = m.$customer
$DOSSIER PROFESSION
2 6
7 6

and end up with
create table qtemp.dossprof as (
select $dossier, profession
from qtemp.custdoss d join qtemp.custmast m
on d.$customer = m.$customer
) with data

Verified with
select $dossier, profession
from qtemp.dossprof
$DOSSIER PROFESSION
2 6
7 6

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
07/18/2008 10:16 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc

Subject
Updating with the column from another file






Hi,



I have an old program that produces a file with, amongst other information, $dossier, profession

The profession has to be changed so it comes from the customer master file.
$customer, profession

To get to the customer master file, I'd have to first access the customer/dossier file $customer, $dossier

I just wasted an hour updating profession in the old file with profession from the customer master file with SQL. The problem was that there can be more than one dossier per customer.

So I'm going to write an RPG program that reads the old file, chains on customer/dossier file then on customer master file.


Was this possible in SQL?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.