Re: AW: SQO Join Update Question

Hi Birgitta,
I tried your solution, but am getting the following message:
Result of SELECT more than one row

There are multiple records in the vsadetl for each record in vsaxref.

TIA,

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3









----- Original Message ----
From: BirgittaHauser <Hauser@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, August 1, 2007 1:26:01 PM
Subject: AW: SQO Join Update Question


Hi,

Try to change your SQL-Statement as follows:
update foodwork/vsadetl
set (sbitem, sbloc) = (select xrto, xrtoloc
from foodwork/vsaxref
where sbcmp = xrcmp and
sbitem = xrfrom and
sbloc = xrtoloc)
where exists (select 1
from foodwork/vsaxref
where sbcmp = xrcmp and
sbitem = xrfrom and
sbloc = xrtoloc)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Young
Gesendet: Wednesday, August 01, 2007 19:13
An: midrange-l@xxxxxxxxxxxx
Betreff: SQO Join Update Question


What is wrong with the statement:
update foodwork/vsadetl set sbitem = (select xrto from
foodwork/vsaxref where sbcmp = xrcmp and sbitem = xrfrom and sbloc
= xrtoloc) , sbloc = (select xrtoloc from foodwork/vsaxref where
sbcmp = xrcmp and sbitem = xrfrom and sbloc = xrtoloc) where exists
(select 1 from foodwork/vsaxref where sbcmp = xrcmp and sbitem =
xrfrom and sbloc = xrtoloc)

What I want to do is update the item & location in vsadetl with the values
from the vsaxref file where the company, item & location match the from data
in the vsaxref. When I run the statement, I get a message indicating no
records found, but I know that there are matches.

TIA,


Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3 IBM Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3