?
Solved

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

Posted on 2007-11-15
9
Medium Priority
?
2,053 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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
 
LVL 27

Accepted Solution

by:
tliotta earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
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.

741 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