|
midrange.com
Well, it doesn't matter our shop doesn't do anything that's been described
in this subject. Which would explain my errant answers. I realized that
my solution wasn't correct and corrected my answer which was correct just
not as efficient as using the EXISTS clause
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"Hauser,
Birgitta"
<Birgitta.Hauser@ To
LP-GMBH.com> "'Midrange Systems Technical
Sent by: Discussion'"
midrange-l-bounce <midrange-l@xxxxxxxxxxxx>
s@xxxxxxxxxxxx cc
Subject
04/04/2006 02:41 AW: SQL - Updates with joins
AM
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
Hi Michael,
joined logical files are not updateable, either.
And you never should use a DDS-described logcial file in an SQL-statement.
All SQL-statements where DDS described logical files are specified get
rerouted to the old (classic) Query Engine (CQE).
Not only the advantages of the new (SQL) Query Engine (SQE) cannot be
exploited, this rerouting may cost up to 10-15% performance.
With release V5R4M0 (or even with some PTFs in release V5R3M0) instead of
triggers can be used to update SQL views that join several tables.
Without registering an instead of trigger for the view, an update of the
view will fail.
In the following article you'll find a nice example:
Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers
http://www.itjungle.com/fhg/fhg030806-story01.html
Birgitta
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Michael_Schutte@xxxxxxxxxxxx
Gesendet: Montag, 3. April 2006 21:03
An: Midrange Systems Technical Discussion
Betreff: RE: SQL - Updates with joins
I was talking about creating a Logical file but stated view instead.
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"Elvis Budimlic"
<ebudimlic@center
fieldtechnology.c To
om> "'Midrange Systems Technical
Sent by: Discussion'"
midrange-l-bounce <midrange-l@xxxxxxxxxxxx>
s@xxxxxxxxxxxx cc
Subject
04/03/2006 02:55 RE: SQL - Updates with joins
PM
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
Joined file SQL view is likely to be read-only, hence it would not
updateable.
John needs to review UPDATE syntax a bit as there is no FROM in the base
UPDATE syntax (there is in subselects, which are often used in UPDATEs for
join purposes).
I'll give it a go for your UPDATE statement, but make sure you test it
first
John:
UPDATE rugd80dat.asbacpp r1
SET bai1tx = 'N', bai2tx = 'N'
WHERE (r1.baartx = 'APV' or r1.baartx = 'MPV' or r1.baartx = 'SPV' or
r1.baartx = 'EPV')
AND r1.bai1tx = ' ' and r1.bai2tx = ' ' AND
EXISTS (SELECT 1 FROM rugd80dat.pmsp0200 r2
WHERE r1.baartx = r2.symbol and r1.baastx = r2.policy0num and r1.baadnb =
r2.module AND r2.trans0stat = 'V' and r2.renewal0cd = '1')
Elvis
-----Original Message-----
Subject: Re: SQL - Updates with joins
I've never seen an update statement with a FROM clause or a JOIN for that
matter. I suggest creating a view that combines the two files and then
run the update over the new view.
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"John Candidi"
<jacandidi@rutger
sinsurance.com> To
Sent by: <midrange-l@xxxxxxxxxxxx>
midrange-l-bounce cc
s@xxxxxxxxxxxx
Subject
SQL - Updates with joins
04/03/2006 02:18
PM
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
I am having a problem generating the correct SQL script run in iSeries
Navigator.
This is what I have so far:
Update rugd80dat.asbacpp
set bai1tx = 'N', bai2tx = 'N'
from rugd80dat.asbacpp r1,rugd80dat.pmsp0200 r2
where (r1.baartx = r2.symbol and r1.baastx = r2.policy0num and r1.baadnb =
r2.module)
and (r1.baartx = 'APV' or r1.
baartx = 'MPV' or r1.baartx = 'SPV' or r1.baartx = 'EPV')
and (r1.bai1tx = ' ' and r1.bai2tx = ' ')
and (r2.trans0stat = 'V' and r2.renewal0cd = '1')
When run as a select statement, it returns the correct rows. When changed
to do the actual update, it reports an unexpected 'from'. Any
recommendations? I tried with a 'where exists' with a select subquery, but
it ended up hitting all the rows in the asbacpp table, not just the handful
in the query result.
John A Candidi
Rutgers Insurance Companies
IT Director - AS/400 Manager
856-779-2274
--
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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
