Re: AW: SQO Join Update Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

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