STRQMQRY problem

30 pts.
Data analysis
Lifecycle development
Software Quality Assurance
Software testing
I've used STRQMQRY command to pass a numeric company code to the query. Unfortunately, everytime I execute the query, it prompts me to enter the company number variable. I would like to execute the query without having to prompt. Could someone in the AS400 development world assist me with this problem. Please confirm... An example of my query are as follows: PGM DCL VAR(&USER) TYPE(*CHAR) LEN(10) DCL VAR(&JOB) TYPE(*CHAR) LEN(10) DCL VAR(&NBR) TYPE(*CHAR) LEN(6) DCL VAR(&LIB) TYPE(*CHAR) LEN(10) DCL VAR(&OSCOMP) TYPE(*CHAR) LEN(2) DCL VAR(&COMP#) TYPE(*CHAR) LEN(2) VALUE('10') RTVJOBA JOB(&JOB) USER(&USER) NBR (&NBR) RTVOBJD OBJ(WBSHPTRNQF) OBJTYPE(*FILE) RTNLIB (&LIB) ADDLIBLE PKMANH182D RMVLIBLE QTEMP MONMSG CPF0000 ADDLIBLE QTEMP *FIRST CRTDUPOBJ OBJ(WBSHPTRNQF) FROMLIB(*LIBL) OBJTYPE(*FILE) + TOLIB(QTEMP) OVRDBF FILE(WBSHPTRNQF) TOFILE(QTEMP/WBSHPTRNQF) CHGVAR VAR(&OSCOMP) VALUE(10) CLRPFM FILE(QTEMP/WBSHPTRNQF) MONMSG CPF0000 STRQMQRY QMQRY(HBROLIB/WB070QM) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WBSHPTRNQF) ALWQRYDFN(*NO) SETVAR(('OSCOMP' &OSCOMP)) endpgm In the above query, SQL statement is populated in hbrolib/WB070QM. The SQL statement is described below: select oscomp, osord#, osshp#, o3casn, osmtsd, osmttn, ossvia, o3soto, o3shto, o3pctl, o3trkn, o3svia, o3proc, o2pkln, o2styl, o2ssfx, o2sdim, o2paku, pkicls, pkiven, pkisty, pkiclr, pkisiz, xipcls, xipvnd, xipsty, xipclr, xipsiz, xprd, xclr, xsiz, xprdd from nshpmnt join o3oput01 on o3casn = osctnid and &OSCOMP = oscomp <---Variable location join o2oput02 on o3pctl = o2pctl join pkxrefp on pkstyl = o2styl and pkssfx = o2ssfx and pksdim = o2sdim join itmxrefp on xipcls = pkicls and xipvnd = pkiven and xipsty = pkisty and xipclr = pkiclr and xipsiz = pkisiz REMINDER: The query works, it just prompts me to input the company number.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Do you want to remove the prompt because it’s always the same company? It looks like you want company 10 from your DCL statements… you can change the setvar to use the &COMP# rather than the &OSCOMP variable.


You have &OSCOMP declared as a *CHAR — is oscomp in the table a CHAR or is it a numeric data type? From there, you have this line in your query:
<pre>and &OSCOMP = oscomp</pre>
That relates to this in your CL:
<pre>CHGVAR VAR(&OSCOMP) VALUE(10) </pre>
<pre>SETVAR((‘OSCOMP’ &OSCOMP)) </pre>
When the value [10] gets substituted into the query, the query clause becomes:
<pre>and 10 = oscomp</pre>
Now, when SQL sees that, it interprets it as “…and when numeric 10 equals the value in the OSCOMP column”. If oscomp is a character column and may contain any non-digit characters, then the numeric 10 won’t compare properly. You would need to specify “character” 10. You would need to put quotes around it.

Unfortunately, you can’t simply put quotes around your substitution variable name because that would make QM think the name was a literal that it should ignore. Instead, you would have to add the quotes to the [10] that you pass into the query.

But that shouldn’t give the behavior that you report. If oscomp is CHAR in the table and only contains digits, the query should work. I <i>think</i> it will try to cast oscomp as an INT(4).

What you say is that the OSCOMP substitution variable is prompted even though you correctly pass the variable name as ‘OSCOMP’ using all upper-case and your substitution variable name is &OSCOMP which is also all upper-case. The two should match.

It’s a little odd that you’re using a literal value [10] in the table-1 position of a JOIN clause join condition, though, rather than naming the column from nshpmnt that the join should be over.

Also, the naming convention of your columns makes it look as if osctnid is a column from o3oput01 and o3casn is a column from nshpmnt. I don’t think it’s wrong, just a little misleading — unless, of course, that’s how the relationships go. Which is perhaps misleading in the other direction.

I can’t see anything actually “wrong”. Just oddities. Maybe all together they add up to confusion for QM.

I think I would run under debug to see what shows up in the joblog that is related to the issue. I’ve never debugged a QM query, but SQL is pretty good at it.


Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheQuigs
    Try taking the quotes (apostrophe actually) of of the veriable name in the SETVAR parameter. i.e., SETVAR((OSCOMP &OSCOMP))
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: