AS/400 QMQRY: Table Lateral Identifier Error

815 pts.
Tags:
AS/400
CL Program
QMQRY
Hi,
I have a file.That comprises of all the file names(Only one column).Now  I am reading this file through a RPG program & passing this file name to a QMQRY object on a CL program. On the QMQRY, I'm facing the below error, as it is not accepting the '&' symbol.
These are the two programs.
RPG program:
0001.00 DTABLE_NAME       S             20A                                       
0002.00 C/EXEC SQL                                                                
0003.00 C+ DECLARE C1 SCROLL CURSOR FOR SELECT TABLE_NAME FROM META_DSNCT         
0004.00 C/END-EXEC                                                                
0005.00 C/EXEC SQL                                                                
0006.00 C+ OPEN C1                                                                
0007.00 C/END-EXEC                                                                
0008.00 C/EXEC SQL                                                                
0009.00 C+ FETCH NEXT FROM C1 INTO :TABLE_NAME                                    
0010.00 C/END-EXEC                                                                
0011.00 C                   DOW       SQLSTATE='00000'                            
0012.00 C                   CALL      'FETCHCL'                                   
0013.00 C                   PARM                    TABLE_NAME                    
0014.00 C/EXEC SQL                                                                
0015.00 C+ FETCH NEXT FROM C1 INTO :TABLE_NAME                                    
0016.00 C/END-EXEC                                                                
0017.00 C                   ENDDO                                                 
0018.00 C/EXEC SQL                                                                
0019.00 C+ CLOSE C1                                                               
0020.00 C/END-EXEC
0021.00 C                   SETON                                        LR
        ****************** End of data ************************************************
CL Program:
0001.00              PGM        PARM(&TB_NAME)
0002.00              DCL        VAR(&TB_NAME) TYPE(*CHAR) LEN(20)
0003.00              DCL        VAR(&SELECT_Q) TYPE(*CHAR) +
0004.00                           LEN(10) VALUE('SELECT')
0005.00              DCL        VAR(&FIELDS_Q) TYPE(*CHAR) +
0006.00                           LEN(10) VALUE('*')
0007.00              DCL        VAR(&FROM_Q) TYPE(*CHAR) LEN(10) +
0008.00                           VALUE('FROM')
0009.00              DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) +
0010.00                           VALUE('&TB_NAME')
0011.00              DCL        VAR(&FETCH_Q) TYPE(*CHAR) LEN(10) +
0012.00                           VALUE('FETCH')
0013.00              DCL        VAR(&FIRST_Q) TYPE(*CHAR) LEN(10) +
0014.00                           VALUE('FIRST')
0015.00              DCL        VAR(&VALUES_Q) TYPE(*CHAR) +
0016.00                           LEN(10) VALUE('10')
0017.00              DCL        VAR(&ROWS_Q) TYPE(*CHAR) LEN(10) +
0018.00                           VALUE('ROWS')
0019.00              DCL        VAR(&ONLY_Q) TYPE(*CHAR) LEN(10) +
0020.00                           VALUE('ONLY')
0021.00              MONMSG     MSGID(CPF0000) EXEC(GOTO +
0022.00                           CMDLBL(ERROR))
0023.00              STRQMQRY   QMQRY(MENTIS1/QMQRY103) +
0024.00                           SETVAR((SELECT &SELECT_Q) +
0025.00                           (FIELDS &FIELDS_Q) (FROM +
0026.00                           &FROM_Q) (FILE &FILE_Q) (FETCH +
0027.00                           &FETCH_Q) (FIRST &FIRST_Q) +
0028.00                           (VALUES &VALUES_Q) (ROWS +
0029.00                           &ROWS_Q) (ONLY &ONLY_Q))
0030.00              GOTO       CMDLBL(END)
0031.00 ERROR:
0032.00              SNDUSRMSG  MSG('ERROR OCCURED')
0033.00 END:
0034.00 ENDPGM
        ****************** End of data *********************************
When I am running this CL program from the RPG.I can see the following error from the DSPJOBLOG.
   2300 - STRQMQRY QMQRY(MENTIS1/QMQRY103) SETVAR((SELECT 'SELECT') (FIELDS
   '*') (FROM 'FROM') (FILE '&TB_NAME') (FETCH 'FETCH') (FIRST 'FIRST')
   (VALUES '10') (ROWS 'ROWS') (ONLY 'ONLY'))
 Token '&TB_NAME' was not valid. Valid tokens: ( TABLE LATERAL        
RUN QUERY command failed with SQLCODE -104. 
RUN QUERY command ended due to error.       
STRQMQRY command failed.                         
   .    
I tried a lot to digest the error.But I am out of it.
Kindly let me know the problem & the solution.
Thanks


Software/Hardware used:
iseries

Answer Wiki

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

So your query is called with &TB_NAME and presumbly it contains a value .. if the option exists on the error do a dump.  Find out what value is in &TB_NAME


Possibilities are


It’s blank


It has a name that isn’t the name of an existing file


It has a name of an existing file but not in the Library List of the job.

Discuss This Question: 7  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.

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
  • ToddN2000
    Start off testing small. Have you tried running your RPG through debug and see if you file is being built properly? If so is your parm TABLE_NAME being populated correctly ? Have you tried debugging the CL to see if everything is correct for your STRQMQRY command.. I no longer have access to that command to test but it looks off a bit.
    9,700 pointsBadges:
    report
  • HABBIE
    @ToddN2000: Thanks for the reply. every thing is working fine. Problem might be with the '&' symbol before the Variable.I don't know how it is skipping from that step.Whether there is any other way to declare the variable on CL program(QMQRY) that accepts parameters from outside.
    815 pointsBadges:
    report
  • philpl1jb

    FILE '&TB_NAME'  would send the literal '&TB_NAME'

    perhaps you should have said

    FILE &TB_NAME  so it would substitute in the value stored in &TB_NAME.

     

    49,730 pointsBadges:
    report
  • HABBIE
    @philpl1jb    : Throwing an error as unable to find the file name from *LIBL.


    815 pointsBadges:
    report
  • philpl1jb

    If the file name is good and you haven't qualifed the library.file then it's the library list.  Add the necessary libary to your job before you call the query?.

    49,730 pointsBadges:
    report
  • philpl1jb
    If you aren't sure that the file name is valid.  Add a sndmsg in the cl program just before the call to the query.  Have it send you &tb_Name
    49,730 pointsBadges:
    report
  • HABBIE
    Acceptance of the file name prefixed & throws the error.
    I have replaced the below,
    0009.00              DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) +
    0010.00                           VALUE('&TB_NAME')
     with this 

    DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) 

    Now the program is accepting values at run time.I can view my output.

    Thanks every one for supporting me.

    815 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