Re: Nested update?

[Wes Williams <wwilliams@MAIL.RKD.SNDS.COM>]

Chuck this logic should work within an sqr. I tried it on my test DB.

begin-procedure geteq
begin-select
eqnum         &eqnum      
meterreading  &meter      
   do update_child
    from equipment
 where parent is null and meterreading > 0
end-select
end-procedure

begin-procedure update_child
 DISPLAY 'UPDATING EQUIPMENT RECORDS'
begin-sql
  update equipment set meterreading = &meter
  where parent = &eqnum
end-sql
end-procedure

Using a subselect would be nice, but SQLBase doesn't allow an update based on a 
subselect.

Regards
Wes
>>> Chuck King <Chuck.L.King@SEALEDAIR.COM> 06/01 1:57 PM >>>
SQR report writer v3.0.13
Windows NT v4.0 SRV PK3
SQLBase v6.0.1
Maximo v3.0.2

Hello fellow SQR gurus!

I'll layout the problem I have to solve:

I have parent equipment hierarchy number of  004000000 with many
children hierarchy numbers below such as: 004010000, 004010100,
004010101, 004020000...etc.

I need an update statement that will update each childs equipment meter
reading from the parents meter reading.  This way no matter how many
children are created in the future the update will continue to work.

The database base column names are:

COLUMN NAME             DESCRIPTION
----------------------------------------------------------------------
-----------
EQNUM                   equipment number
METERREADING    equipment meter reading


Do I need a nested update statement?  Could someone provide me a code example?


Sincere thanks,

Chuck L. King
CMMS / LAN Admin.
Cryovac Div. of Sealed Air Corp.
Chuck.L.King@sealedair.com

Wes Williams
Sundstrand Aerospace