Solved

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

Posted on 2007-11-15
9
1,983 Views
Last Modified: 2013-12-06
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?
0
Comment
Question by:whosoever
  • 5
  • 3
9 Comments
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20295326
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 20295559
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
0
 

Author Comment

by:whosoever
ID: 20298482
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

0
 

Author Comment

by:whosoever
ID: 20299483
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
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 20301437
whosoever:

A QM query involves a SQL statement. Comparison of a date _might look similar to:

  select col1, col2, col3 from mytable where col2 = &MYDATE

The &MYDATE substitution variable might receive a value like 20071116. But note the resulting statement:

  select col1, col2, col3 from mytable where col2 = 20071116

If col2 is a NUMERIC column, that would be a valid comparison. But if col2 is any kind of non-numeric column, the comparison must be against a quoted string.

QM queries will not insert quotes for you; you must supply them. As a further complication, if you try to place the substitution variable in quotes, e.g.:

  select col1, col2, col3 from mytable where col2 = '&MYDATE'

...QM will ignore the variable, assuming it's simply a quoted literal.

Your resolution puts the quotes into the actual value, which is a common way to do it. I personally prefer an alternative:

  select col1, col2, col3 from mytable where col2 = &q&MYDATE&q

Wherever I want a quote to appear, I put the substitution variable &q. Then my STRQMQRY command is like:

STRQMQRY   QMQRY(*LIBL/BUSAVGCSCN) ALWQRYDFN(*YES) +    
              SETVAR((BU &RPT_BUNIT) (DATE1 &RDATE1) (q ''''))

Note that substitution variable names are case-sensitive. Also note that a substitution variable can be used many times in a single SQL statement. This leaves the specification of where to place quotes inside the QM query and the CL only needs to provide actual values. There's no need for the CL programmer to know where quotes might be needed and the concatenation operations are eliminated.

Tom
0
 

Author Closing Comment

by:whosoever
ID: 31409444
Thanks for your assistance.
0
 

Author Comment

by:whosoever
ID: 20313737
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?                                                      

0
 
LVL 27

Expert Comment

by:tliotta
ID: 20315521
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
0
 

Author Comment

by:whosoever
ID: 20315686
I have already begun lookiing at that, I will let you know as soon as I find something.
Thanks,
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Staging desktop / laptop tools 5 100
Windows 8,8.1 & 10 product key identification question 12 174
Thin secure Windows 10 5 75
find age for two dates 5 35
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now