not a valid Query Management variable.

5 pts.
Tags:
CL
CL programming
CL variables
iseries v5r4
QMQRY
Query Management Facility
STRQMQRY
Variables
Hi, can someone please help me, I am trying to use the QMQRY function to run an update statement. I pass it a variable from the program. Get the following error. is not a valid Query Management variable. Error found on STRQMQRY command. Function check. CPF0001 unmonitored by PGMFCTSEC at statement 3500, instruction X'0021'. Can anyone help? Thanks

 

Program

0021.00      DCL   VAR(&T01.SCTRAN) TYPE(*CHAR) LEN(10)                                   0022.00  /*        DCL        VAR(&FCT) TYPE(*CHAR) LEN(10)  */                           0023.00      DCLF  FILE(SECTEMP/UNUSEDOUT)                                                0024.00                                                                                   0025.00      CLRPFM     FILE(SECTEMP/UNUSEDOUT)                                           0026.00                                                                                   0027.00              RUNQRY     QRY(SECTEMP/UNUSEDFCT) /* GENERATE LISTING +              0028.00                           OF UNUSED FUNCTIONS */                                  0029.00                                                                                   0030.00          /* READ IN UNUSEDOUT FILE THAT CONTAINS A LISTING OF UNUSED FUNCTIONS */ 0031.00                                                                                   0032.00   LOOP:   RCVF                                                                    0033.00     MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF))                                  0034.00                                                                                   0035.00              STRQMQRY   QMQRY(SECTEMP/FCTUPDATE) OUTPUT(*OUTFILE) +               0036.00                           OUTFILE(SECTEMP/FCTOUTPUT) +                            0037.00                           SETVAR('FCT' &T01.SCTRAN)                               0038.00                                                                                   0039.00     GOTO CMDLBL LOOP)                             

0040.00                                                                         0041.00                                                                         0042.00                                                                         0043.00 EOF:                                                                    0044.00              SNDMSG     MSG('***** Function Update Completed *****') +  0045.00                           TOUSR(SVRHUMPHP)                              0046.00 ENDPGM                                                                 

Update statement called

0001.00 UPDATE SECTEMP/SCP002 SET SCTLVL = '8' WHERE SCTRAN = &T01.SCTRAN

Any help would be gratefully appreciated... Thanks

                                                                                                                          



Software/Hardware used:
V5 R4

Answer Wiki

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

The query variable is invalid in the query and in the CL.

In the QM Query, give the variable a meaningful name, preceded by an ampersand ie. &TRAN.

in the CL, the SETVAR part of the STRQMQRY statement should read ((TRAN ‘FCT’)), where the QM Query variable is first WITHOUT THE AMPERSAND and the value that you want it changed to is second. This second value could be a variable from the CL program WITH AN AMPERSAND.

Regards

Andy M.

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

The above is generally true. Your query should probably be more like:

UPDATE SECTEMP/SCP002 SET SCTLVL = ’8′ WHERE SCTRAN = &FCT

The SETVAR() parameter that you have says to replace the QM substitution variable FCT with the value from the CL variable &T01.SCTRAN. However, you haven’t put [&FCT] anywhere in the QM UPDATE statement to indicate where the substitution ought to be made.

Although you declare &T01.SCTRAN, I don’t see where you assign any value to it. Most likely, the value comes from the file you declared — UNUSEDFCT. But if it does, then there’s no reason to declare it. The reference to the file will generate an automatic declaration for all fields in the file. BTW, it’s a little odd to have a period (.) in the middle of a CL variable name. Is that really the correct name? Or were you intending ‘T01′ to be a correlated name?

Also, you declare &T01.SCTRAN as a *CHAR (10) variable. That potentially means that you expect a character expression in your WHERE clause. Unfortunately, that usually means that a character literal is going to be used. And character literals need to be enclosed in quotes, as you did with ’8′. If you don’t want QM to interpret the value as a column name, you will have to supply quotes along with the value.

Assume &T01.SCTRAN has the value ‘ABC DEF’. After the substitution, the query above would become:

UPDATE SECTEMP/SCP002 SET SCTLVL = ’8′ WHERE SCTRAN = ABC DEF

Notice the lack of quotes around ‘ABC DEF’.

I supply quotes like this:

UPDATE SECTEMP/SCP002 SET SCTLVL = ’8′ WHERE SCTRAN = &Q&FCT&Q

…with my SETVAR() like this:

SETVAR( (Q ””) (FCT &T01.SCTRAN))

That is, I simply use a new QM substitution variabe, &Q, and I put it wherever quotes will end up in the query. Then I send in a the quote mark through the SETVAR(). Usually, I declare a CL variable as &Q defined as *CHAR (1) with VALUE( ”” ). My SETVAR() becomes:

SETVAR( (Q &Q) (FCT &T01.SCTRAN))

With that, the resulting UPDATE becomes:

UPDATE SECTEMP/SCP002 SET SCTLVL = ’8′ WHERE SCTRAN = ‘ABC DEF’

You have to supply the quotes yourself; you can’t code them into the UPDATE statement around a substitution variable. If you do, then QM will assume that you want the literal ‘&FCT’ rather than the substitution variable inside quotes. It’s a detail that will trip you up a couple times, but pretty easy to get around.

One minor note — You have FCT surrounded by quotes in your SETVAR(). The only reason to do that would be if your substitution variable name had lower-case letters like:

UPDATE SECTEMP/SCP002 SET SCTLVL = ’8′ WHERE SCTRAN = &Q&fct&Q

You’d then need to use this:

SETVAR( (Q &Q) (‘fct’ &T01.SCTRAN))

…in order to preserve the lower-case name.

Tom

Discuss This Question:  

 
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

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