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

il 06/09/2007 11.08, Scrive Wingnut 40647816:[color=blue]
> 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"[/color]
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ñejoAlcoolInside
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'

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" <do.not.spam.me.please@nettuno.it> wrote in message
news:46dfceea$0$16032$5fc30a8@news.tiscali.it...[color=blue]
> il 06/09/2007 11.08, Scrive Wingnut 40647816:[color=green]
>> 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"[/color]
> 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ñejoAlcoolInside
> 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'[/color]

il 06/09/2007 12.25, Scrive Wingnut 40647816:[color=blue]
> 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[/color]
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.
[color=blue]
>
> "Dr.UgoGagliardelli" <do.not.spam.me.please@nettuno.it> wrote in message
> news:46dfceea$0$16032$5fc30a8@news.tiscali.it...[color=green]
>> il 06/09/2007 11.08, Scrive Wingnut 40647816:[color=darkred]
>>> 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"[/color]
>> 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ñejoAlcoolInside
>> 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'[/color]
>
>[/color]


--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñejoAlcoolInside
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'

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
[url]http://archive.midrange.com/midrange-l/200508/msg00958.html[/url] 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:[color=blue]
> il 06/09/2007 11.08, Scrive Wingnut 40647816:[color=green]
>> 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"[/color]
> 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
>[/color]

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" <do.not.spam.me.please@nettuno.it> wrote in message
news:46dfec60$0$16040$5fc30a8@news.tiscali.it...[color=blue]
> il 06/09/2007 12.25, Scrive Wingnut 40647816:[color=green]
>> 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[/color]
> 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.
>[color=green]
>>
>> "Dr.UgoGagliardelli" <do.not.spam.me.please@nettuno.it> wrote in message
>> news:46dfceea$0$16032$5fc30a8@news.tiscali.it...[color=darkred]
>>> 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ñejoAlcoolInside
>>> 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'[/color]
>>
>>[/color]
>
>
> --
> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñejoAlcoolInside
> 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'[/color]