?
Solved

Passing parm to QM query

Posted on 2007-10-15
7
Medium Priority
?
865 Views
Last Modified: 2013-12-06
I am passing two parm to a qm query and they work only 50% of the time and 50% of the time the query fails. Please advise
0
Comment
Question by:dllssn
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:tliotta
ID: 20081098
dllssn:

Without seeing (1) how the substitution variable is specified in the QM query source, nor (2) how you're passing the value, it's impossible to know what's being done wrong.

It might be improper case on the substitution variable name -- the names are case-sensitive. It might improper quoting -- SQL character values can require quotes around the values. It might be something else entirely.

Please provide source for the definition or usage in the query and the source where the value is supplied.

Tom
0
 

Author Comment

by:dllssn
ID: 20081304
Here is the code:

DCL        VAR(&CNA) TYPE(*CHAR) LEN(7)      
DCL        VAR(&LNA) TYPE(*CHAR) LEN(8)        
DCL        VAR(&SRQ) TYPE(*CHAR) LEN(4)        
                                                 
STRQMQRY   QMQRY(CVARFILEB) OUTPUT(*OUTFILE) +            
             OUTFILE(QTEMP/CVARFILE) +                    
             ALWQRYDFN(*YES) SETVAR((STRC &SRQ) (CTNO +    
             &CNA) (LIST &LNA))                            

Query Select:

AND/OR  Field             Test   Value (Fie
        CONO77            EQ     'JB'      
 AND    STRC77            EQ     :STRC    
 AND    CTNO77            EQ     :CTNO    
 AND    ACNO77            EQ     :LIST    
 AND    QDIFF             NE     0        
                                           
0
 
LVL 27

Expert Comment

by:tliotta
ID: 20081603
dllssn:

The definitions of STRC77, CTNO77 and ACNO77 aren't shown, but they are likely character variables. As such, they probably need to be surrounded by quotes when they're executed within the query. The STRQMQRY command that you show doesn't _appear_ to supply any quotes for those values.

In order to supply a quote-mark as part of the substitution value while maintaining all rules for quotes in a command, you'll need to have two consecutive quote-marks and surround those in quote-marks. And you'll have to have those at the beggining _and_ end of all three values. Something like this:

...SETVAR((STRC ('''' *cat &SRQ *cat '''')) (CTNO +    
             ('''' *cat &CNA *cat '''')) (LIST ('''' *cat &LNA *cat '''')))

Now when those values arrive inside of STRQMQRY, they will have quote-marks surrounding them. The quote-handling has to do with the use of STRQMQRY where there are conditions where quotes are not needed around character substitution values. Since you're not going through QM query to create the query, it's hard to show any QM query example where character values won't need quotes. So, it kind of has to be taken on faith that that's how it works.

There are various ways to simplify the coding, but that's the direct route to getting values quoted for substitution. Note that each of the three now has an additional set of parentheses to delimit the "expressions".

Personally, the _very_ first thing I'd do is dump the old Query/400 definition and use a QM query instead. But if you don't have the SQL Developer Kit installed, you might have reason to stick with the old Query/400 stuff.

Tom
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:dllssn
ID: 20086087
 10  T01.STRC77        Stockroom code                                  2      
  20  T01.CTNO77        Count Number                                    7    0
  30  T01.ACNO77        Action list number                              8    0

does that help?

0
 
LVL 27

Accepted Solution

by:
tliotta earned 375 total points
ID: 20089305
dllssn:

Yes, because STRC77 is the only one that is a character value. That means that CTNO77 & ACNO77 wouldn't require surrounding quotes under nearly any circumstances.

...SETVAR((STRC ('''' *cat &SRQ *cat '''')) (CTNO +    
             &CNA) (LIST &LNA))

Keep in mind that STRQMQRY is based in SQL. An SQL SELECT would normally require quote marks around character literals. A "value" that is passed into STRQMQRY will generally be inserted into a generated SELECT statement, but the query doesn't know about the field definitions when the SELECT is being constructed.

You are generally given the responsibility of supplying quote characters at the beginning and end of any string value. You can either supply them at the time the value is given to the SETVAR() parameter or you can build a variable in your CL program that has the quotes added to the value.

There might still be details missing, but the SETVAR() above should be much closer to what you need.

Tom
0
 

Author Comment

by:dllssn
ID: 20100405
still fails 50% of the time - any other ideas?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 20103989
Immediate thought is "What is the definition of 'fails 50% of the time'?"

I.e., are there error messages? What fails? The program that executes STRQMQRY? The query itself returns an error message? The results don't include expected values? Other...?

For any error messages, ensure that the STRQMQRY is executed within a job that is set to log full error reporting. The job logging level should be LOG(4 00 *SECLVL). This can be set by using the CHGJOB command or by setting the attributes of the job description that's used for the job.

After the error condition occurs, review the joblog to locate error messages. Often, an *ESCAPE message will be signaled after one or more *DIAG messages. The diagnostic messages can hold the actual descriptions of errors while the escape message finally is the signal indication of errors reaching a high enough level to exit.

Post the reported conditions back here.

Tom
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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 …
Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
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.
Suggested Courses

850 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