On 21 May 2013 10:03, Anderson, Kurt wrote:
Is there a way to merge these two statements into one?
While probably not the impetus for the inquiry... Given the number of
rows [in total being deleted], does not exceed [the fairly large]
limits, both statements could add a WITH-isolation clause to effect the
operation under commitment control, and then both statements followed by
a COMMIT. They are then performed in the same transaction, even if not
the same statement. Or the unchanged scripted requests could be run in
an environment with that isolation level already established, or
established with the SET TRANSACTION ISOLATION LEVEL.
It's not really necessary, but if there is a way I'd like to know
about it.
Note: we aren't using referential integrity.
IBM i 7.1
The relationship of the data is not entirely obvious to me, so
whether this could be resolved by issuing a DELETE on a parent in a
relationship established with a referential constraint defined using the
ON DELETE CASCADE is also not obvious. But because the second DELETE
seems not to verify any data relationships, I suppose not. That delete
removes even rows that have no matching row in cdrMstP.
-- First delete the summary CDRMSTP records
delete
from cdrmstp a
where exists (select * from cdrmiscp b
where a.editid = b.editid
and a.cdrseq# = b.cdrseq#
and b.misccode = 'SUM' );
-- Then delete the Summary code records
delete from CdrMiscP where misccode = 'SUM';
To directly effect what is requested, i.e. to enable a single DELETE
statement to effect the deletion of the rows from the two TABLEs...
Something like the following should work. First, a VIEW that selects
whatever is necessary to identify the rows that are to be deleted from
each TABLE. Second, an Instead-Of-Trigger (IOT) is generated to effect
the deletion of those selected or otherwise identified rows, when a
DELETE statement is issued against the VIEW. The following example
assumes the /selected/ [key] rows can be deleted, but in comments there
is alternate code in both the VIEW and the TRIGGER that more closely
mimic what was actually asked of the second DELETE. I do that, because
although the IOT can effect almost anything, I would not prefer to
design something so confusing in its actual usage.
Sorry, I can not test/verify this script is functional:
create table cdrMstP (editId int, cdrSeq# int)
; -- example given "summary CDRMSTP" file
create table cdrMiscP (editId int, cdrSeq# int, miscCode char(3))
; -- example given "Summary code" file
create view cdrMstMisc as
( select distinct editId , cdrSeq# , char('XXX') as miscCode
/* or perhaps miscCode defined as: cast(NULL as char(3)) */
from cdrMstP s
join cdrMiscP i /* join vs exists... just because */
on s.editId = i.editId
and s.cdrSeq# = i.cdrSeq#
and i.miscCode = 'SUM'
union all
select distinct editId , cdrSeq# , miscCode
from cdrMiscP
where miscCode = 'SUM'
/* requested instead, but not so /nice/ for a WHERE on DELETE:
select 0 as editId , 0 as cdrSeq# , char('SUM') as miscCode
from cdrMiscP
group by '1'
*/
)
; -- VIEW selects all rows desired to be deleted
create trigger cdrMstDlt INSTEAD OF DELETE
on cdrMsgMisc
referencing old as O for each row mode db2sql
begin
case o.miscCode
when 'SUM' then
delete from cdrMiscP as I
where i.editId = o.editId
and i.cdrSeq# = o.cdrSeq#
and i.miscCode = o.miscCode /* same as = 'SUM' per CASE */
;
/* requested instead, but not so /nice/ for a WHERE on DELETE:
delete from cdrMiscP as I
where i.miscCode = o.miscCode -- same as = 'SUM' per CASE
;
*/
else /* miscCode could be the NULL value or 'XXX' per VIEW */
delete from cdrMstP as S
where s.editId = o.editId
and s.cdrSeq# = o.cdrSeq#
;
end case ;
end
; -- Delete of rows from the VIEW now effect DELETE from TABLEs
Given the selection by editId and cdrSeq# are desirable, as coded,
the above TRIGGER will allow either a DELETE without an WHERE-clause or
a more intuitive statement like the following which could perform
selection on one of the columns included in the VIEW:
delete from cdrMstMisc where editId=:hvId
However by removing from the TRIGGER both the current DELETE
statement for cdrMiscP and the comment lines surrounding the more
generic DELETE statement that follows, plus performing similar removal
actions for both the current and commented subquery in the VIEW, then
the DELETE against that VIEW would only be intuitively functional when
there is either no WHERE-clause on the DELETE. The non-intuitive effect
of deleting from cdrMstMisc would require using some predicate which
includes also the zero-value for editId [and that implementation
*assumes* a zero value is reserved for this special case]. For example:
delete from cdrMstMisc where editId in (0 , :hvId)
midrange.com
