Problem with variables in STRQMQRY - Help ! - IBM AS400

This is a discussion on Problem with variables in STRQMQRY - Help ! - IBM AS400 ; I am having considerable difficulty attempting to execute a QMQRY from a CL program in which I am passing to the QMQRY a variable containing embedded spaces. I am aware of the importance of enclosing the variable in the "correct" ...

+ Reply to Thread
Results 1 to 7 of 7

Thread: Problem with variables in STRQMQRY - Help !

  1. Problem with variables in STRQMQRY - Help !

    I am having considerable difficulty attempting to execute a QMQRY from a CL program in which I am passing to the QMQRY a variable containing embedded spaces.

    I am aware of the importance of enclosing the variable in the "correct" number of quotes in order for the variable to be correctly resolved; however, I have used a couple of different approaches but neither of them is producing the desired result, with SQL errors being returned in both cases.

    Below I have attempted to show the variations of code which I have used in an attempt to solve the problem:

    Method 1. (Build the variable within the SQL)
    PGM
    ..
    DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    ..
    LOOP: DCLF FILE(filea)

    ..

    ..

    STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +

    (NAME &NAME) (Q &Q)) [note that the variables &AGENT and &NAME are fielkds on the input file]



    GOTO CMDLBL(LOOP)



    ENDPGM


    The value of the variable &NAME (len 30) at the time of execution of the QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is 'ABCD12345'

    The QMQRY qmqryname is as follows:

    INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)

    Referring to the job log, the resolved statement is:

    STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))

    with the resulting error message indicated:

    Token SMITH was not valid. Valid tokens : ),.
    RUN QUERY command failed with SAQLCODE -104

    Method 2. (Build the variable in the CL program)
    PGM
    ..
    DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    ..
    LOOP: DCLF FILE(filea)

    ..

    ..

    CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)

    STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +

    (NAME &NAME))



    GOTO CMDLBL(LOOP)



    ENDPGM


    with the QMQRY changed accordingly:

    INSERT INTO fileb VALUES(&AGENT, &NAME)

    Referring to the job log, the resolved statement is:

    STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME '''JOHN SMITH'''))

    with the resulting error message indicated:

    Column ABCD12345 not in specified tables.
    RUN QUERY command failed with SAQLCODE -206


    What am I doing wrong and what do I need to do to resolve it ? Can anyone please assist ?

    Thanks in advance.

    Steve

  2. Re: Problem with variables in STRQMQRY - Help !

    On Oct 30, 8:27 am, "Wingnut" wrote:
    > I am having considerable difficulty attempting to execute a QMQRY from a CL program in which I am passing to the QMQRY a variable containing embedded spaces.
    >
    > I am aware of the importance of enclosing the variable in the "correct" number of quotes in order for the variable to be correctly resolved; however, I have used a couple of different approaches but neither of them is producing the desired result, with SQL errors being returned in both cases.
    >
    > Below I have attempted to show the variations of code which I have used in an attempt to solve the problem:
    >
    > Method 1. (Build the variable within the SQL)
    > PGM
    > .
    > DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    > .
    > LOOP: DCLF FILE(filea)
    >
    > .
    >
    > .
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >
    > (NAME &NAME) (Q &Q)) [note that the variables &AGENT and &NAME are fielkds on the input file]
    >
    > GOTO CMDLBL(LOOP)
    >
    > ENDPGM
    >
    > The value of the variable &NAME (len 30) at the time of execution of the QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is 'ABCD12345'
    >
    > The QMQRY qmqryname is as follows:
    >
    > INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)
    >
    > Referring to the job log, the resolved statement is:
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))
    >
    > with the resulting error message indicated:
    >
    > Token SMITH was not valid. Valid tokens : ),.
    > RUN QUERY command failed with SAQLCODE -104
    >
    > Method 2. (Build the variable in the CL program)
    > PGM
    > .
    > DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    > DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    > .
    > LOOP: DCLF FILE(filea)
    >
    > .
    >
    > .
    >
    > CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >
    > (NAME &NAME))
    >
    > GOTO CMDLBL(LOOP)
    >
    > ENDPGM
    >
    > with the QMQRY changed accordingly:
    >
    > INSERT INTO fileb VALUES(&AGENT, &NAME)
    >
    > Referring to the job log, the resolved statement is:
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME '''JOHN SMITH'''))
    >
    > with the resulting error message indicated:
    >
    > Column ABCD12345 not in specified tables.
    > RUN QUERY command failed with SAQLCODE -206
    >
    > What am I doing wrong and what do I need to do to resolve it ? Can anyone please assist ?
    >
    > Thanks in advance.
    >
    > Steve


    Can you include your QMQRY source??


  3. Re: Problem with variables in STRQMQRY - Help !

    Actually the QMQRY sources were included. Since I do not have the
    original post, I am responding to this reply.

    In both methods it seems the same error is made. That is, both fail
    to treat the first variable as a string.

    In the "method 1" the QMQRY source has INSERT INTO fileb
    VALUES(&AGENT, &Q&NAME&Q), but it requires instead to have INSERT INTO
    fileb VALUES(&Q&AGENT&Q, &Q&NAME&Q)

    In the "method 2" the CLP gives additional apostrophes to a variable
    &NAME1, but not to a similarly extended variable &AGENT1.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    jacko wrote:
    > On Oct 30, 8:27 am, "Wingnut" wrote:
    >> I am having considerable difficulty attempting to execute a QMQRY from
    > > a CL program in which I am passing to the QMQRY a variable containing
    >> embedded spaces.
    >>
    >> I am aware of the importance of enclosing the variable in the "correct"
    >> number of quotes in order for the variable to be correctly resolved;
    >> however, I have used a couple of different approaches but neither of
    >> them is producing the desired result, with SQL errors being returned
    > > in both cases.
    >>
    >> Below I have attempted to show the variations of code which I have used
    >> in an attempt to solve the problem:
    >>
    >> Method 1. (Build the variable within the SQL)
    >> PGM
    >> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >> <>
    >> LOOP: DCLF FILE(filea)
    >> <>
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >> (NAME &NAME) (Q &Q))
    >> /* note that variables &AGENT and &NAME are fields on the input file */
    >> GOTO CMDLBL(LOOP)
    >> ENDPGM
    >>
    >> The value of the variable &NAME (len 30) at the time of execution of the
    >> QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is 'ABCD12345'
    >>
    >> The QMQRY qmqryname is as follows:
    >> INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)
    >>
    >> Referring to the job log, the resolved statement is:
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))
    >>
    >> with the resulting error message indicated:
    >> Token SMITH was not valid. Valid tokens : ),.
    >> RUN QUERY command failed with SAQLCODE -104
    >>
    >> Method 2. (Build the variable in the CL program)
    >> PGM
    >> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >> DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    >> <>
    >> LOOP: DCLF FILE(filea)
    >> <>
    >> CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >> (NAME &NAME))
    >> GOTO CMDLBL(LOOP)
    >> ENDPGM
    >>
    >> with the QMQRY changed accordingly:
    >> INSERT INTO fileb VALUES(&AGENT, &NAME)
    >>
    >> Referring to the job log, the resolved statement is:
    >> STRQMQRY QMQRY(qmqryname)
    >> SETVAR((AGENT 'ABCD12345') (NAME '''JOHN SMITH'''))
    >>
    >> with the resulting error message indicated:
    >> Column ABCD12345 not in specified tables.
    >> RUN QUERY command failed with SAQLCODE -206
    >>
    >> What am I doing wrong and what do I need to do to resolve it?
    >> Can anyone please assist ?
    >>
    >> Thanks in advance.
    >> Steve

    >
    > Can you include your QMQRY source??


  4. Re: Problem with variables in STRQMQRY - Help !

    Thanks for the feedback.

    However, using Method 1, the error returned relates to the second variable &NAME and not the first &AGENT (which does not have the additional apostrophes); the problem appears to be with the field which has EMBEDDED SPACES (even though the extra apostrophes have been included).

    It is for this reason that I didn't include the extra apostrophes on the &AGENT field in Method 2.

    Note that using Method 2, the error then relates to the first variable &AGENT but states :

    Column variablevalue not in specified tables,

    whereas as the error for method 1 relates to the second variable &NAME :

    Token variablevalue was not valid. Valid tokens : ),.

    All responses much appreciated.

    Thanks,
    Steve
    "CRPence" wrote in message news:47275878$1@kcnews01...
    > Actually the QMQRY sources were included. Since I do not have the
    > original post, I am responding to this reply.
    >
    > In both methods it seems the same error is made. That is, both fail
    > to treat the first variable as a string.
    >
    > In the "method 1" the QMQRY source has INSERT INTO fileb
    > VALUES(&AGENT, &Q&NAME&Q), but it requires instead to have INSERT INTO
    > fileb VALUES(&Q&AGENT&Q, &Q&NAME&Q)
    >
    > In the "method 2" the CLP gives additional apostrophes to a variable
    > &NAME1, but not to a similarly extended variable &AGENT1.
    >
    > Regards, Chuck
    > --
    > All comments provided "as is" with no warranties of any kind
    > whatsoever and may not represent positions, strategies, nor views of my
    > employer
    >
    > jacko wrote:
    >> On Oct 30, 8:27 am, "Wingnut" wrote:
    >>> I am having considerable difficulty attempting to execute a QMQRY from
    >> > a CL program in which I am passing to the QMQRY a variable containing
    >>> embedded spaces.
    >>>
    >>> I am aware of the importance of enclosing the variable in the "correct"
    >>> number of quotes in order for the variable to be correctly resolved;
    >>> however, I have used a couple of different approaches but neither of
    >>> them is producing the desired result, with SQL errors being returned
    >> > in both cases.
    >>>
    >>> Below I have attempted to show the variations of code which I have used
    >>> in an attempt to solve the problem:
    >>>
    >>> Method 1. (Build the variable within the SQL)
    >>> PGM
    >>> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >>> <>
    >>> LOOP: DCLF FILE(filea)
    >>> <>
    >>> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >>> (NAME &NAME) (Q &Q))
    >>> /* note that variables &AGENT and &NAME are fields on the input file */
    >>> GOTO CMDLBL(LOOP)
    >>> ENDPGM
    >>>
    >>> The value of the variable &NAME (len 30) at the time of execution of the
    >>> QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is 'ABCD12345'
    >>>
    >>> The QMQRY qmqryname is as follows:
    >>> INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)
    >>>
    >>> Referring to the job log, the resolved statement is:
    >>> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))
    >>>
    >>> with the resulting error message indicated:
    >>> Token SMITH was not valid. Valid tokens : ),.
    >>> RUN QUERY command failed with SAQLCODE -104
    >>>
    >>> Method 2. (Build the variable in the CL program)
    >>> PGM
    >>> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >>> DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    >>> <>
    >>> LOOP: DCLF FILE(filea)
    >>> <>
    >>> CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)
    >>> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >>> (NAME &NAME))
    >>> GOTO CMDLBL(LOOP)
    >>> ENDPGM
    >>>
    >>> with the QMQRY changed accordingly:
    >>> INSERT INTO fileb VALUES(&AGENT, &NAME)
    >>>
    >>> Referring to the job log, the resolved statement is:
    >>> STRQMQRY QMQRY(qmqryname)
    >>> SETVAR((AGENT 'ABCD12345') (NAME '''JOHN SMITH'''))
    >>>
    >>> with the resulting error message indicated:
    >>> Column ABCD12345 not in specified tables.
    >>> RUN QUERY command failed with SAQLCODE -206
    >>>
    >>> What am I doing wrong and what do I need to do to resolve it?
    >>> Can anyone please assist ?
    >>>
    >>> Thanks in advance.
    >>> Steve

    >>
    >> Can you include your QMQRY source??


  5. Re: Problem with variables in STRQMQRY - Help !


  6. Re: Problem with variables in STRQMQRY - Help !

    On Oct 30, 5:27 am, "Wingnut" wrote:
    > I am having considerable difficulty attempting to execute a QMQRY from a CL program in which I am passing to the QMQRY a variable containing embedded spaces.
    >
    > I am aware of the importance of enclosing the variable in the "correct" number of quotes in order for the variable to be correctly resolved; however, I have used a couple of different approaches but neither of them is producing the desired result, with SQL errors being returned in both cases.
    >
    > Below I have attempted to show the variations of code which I have used in an attempt to solve the problem:
    >
    > Method 1. (Build the variable within the SQL)
    > PGM
    > .
    > DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    > .
    > LOOP: DCLF FILE(filea)
    >
    > .
    >
    > .
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >
    > (NAME &NAME) (Q &Q)) [note that the variables &AGENT and &NAME are fielkds on the input file]
    >
    > GOTO CMDLBL(LOOP)
    >
    > ENDPGM
    >
    > The value of the variable &NAME (len 30) at the time of execution of the QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is 'ABCD12345'
    >
    > The QMQRY qmqryname is as follows:
    >
    > INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)
    >
    > Referring to the job log, the resolved statement is:
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))
    >
    > with the resulting error message indicated:
    >
    > Token SMITH was not valid. Valid tokens : ),.
    > RUN QUERY command failed with SAQLCODE -104
    >
    > Method 2. (Build the variable in the CL program)
    > PGM
    > .
    > DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    > DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    > .
    > LOOP: DCLF FILE(filea)
    >
    > .
    >
    > .
    >
    > CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >
    > (NAME &NAME))
    >
    > GOTO CMDLBL(LOOP)
    >
    > ENDPGM
    >
    > with the QMQRY changed accordingly:
    >
    > INSERT INTO fileb VALUES(&AGENT, &NAME)
    >
    > Referring to the job log, the resolved statement is:
    >
    > STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME '''JOHN SMITH'''))
    >
    > with the resulting error message indicated:
    >
    > Column ABCD12345 not in specified tables.
    > RUN QUERY command failed with SAQLCODE -206
    >
    > What am I doing wrong and what do I need to do to resolve it ? Can anyone please assist ?
    >
    > Thanks in advance.


    QM query:
    insert into mylib.testqm values(&AGENT, &AGENTNAME)


    CL pgm:
    pgm
    dcl var(&agent) type(*char) len(10)
    value(abcd1234)
    dcl var(&agentname) type(*char) len(20) value('Jonathan
    Ball')
    dcl var(&p_agent) type(*char)
    len(12)
    dcl var(&p_name) type(*char)
    len(22)
    chgvar var(&p_agent) value('''' || &agent |<
    '''')
    chgvar var(&p_name) value('''' || &agentname |<
    '''')
    strqmqry mylib/testinsert setvar((agent &p_agent) (agentname &p_name))
    +

    naming(*sql)
    return
    endpgm



  7. Re: Problem with variables in STRQMQRY - Help !

    Thank you all for your input. I managed to sort it out with your assistance.

    Much appreciated.

    Steve
    "Jonathan Ball" wrote in message
    news:1193797119.867534.312060@e9g2000prf.googlegro ups.com...
    > On Oct 30, 5:27 am, "Wingnut" wrote:
    >> I am having considerable difficulty attempting to execute a QMQRY from a
    >> CL program in which I am passing to the QMQRY a variable containing
    >> embedded spaces.
    >>
    >> I am aware of the importance of enclosing the variable in the "correct"
    >> number of quotes in order for the variable to be correctly resolved;
    >> however, I have used a couple of different approaches but neither of them
    >> is producing the desired result, with SQL errors being returned in both
    >> cases.
    >>
    >> Below I have attempted to show the variations of code which I have used
    >> in an attempt to solve the problem:
    >>
    >> Method 1. (Build the variable within the SQL)
    >> PGM
    >> .
    >> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >> .
    >> LOOP: DCLF FILE(filea)
    >>
    >> .
    >>
    >> .
    >>
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >>
    >> (NAME &NAME) (Q &Q)) [note that the variables
    >> &AGENT and &NAME are fielkds on the input file]
    >>
    >> GOTO CMDLBL(LOOP)
    >>
    >> ENDPGM
    >>
    >> The value of the variable &NAME (len 30) at the time of execution of the
    >> QMQRY is 'JOHN SMITH' while that of variable &AGENT (len 9) is
    >> 'ABCD12345'
    >>
    >> The QMQRY qmqryname is as follows:
    >>
    >> INSERT INTO fileb VALUES(&AGENT, &Q&NAME&Q)
    >>
    >> Referring to the job log, the resolved statement is:
    >>
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME 'JOHN SMITH'))
    >>
    >> with the resulting error message indicated:
    >>
    >> Token SMITH was not valid. Valid tokens : ),.
    >> RUN QUERY command failed with SAQLCODE -104
    >>
    >> Method 2. (Build the variable in the CL program)
    >> PGM
    >> .
    >> DCL VAR(&Q) TYPE(*CHAR) LEN(1) VALUE('''')
    >> DCL VAR(&NAME1) TYPE(*CHAR) LEN(32)
    >> .
    >> LOOP: DCLF FILE(filea)
    >>
    >> .
    >>
    >> .
    >>
    >> CHGVAR VAR(&NAME1) VALUE(&Q *CAT &NAME *TCAT &Q)
    >>
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT &AGENT) +
    >>
    >> (NAME &NAME))
    >>
    >> GOTO CMDLBL(LOOP)
    >>
    >> ENDPGM
    >>
    >> with the QMQRY changed accordingly:
    >>
    >> INSERT INTO fileb VALUES(&AGENT, &NAME)
    >>
    >> Referring to the job log, the resolved statement is:
    >>
    >> STRQMQRY QMQRY(qmqryname) SETVAR((AGENT 'ABCD12345') (NAME '''JOHN
    >> SMITH'''))
    >>
    >> with the resulting error message indicated:
    >>
    >> Column ABCD12345 not in specified tables.
    >> RUN QUERY command failed with SAQLCODE -206
    >>
    >> What am I doing wrong and what do I need to do to resolve it ? Can anyone
    >> please assist ?
    >>
    >> Thanks in advance.

    >
    > QM query:
    > insert into mylib.testqm values(&AGENT, &AGENTNAME)
    >
    >
    > CL pgm:
    > pgm
    > dcl var(&agent) type(*char) len(10)
    > value(abcd1234)
    > dcl var(&agentname) type(*char) len(20) value('Jonathan
    > Ball')
    > dcl var(&p_agent) type(*char)
    > len(12)
    > dcl var(&p_name) type(*char)
    > len(22)
    > chgvar var(&p_agent) value('''' || &agent |<
    > '''')
    > chgvar var(&p_name) value('''' || &agentname |<
    > '''')
    > strqmqry mylib/testinsert setvar((agent &p_agent) (agentname &p_name))
    > +
    >
    > naming(*sql)
    > return
    > endpgm
    >
    >




+ Reply to Thread