Link to home
Start Free TrialLog in
Avatar of whosoever
whosoever

asked on

Cmd Lang. - declare a date variable from a character parm ?

i am passing a parm to a cl that calls a query and the query is comparing the character parm to a date and i'm gettin an error.

can i create a date field and move the character value to it?
Avatar of ocgstyles
ocgstyles

Hi whosoever,

You can use the DATE function to convert you character representation to a date data type.  For example,

select date('11/15/2007') from sysibm.sysdummy1;

You just have to make sure the character representation can be understood by the date function.

These work:

YYYY-MM-DD
MM/DD/YYYY

Does that answer your question?

Keith
whosoever:

Yes, but the particular technique will depend upon how the "query" is being invoked. Is it a CALL to another program? Is it via STRQMQRY? via RUNQRY? via some internal function that provides SQL statement processing to CL? something else...?

And it will also depend upon what format the parm characters take. MMDDYY? YYYYMMDD? CYYMMDD? MM/DD/YYYY? other?

Basic info will help with providing best answers.

Tom
Avatar of whosoever

ASKER

Hi, I appreciate your assistance.  
  First, from a menu, i call a cl that calls an screen pgm that allows users to input select criteria, then i call this cl and pass it parms, of which date values are  included.

below is a code snippet and the error
BEGPGM:     PGM        PARM(&EXIT &RPT_BUNIT &RPT_PERIOD &RPT_YEAR +
                         &RPT_DATE1 &RPT_DATE2 &RPT_DATE3 +         
                         &RPT_DATE4 &RPT_DATE5 &RPT_DATE6 +         
                         &RPT_DATE7 &RPT_EMAIL)    
DCL        VAR(&RPT_DATE1)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE2)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE3)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE4)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE5)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE6)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_DATE7)  TYPE(*CHAR) LEN(10) 
DCL        VAR(&RPT_EMAIL)  TYPE(*CHAR) LEN(70) 
 
 DCL        VAR(&RDATE1)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE2)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE3)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE4)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE5)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE6)  TYPE(*CHAR) LEN(10)  
 DCL        VAR(&RDATE7)  TYPE(*CHAR) LEN(10)  
 
 
                                                   
 CVTDAT     DATE(&RPT_DATE1) TOVAR(&RDATE1) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE2) TOVAR(&RDATE2) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE3) TOVAR(&RDATE3) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE4) TOVAR(&RDATE4) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE5) TOVAR(&RDATE5) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE6) TOVAR(&RDATE6) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 CVTDAT     DATE(&RPT_DATE7) TOVAR(&RDATE7) +      
              FROMFMT(*MDYY) TOFMT(*ISO)           
 OVRDBF     FILE(CSSDM21) TOFILE(&EOP99/CSSDM21)         
                          */                             
 OVRDBF     FILE(QBU_ROLLU3) TOFILE(&DATALIB/QBU_ROLLU3) 
 OVRDBF     FILE(WRKBRPAVGA) TOFILE(&DATALIB/WRKBRPAVGA) 
                          */                             
 STRQMQRY   QMQRY(*LIBL/BUSAVGCSCN) ALWQRYDFN(*YES) +    
              SETVAR((BU &RPT_BUNIT) (DATE1 &RDATE1))    
                          */                       
 
ERROR
 
   10800 - STRQMQRY QMQRY(*LIBL/BUSAVGCSCN) ALWQRYDFN(*YES) SETVAR((BU  
    '05855217') (DATE1 '2006-04-23'))                                   
  Query derived from *QRYDFN object BUSAVGCSCN in SCSSPRM, code 00.     
  Comparison operator >= operands not compatible.                       
  RUN QUERY command failed with SQLCODE -401.                           
  RUN QUERY command ended due to error.                                 
  STRQMQRY command failed.                                              
  Function check. QWM2701 unmonitored by BRPC1 at statement 10800,      
    instruction X'00BB'.                                                
  QWM2701 received by BRPC1 at 10800. (C D I R)                         
  QWM2701 received by BRPC1 at 10800. (C D I R)                               
 
 
                                              

Open in new window

I HAD TO *CAT THE PARMS WITH 4 SINGLE QUOTES TO GET THIS TO WORK.

 CHGVAR     VAR(&RBU) VALUE('''' *CAT &RPT_BUNIT *CAT '''')
                                                           
 CHGVAR     VAR(&RD1)   VALUE('''' *CAT &RDATE1 *CAT '''')
 CHGVAR     VAR(&RD2)   VALUE('''' *CAT &RDATE2 *CAT '''')
 CHGVAR     VAR(&RD3)   VALUE('''' *CAT &RDATE3 *CAT '''')
 CHGVAR     VAR(&RD4)   VALUE('''' *CAT &RDATE4 *CAT '''')
 CHGVAR     VAR(&RD5)   VALUE('''' *CAT &RDATE5 *CAT '''')
 CHGVAR     VAR(&RD6)   VALUE('''' *CAT &RDATE6 *CAT '''')
 CHGVAR     VAR(&RD7)   VALUE('''' *CAT &RDATE7 *CAT '''')

Thanks for your assistance
ASKER CERTIFIED SOLUTION
Avatar of Member_2_276102
Member_2_276102

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your assistance.
Friday my
 STRQMQRY   QMQRY(*LIBL/BUSAVGCSCN) OUTPUT(*OUTFILE) +  
              OUTFILE(&DATALIB/WRKBRPAVGA) +            
              ALWQRYDFN(*YES) SETVAR((BU &RBU) (DATE1 +  
              &RD1))                                    
was working, this morning, it is not wokring and I am getting the same error?

I still have the 4 single quotes in embedded in the STRQMQRY CMD?

    12100 - STRQMQRY QMQRY(*LIBL/BUSAVGCSCN) OUTPUT(*OUTFILE)              
     OUTFILE(SCSSDRM/WRKBRPAVGA) ALWQRYDFN(*YES) SETVAR((BU '''07426941''')
     (DATE1 '''2007-11-03'''))                                              
   Query derived from *QRYDFN object BUSAVGCSCN in SCSSPRM, code 00.        
   Object format not compatible with query data.                            
   SAVE DATA command ended due to error.  
     STRQMQRY command failed.                                          
  Function check. QWM2701 unmonitored by BRPC1 at statement 12100,  
    instruction X'00F5'.                                            
  QWM2701 received by BRPC1 at 12100. (C D I R)                      
  QWM2701 received by BRPC1 at 12100. (C D I R)                      
                                                                     
    ANY SUGGESTIONS?                                                      

whosoever:

The function check refers to QWM2701 which is the "STRQMQRY command failed." message. That is an *ESCAPE that is sent from QM when problems arise.

The problems are noted in earlier *DIAG (diagnostic) messages. It looks like the important one is "Object format not compatible with query data. ". You don't show what the message identifier is for that, so I can't determine what additional info might be associated with it.

You may need to position the cursor on that message and press [F1] for additional help info or spool the joblog with *SECLVL logging.

It _looks_ as if either the underlying query or the SCSSDRM/WRKBRPAVGA outfile might have changed. The query itself seems to have run, but the result set did not match the outfile. (Best guess from limited info.) The QM SAVE DATA command seems to be the point of error.

Tom
I have already begun lookiing at that, I will let you know as soon as I find something.
Thanks,