MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: SQL and trigger s



fixed

This thread "Subject: SQL and trigger s" seems to have drifted away from the original question posed [about a delete trigger preventing *REPLACE member data processing for a CPYF request], apparently having drifted [back] to the topic of an older thread "Subject: Quick file changes". From that I have inferred "the file method" refers to the use of a data\control-record file which can be used as input for the SQL DELETE request. The message "by Charles":
http://archive.midrange.com/midrange-l/201210/msg00802.html
"...
You could also consider storing the function names in a new table
DELETE FROM BSYSQSP2
WHERE QSSEC2 <> 'xxxx'
AND QSFN01 in (select funcname from mytable)
"

Now understanding the above, and inferring both that QSFN01 is the "function" and QSSW01 is the "switch setting", the discussion now seems much clearer. And apparently the "additional parameter" required for selection is for the "switch setting", having originally noted only the need for selection by "function".

create table qtemp/RmvByFNSW (RmFN01 char(8), RmSW01 char(1))
;
insert into qtemp/RmvByFNSW values
('BOC151CL', 'E')
, ...
;
DELETE FROM SECWRK/BSYSQSP2
WHERE QSSEC2 <> '@ADR'
AND EXISTS (SELECT '1'
FROM qtemp/RmvByFNSW
WHERE QSFN01 = RmFN01
AND QSSW01 = RmSW01
)
;

Regards, Chuck

On 31 Oct 2012 09:03, John McKee wrote:
<<SNIP>>

So, I can do this two ways:

1) (the way I know) put the statements in a file and run multiple
DELETE statements.
2) Put the functions and switch settings in a file as suggested by
Charles and run a single DELETE. Thing is, I did not specify that a
function and a matching switch setting were needed. I don't know how
to do that.

Guess I will just go with what I know. Only 29 delete lines.

John McKee on 10/30/2012 05:48 PM wrote:

Finally getting to try this. I wanted to use the file method.
I neglected to include an additional parameter in my example.

Needs to be:

DELETE FROM SECWRK/BSYSQSP2
WHERE QSSEC2 <> '@ADR'
AND QSFN01 = 'BOC151CL'
AND QSSW01 = 'E'

Other values for QSFN01 have either no value for QSSW01 or some
other single character.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact