× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Vern,

What your missing is that I could be dealing with more than one
date....so I'll need to possibly invoke the delete file multiple
times.

Given that we where already using SQL to delete the records, thus knew
what dates where effected in the SQL, being able to invoke the delete
of the stream file from the SQL statement, _seemed_ a simple enough
request.

Charles

On Wed, May 12, 2010 at 4:27 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:
Using SQL seems the hard way to do it. Use QCMDEXC, passing it a
variable for the date that uses RPG date functions to get current date
less 1 month. No need for even yet one more program just to have a UDF.

Maybe I'm missing something, but a UDF here violates the KISS principle
for me.

Eh?
Vern

Charles Wilt wrote:
All,

We've got an existing SQLRPGLE program that contains basically the statement:

delete from mytable
  where datefld <= CURRENT_DATE - 1 month;


Datefld is non-unique, so many records could have the same date.

One of the other developers came to me and asked if there was a way to
call a CL program that will delete a .ZIP file with a naming
convention that includes the date for any dates being deleted from the
table.  Example:  MY_ARCHIVE_yyyyy-mm-dd.ZIP

The developer already had a CL program that when passed the date,
would delete the .ZIP.

My first thought, sure we can create a user defined function to call
the CL during the execution of the SQL statement...
delete from mytable
  where datefld <= CURRENT_DATE - 1 month
     and Delete_Zip_File(char(datefld,ISO)) = '0';


Anybody see a problem with this?  (hint consider non short circuit
evaluations ;)

Then I thought well we'll just run two statements:
with tbl as (select distinct datefld
                   from mytable
                 where datefld <= CURRENT_DATE - 1 month
                )
select Delete_Zip_File(char(datefld,ISO))
from tbl;

delete from mytable
  where datefld <= CURRENT_DATE - 1 month;


But the compiler doesn't like me doing a SELECT instead of a SELECT INTO...

Anyway, the developer is looking at just using RPG RLA to delete the
rows, calling the CL program normally when the date changes.

But I thought it's an interesting issue and I figured I thought it out
here to see if anybody had any better ideas.

Thanks!
Charles Wilt

--
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 thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.