STRQMQRY problem

10 pts.
Tags:
AS/400
Certifications
Data analysis
Development
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>
and
<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.

Tom

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TheQuigs
    Try taking the quotes (apostrophe actually) of of the veriable name in the SETVAR parameter. i.e., SETVAR((OSCOMP &OSCOMP))
    0 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following