Re: CL question

[Martin Rowe <martin@xxxxxxxxxx>]

On Fri, 2005-02-18 at 05:13 +0530, Muralidhar Narayana wrote:
> I have following code:
> Iam copying YWRKVOLF1 to qtemp/ywrkvolf1 .
> after copying (after cpyf condition and before overrdiing)I want to execute 
> delete condition like
> DELETE FROM QTEMP/YWRKVOLF1 WHERE WVYEAR > 2004 AND WVMONTH > 02.
> Can some one advise me without creating addtional files how can I achieve 
> it,Iam deleting qtemp file at the end.

You could use RUNSQLSTM with the appropriate SQL in a source member. If
you are able to install a small (free) application on the machine then
you could use my EXCSQL to run your SQL directly 

> PGM        PARM(&ACCOUNT)                                            
>   DCL        VAR(&ACCOUNT)    TYPE(*CHAR) LEN(8)                     
>  DCL        VAR(&FLIB)       TYPE(*CHAR) LEN(10)                     
>                                                                      
>   OVRPRTF    FILE(BYACT) TOFILE(PRINTERF) PAGESIZE(66) +             
>              LPI(6) OVRFLW(60)                                       
>              CPYF       FROMFILE(QS36F/YWRKVOLF1) +                  
>                           TOFILE(QTEMP/YWRKVOLF1) MBROPT(*REPLACE) + 
>                           CRTFILE(*YES) INCREL((*IF WVACCT *EQ +     
>                           &ACCOUNT))          

    EXCSQL    SQL('DELETE FROM QTEMP/YWRKVOLF1 WHERE WVYEAR > 2004 AND +
                WVMONTH > 02')

>                        
> OVRDBF     FILE(YWRKVOLF1) TOFILE(QTEMP/YWRKVOLF1)                   
> CALL       PGM(YVOLSTATR2) PARM(&ACCOUNT)                            
>   DLTF       FILE(QTEMP/YWRKVOLF1)                              
> DLTOVR     FILE(YWRKVOLF1)                                           
> MONMSG     MSGID(CPF0000)                                            
> DLTOVR     FILE(*ALL)                                                

The SQL parm supports expressions if you need to set the year & month
dynamically. 

Details & source download from http://www.dbg400.net/excsql.html 

Regards, Martin
-- 
martin@xxxxxxxxxx  AIM/Gaim: DBG400dotNet  http://www.dbg400.net       /"\
DBG/400 - DataBase Generation utilities - AS/400 / iSeries Open        \ /
Source free test environment tools and others (file/spool/misc)         X 
[this space for hire]  ASCII Ribbon Campaign against HTML mail & news  / \