Re: Calling CL program to delete .ZIP file in IFS as part of an SQL Delete

Luis,

The problem is that SELECT INTO only works for at most one row....

The existing CL returns '1' or '0'....

But as I can't actually use it in the delete statement, I might be
able to change it to just return '0' and then use DISTINCT with the
SELECT INTO...

Charles

On Wed, May 12, 2010 at 4:00 PM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Charles,

Have your UDF return a value, then you can have a SELECT INTO.

That said, maybe the RLA program would be more efficient...

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, May 12, 2010 at 2:57 PM, Charles Wilt <charles.wilt@xxxxxxxxx>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.

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