Re: running sql from a cl to update

[Ravi <ravi@xxxxxxxxxxxxx>]

When you CHGVAR &FRDTE and &TODTE, you need to *CAT four  single quotes
'''' before and after the variable.
Below is a piece of code that I use:
(ex)
             
                                                     
    CHGVAR     VAR(&CONT1) VALUE('''')  
    CHGVAR     VAR(&CONT3) VALUE('''')               
    CHGVAR     VAR(&CONT) VALUE(&CONT1 *TCAT +      
                 &CONT2 *TCAT &CONT3 )  /* &CONT2 contains value that
should  be passed to query */         
   STRQMQRY   QMQRY(AKBLIB/ARM073Q1) OUTPUT(*OUTFILE) +     
                OUTFILE(QTEMP/ARM073WRK) OUTMBR(*FIRST) +   
                SETVAR((CONTR &CONT))                      
    
Hope this helps.

Ravi                                                              


Wawrzaszek wrote:
> 
> I am trying to delete records from a file using an sql statement that I send
> parms to.  I get the following message when I run the CL:
> "Type a value for variable "frdte" and press Enter"
> 
> The variable should be the parm I am sending in.  I have included my code,
> If someone could point me in the right direction I would appreciate it!!!
> 
> SQL:
> delete from library/file where field1 >= &frdte and field1 <= &todte
> field1 is 8,0
> 
> CL:
> PGM
>    DCL     VAR(&DATE) TYPE(*CHAR) LEN(6)
>    DCL     VAR(&DATE8) TYPE(*CHAR) LEN(8)
>    DCL     VAR(TDTE8) TYPE(*CHAR) LEN(8)
>    DCL     VAR(&FRDTE) TYPE(*CHAR) LEN(8)
>    DCL     VAR(&TODTE) TYPE(*CHAR) LEN(8)
> 
>    RTVSYSVAL SYSVAL(QDATE) RTNVAR(&DATE)
>    CVTDAT   DATE(&DATE) TOVAR(&DATE8) FROMFMT(*MDY) +
>                          TOFMT(*YYMD) TOSEP(*NONE)
>    CALL        PGM(DAT8TIME) PARM(&DATE8 ' ' ' ' ' ' &TDTE8 +
>                        '000060' 'sd')  /*  PGM WILL SUB #DAYS PASSED IN FROM
>                                                        FROM DATE - SUB 60
> DAYS) */
>    CHGVAR  VAR(&FRDTE) VALUE(&TDTE8)
>    CHGVAR  VAR(&TODTE) VALUE(&DATE8)
> 
>    STRQMQRY   QMQRY(library/sql)  SETVAR((FRDTE &FRDTE) +
>                               (TODTE &TODTE))
> 
> ENDPGM
> 
> If I look at the joblog, it looks fine:
> 
> strqmqry qmqr(library/sql) setvar((frdte '19981024') (todte '19981223'))
> 
> If I change strqmqry to alwqrydfn(*only) i get error object sql not found in
> library.  However I can do a dspobjd and find this sql query.  I even
> recreated it using CRTQMQRY
> 
> What am I missing?????
> Thanks in advance.
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---