Solved

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

Posted on 2007-11-15
9
1,977 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

23 Experts available now in Live!

Get 1:1 Help Now