STRQMQRY

85 pts.
Tags:
CLP
STRQMQRY
If you use a CLP to pass parameters to a STRQMQRY command. How do you disable prompting this command.
Asked: Last updated:
Related Questions
1

Answer Wiki

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

I am assuming you would want to do something like this

CHGVAR VAR(&VAR1) VALUE(‘”‘ || &PARM || ‘”‘)
where &PARM is the parm either passed into the CL pgm or set within the CL program.

STRQMQRY QMQRY(yourquery) SETVAR((QRYVAR &VAR1))

THIS CLP CONTAINS ALREADY THE PARMS USED IN THE STRQMQRY, I JUST WANT TO DISABLE THE PROMPT FOR THE SETVAR in THE STRQMQRY:
PGM PARM(&CDTEI &CCAMI &CYERI)
DCL VAR(&CDTEI) TYPE(*CHAR) LEN(7)
DCL VAR(&CCAMI) TYPE(*DEC) LEN(11 2)
DCL VAR(&CYERI) TYPE(*DEC) LEN(2 0)
DCL VAR(&CDTE) TYPE(*CHAR) LEN(7)
DCL VAR(&CCAM) TYPE(*CHAR) LEN(11)
DCL VAR(&CYER) TYPE(*CHAR) LEN(2)

CHGVAR VAR(&CDTE) VALUE(&CDTEI)
CHGVAR VAR(&CCAM) VALUE(&CCAMI)
CHGVAR VAR(&CYER) VALUE(&CYERI)

STRQMQRY QMQRY(AXMTESTLIB/NEWQRY) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/OLAUDDDU21) ALWQRYDFN(*NO) +
SETVAR((BLCLAS &CDTE) (BLRCAM &CCAM) +
(LICYER &CYER))

RETURN

==================================================================

You don’t actually disable prompting; it’s more accurate to say that you eliminate the need for prompting by passing the correct parameters into the STRQMQRY command. Prompting occurs when the parameters don’t match.

The most common mistake is that the QM substitution variable names are case-sensitive with lower-case letters and the CL was coded to pass in names that are all upper-case. Ensure that the names match exactly and prompting will disappear.

Tom

Discuss This Question: 11  Replies

 
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.
  • sorella
    If I run STRQMQRY in batch mode I can bypass the prompt, but the command fails with error QWM2701.
    85 pointsBadges:
    report
  • Yorkshireman
    Then you want to run the clle in debug and check what's going on Capture the precise command as constructed and run it from the command line.
    6,085 pointsBadges:
    report
  • JSellick
    Have you enclosed the variables with quotes? If not, QMQRY won't recognise them as valid values and will prompt. When running in batch it can't prompt, which is why the job fails. Each variable needs to be enclosed in quotes, so for example &CDTE must be defined with length 9. Then CHGVAR VAR(&CDTE) VALUE( '''' *CAT CDTEI *CAT '''')
    115 pointsBadges:
    report
  • TomLiotta
    Variables do not need to be enclosed in quotes. Substitution variable names might need to be inquotes if the names contain lower-case letters -- QM substitution variable names are case-sensitive. Use upper-case names and quotes aren't needed. Also, the SQL statement might contain elements that rely on quotes. E.g.: 
    select col1, col2, col3 from table where col3='literalvalue'
    For that statement, you might create your QM query like this: 
    select col1, col2, col3 from table where col3=&PARM1
    Because you removed the quotes when you put the substitution variable in, you have to replace the quotes when the substitution value is passed in. It seems that most people will concatenate quote-marks with the literal value in CL before passing it in. I prefer a different way: 
    select col1, col2, col3 from table where col3=&Q&PARM1&Q
    I simply mark any spot where I want a quote-mark with a new substitution variable -- &Q. The STRQMQRY SETVAR() parameter can then include SETVAR((Q '''') (PARM1 'literalvalue')) The Q variable simply gets the quote-mark and it's placed wherever I marked it. Tom
    125,585 pointsBadges:
    report
  • HishamKhafagi
    also check if you pass a character variable then you need to pass it's lenght + 2 to anther variable which must contain single cottation + your variable + single cottation ( ' *cat &var *cat ')
    25 pointsBadges:
    report
  • BigKat
    Tom Since this was popped back onto the active questions, I like your "trick" of coding the &Q parm for the single quote. It makes it easy to get them in the right places, but do you find it confuses others who may have to support the code in the future? It seems that someone might look at the query, before they looked at the CL and wouldn't know what the &Q&PARM1&Q was doing.
    9,460 pointsBadges:
    report
  • TomLiotta
    It seems that someone might look at the query, before they looked at the CL and wouldn’t know what the &Q&PARM1&Q was doing. I wouldn't be expecting someone to be making changes without also testing. I'm sure it could happen, but what would they do? Surely they're not going to be removing substitution parameters without knowing why they're there. If they do, then I'm not clear what protections could be put in place. But note that I used the variable name "Q" mostly for brevity. How about using the name "apost" or "SnglQuote" instead? The value in "Q" is shortness for examples and whatever help it gives keeping the SQL simple. Perhaps it also provokes a little curiosity. Some research into why it's there could be valuable in learning what substitution variables actually and how they can be used to advantage. Is it better that the technique is never seen? Tom
    125,585 pointsBadges:
    report
  • AndyDim
    You will need to turn your query into a type of SQL instead of PROMPT by taking option 10 (Convert to SQL) from the Work with Query Manager Queries screen.
    25 pointsBadges:
    report
  • TomReiner
     ************************** Beginning of D
     Insert into TREINER/PPPROD03TR           
     Select * from v91mod/PPPROD03SV          
       where (ADATE = &ADATE)                 
     ***************************** End of Data
    The ADATE in the DDS of the file is defined as a date field.  Trying to pass a calculated date from a CLP w/ setvar but continue to receive a prompt to enter the date.  Can anyone provide an example?

    Thanks,
    Tom
    20 pointsBadges:
    report
  • ToddN2000
    @TomRenier: What are the field definitions of the calculated date from the CLP and in the DDS. There may be an issue with the actual date format,  being an incorrect length or format. 
    135,465 pointsBadges:
    report
  • TheRealRaven
    Seems unlikely that the format would affect prompting. Prompting should only be affected by the values passed into the STRQMQRY SETVAR() parameter. An invalid format should show up as a SQL error.

    Show the STRQMQRY command, and maybe we can suggest changes. Also show the DDS and DCL data definitions in case we need to comment on the parameter data values.
    36,370 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.

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

Following

Share this item with your network: