STRQMQRY Substitution Variables - HELP Please ! - IBM AS400

This is a discussion on STRQMQRY Substitution Variables - HELP Please ! - IBM AS400 ; Can anyone PLEASE assist me with an issue I am having, attempting to run a QMQRY and passing to it a substitution variable whose value is held in a CL variable. Using code similar to that below: PGM .. .. ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: STRQMQRY Substitution Variables - HELP Please !

  1. STRQMQRY Substitution Variables - HELP Please !

    Can anyone PLEASE assist me with an issue I am having, attempting to run a QMQRY and passing to it a substitution variable whose value is held in a CL variable.

    Using code similar to that below:
    PGM
    ..
    ..
    ..
    DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)
    ..
    ..
    ..
    STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)

    The Query itself is inserting a record using, among other values, that of the substitution variable GLACCT as follows:

    INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    At the time of execution, the value of the variable &ACCT is '096EOPB'

    The STRQMQRY command is failing, however, with the message

    "Numeric constant 096EOPB not valid."
    "RUN QUERY command failed with SQLCODE -103"

    Why am I getting this message ? and what can I do to overcome the problem so I can pass the substitution variable to the Query ?

    Any assistance would be GREATLY APPRECIATED.

    Thanks,
    Steve

  2. Re: STRQMQRY Substitution Variables - HELP Please !

    il 06/09/2007 11.08, Scrive Wingnut 40647816:
    > Can anyone PLEASE assist me with an issue I am having, attempting to run
    > a QMQRY and passing to it a substitution variable whose value is held in
    > a CL variable.
    >
    > Using code similar to that below:
    > *PGM*
    > *.*
    > *.*
    > *.*
    > *DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)*
    > *.*
    > *.*
    > *.*
    > *STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)*
    >
    > The Query itself is inserting a record using, among other values, that
    > of the substitution variable GLACCT as follows:
    >
    > INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    > At the time of execution, the value of the variable &ACCT is *'096EOPB'*
    >
    > The STRQMQRY command is failing, however, with the message
    >
    > "Numeric constant 096EOPB not valid."
    > "RUN QUERY command failed with SQLCODE -103"

    I guess missing quotes around the variable inside the sql statement.
    Without quotes a value will be accepted as a numeric value, while
    '096EOPB' is not

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  3. Re: STRQMQRY Substitution Variables - HELP Please !

    Thanks for the reply.

    I would have thought, however, that if I put quotes around the variable
    within the query, (ie. '&GLACCT'), the value inserted would be the string
    '&GLACCT', rather than the contents of the substitution variable &GLACCT,
    wouldn't it ? (Bearing in mind that the substitution variable &GLACCT has
    the value passed to it via the CL variable &ACCT)

    Appreciate your response though.
    Cheers

    "Dr.UgoGagliardelli" wrote in message
    news:46dfceea$0$16032$5fc30a8@news.tiscali.it...
    > il 06/09/2007 11.08, Scrive Wingnut 40647816:
    >> Can anyone PLEASE assist me with an issue I am having, attempting to run
    >> a QMQRY and passing to it a substitution variable whose value is held in
    >> a CL variable.
    >> Using code similar to that below:
    >> *PGM*
    >> *.*
    >> *.*
    >> *.*
    >> *DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)*
    >> *.*
    >> *.*
    >> *.*
    >> *STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)*
    >> The Query itself is inserting a record using, among other values, that
    >> of the substitution variable GLACCT as follows:
    >> INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    >> At the time of execution, the value of the variable &ACCT is *'096EOPB'*
    >> The STRQMQRY command is failing, however, with the message
    >> "Numeric constant 096EOPB not valid."
    >> "RUN QUERY command failed with SQLCODE -103"

    > I guess missing quotes around the variable inside the sql statement.
    > Without quotes a value will be accepted as a numeric value, while
    > '096EOPB' is not
    >
    > --
    > Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    > Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    > Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    > schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'




  4. Re: STRQMQRY Substitution Variables - HELP Please !

    il 06/09/2007 12.25, Scrive Wingnut 40647816:
    > Thanks for the reply.
    >
    > I would have thought, however, that if I put quotes around the variable
    > within the query, (ie. '&GLACCT'), the value inserted would be the string
    > '&GLACCT', rather than the contents of the substitution variable &GLACCT,
    > wouldn't it ? (Bearing in mind that the substitution variable &GLACCT has
    > the value passed to it via the CL variable &ACCT)
    >
    > Appreciate your response though.
    > Cheers

    Of course, I wasn't careful enough, you said that "the value of the
    variable &ACCT is *'096EOPB'*"
    Because of you declared &ACCT as 7 chars it means that the variable
    cannot contain single quotes, as 096EOPB is seven chars, so you need to
    put quotes inside the variable, then you need to double them accordingly
    to cl sintax, ad increasing the var length e.g.
    DCL VAR(&ACCT) TYPE(*CHAR) LEN(9) VALUE('''096EOPB''')
    This way it should run.

    >
    > "Dr.UgoGagliardelli" wrote in message
    > news:46dfceea$0$16032$5fc30a8@news.tiscali.it...
    >> il 06/09/2007 11.08, Scrive Wingnut 40647816:
    >>> Can anyone PLEASE assist me with an issue I am having, attempting to run
    >>> a QMQRY and passing to it a substitution variable whose value is held in
    >>> a CL variable.
    >>> Using code similar to that below:
    >>> *PGM*
    >>> *.*
    >>> *.*
    >>> *.*
    >>> *DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)*
    >>> *.*
    >>> *.*
    >>> *.*
    >>> *STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)*
    >>> The Query itself is inserting a record using, among other values, that
    >>> of the substitution variable GLACCT as follows:
    >>> INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    >>> At the time of execution, the value of the variable &ACCT is *'096EOPB'*
    >>> The STRQMQRY command is failing, however, with the message
    >>> "Numeric constant 096EOPB not valid."
    >>> "RUN QUERY command failed with SQLCODE -103"

    >> I guess missing quotes around the variable inside the sql statement.
    >> Without quotes a value will be accepted as a numeric value, while
    >> '096EOPB' is not
    >>
    >> --
    >> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    >> Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    >> Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    >> schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

    >
    >



    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  5. Re: STRQMQRY Substitution Variables - HELP Please !

    Note: AFaIK alt.comp.sys.as400 is effectively defunct; merely a spam
    repository.
    A search of archive.midrange.com against MIDRANGE-L, using the search
    tokens / strqmqry quote apostrophe / will give a hit for message
    http://archive.midrange.com/midrange.../msg00958.html which gives
    an example of character value INSERT, avoiding escaping of apostrophe on
    data passed in variables using the SETVAR() parameter. FWiW removing
    the token / apostrophe / will probably produce many more postings with
    other examples; oddly most people incorrectly quote the word quote ;-)

    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

    Dr.UgoGagliardelli wrote:
    > il 06/09/2007 11.08, Scrive Wingnut 40647816:
    >> Can anyone PLEASE assist me with an issue I am having, attempting to
    >> run a QMQRY and passing to it a substitution variable whose value is
    >> held in a CL variable.
    >>
    >> Using code similar to that below:
    >> *PGM*
    >> *.*
    >> *.*
    >> *.*
    >> *DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)*
    >> *.*
    >> *.*
    >> *.*
    >> *STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)*
    >>
    >> The Query itself is inserting a record using, among other values, that
    >> of the substitution variable GLACCT as follows:
    >>
    >> INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    >> At the time of execution, the value of the variable &ACCT is *'096EOPB'*
    >>
    >> The STRQMQRY command is failing, however, with the message
    >>
    >> "Numeric constant 096EOPB not valid."
    >> "RUN QUERY command failed with SQLCODE -103"

    > I guess missing quotes around the variable inside the sql statement.
    > Without quotes a value will be accepted as a numeric value, while
    > '096EOPB' is not
    >


  6. Re: STRQMQRY Substitution Variables - HELP Please !

    Thank you so much for your help.

    After a bit of playing around, defining variables with extra 'quotes', I
    managed to get it all happening as it should.

    It was also having trouble when substition variables had a value wioth
    leading or embedded blanks.
    It now works as well as a "bought" one !

    Thanks again.
    Steve


    "Dr.UgoGagliardelli" wrote in message
    news:46dfec60$0$16040$5fc30a8@news.tiscali.it...
    > il 06/09/2007 12.25, Scrive Wingnut 40647816:
    >> Thanks for the reply.
    >>
    >> I would have thought, however, that if I put quotes around the variable
    >> within the query, (ie. '&GLACCT'), the value inserted would be the string
    >> '&GLACCT', rather than the contents of the substitution variable &GLACCT,
    >> wouldn't it ? (Bearing in mind that the substitution variable &GLACCT has
    >> the value passed to it via the CL variable &ACCT)
    >>
    >> Appreciate your response though.
    >> Cheers

    > Of course, I wasn't careful enough, you said that "the value of the
    > variable &ACCT is *'096EOPB'*"
    > Because of you declared &ACCT as 7 chars it means that the variable cannot
    > contain single quotes, as 096EOPB is seven chars, so you need to put
    > quotes inside the variable, then you need to double them accordingly to cl
    > sintax, ad increasing the var length e.g.
    > DCL VAR(&ACCT) TYPE(*CHAR) LEN(9) VALUE('''096EOPB''')
    > This way it should run.
    >
    >>
    >> "Dr.UgoGagliardelli" wrote in message
    >> news:46dfceea$0$16032$5fc30a8@news.tiscali.it...
    >>> il 06/09/2007 11.08, Scrive Wingnut 40647816:
    >>>> Can anyone PLEASE assist me with an issue I am having, attempting to
    >>>> run a QMQRY and passing to it a substitution variable whose value is
    >>>> held in a CL variable.
    >>>> Using code similar to that below:
    >>>> *PGM*
    >>>> *.*
    >>>> *.*
    >>>> *.*
    >>>> *DCL VAR(&ACCT) TYPE(*CHAR) LEN(7)*
    >>>> *.*
    >>>> *.*
    >>>> *.*
    >>>> *STRQMQRY QMQRY(queryname) SETVAR((GLACCT &ACCT),,,,,etc,,,,,,,)*
    >>>> The Query itself is inserting a record using, among other values, that
    >>>> of the substitution variable GLACCT as follows:
    >>>> INSERT INTO filename ........VALUES(&GLACCT, ..............etc.)
    >>>> At the time of execution, the value of the variable &ACCT is
    >>>> *'096EOPB'*
    >>>> The STRQMQRY command is failing, however, with the message
    >>>> "Numeric constant 096EOPB not valid."
    >>>> "RUN QUERY command failed with SQLCODE -103"
    >>> I guess missing quotes around the variable inside the sql statement.
    >>> Without quotes a value will be accepted as a numeric value, while
    >>> '096EOPB' is not
    >>>
    >>> --
    >>> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    >>> Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    >>> Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    >>> schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

    >>
    >>

    >
    >
    > --
    > Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    > Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    > Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    > schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'




+ Reply to Thread