STRQMQRY error sqlcode = -104 QWM2701 - passing variable to query from CL program

[voicucosmin90]

Hi all,

I have an issue regaring a query who run in a CL, the query is started with a value passed from CL program:

My QMQRY:
SELECT * FROM mylib/myfile WHERE m_year concat m_month concat m_day > &var1

Here is the cl:
PGM PARM(&var1)
DCL (&var1) (*CHAR) LEN(8) --- like 20170519 as possible value, the format is YYYYMMDD
STRQMQRY QMQRY(My_query) OUTPUT(*) NAMING(*SYS) SETVAR((var1 &var1))

--> My problem is that I have an error - sqlcode=-104, it doesn't recognize the symbol & - I have in french the message : Element syntaxique & n'est pas correct. Elements possibles : ( + - ? : DAY..
message QWM2701

Please help me to understand what is wrong.
Before it works, now I have this problem but exactly this program with this query worked fine, I don't know what is change, if it related system values or not.

Thanks in advance

[CRinger400]

Not sure but I'll take a guess. Since you are using CONCAT, that date result is a string. Your YYYYMMDD parm is interpreted as a number in SQL since it's not surrounded by literal quotes.
So you have 2 choices:
1. Cast the CONCAT result as decimal(8,0) in the SQL. -or-
2. Define the parm as 10a and delimit it with single quotes '20170519'. CHGVAR VAR(&var1) VALUE(''' *CAT &var1 *CAT ''').

The query optimizer may tell you which option is better.

Ringer

[voicucosmin90]

Thanks CRinger400,

I tried but.. it doesn't work .. maybe I missed something
for option 1 I made this

SELECT * FROM mylib/myfile WHERE
cast(
digits(m_year) concat digits(m_month) concat digits(m_day)) > &var1
The result is the same, same error concerning "&"

For option 2 I made this In cl:

PGM PARM(&var1)
DCL (&var1) (*CHAR) LEN(8) --- like 20170519 as possible value, the format is YYYYMMDD
DCL (&var2) (*CHAR) LEN(10)

CHGVAR VAR(&var2) VALUE('''' *CAT &var1 *CAT ''''). --> I think it must be '''' and '''' - double quotes, else I don't have the appropriate value in the variable, but it doesn't work

STRQMQRY QMQRY(My_query) OUTPUT(*) NAMING(*SYS) SETVAR((var1 &var1))
....

Any idea please, how can I handle this?

[CRinger400]

Option 1: Digits creates a string. You want decimal(YourDateString, 8, 0).
Else Option 2: Yeah, I meant 4 single quotes on each side of the *CAT. I actually like to create a variable and use that in the code.
DCL VAR(&Qt ) TYPE(*CHAR) LEN(1) Value('''')

Ringer

[tomliotta]

My QMQRY:
SELECT * FROM mylib/myfile WHERE m_year concat m_month concat m_day > &var1

If that is an exact quote of your QM query, the first thing to change is your STRQMQRY command:

STRQMQRY QMQRY(My_query) OUTPUT(*) NAMING(*SYS) SETVAR(('var1' &var1))

Note that you specified lower-case "var1" inside your QM query, therefore the replacement name must be passed in as a lower-case value. By wrapping the replacement name in single-quotes, CL will keep the name as lower-case as it's made available to QM.

Alternatively, you can change the QM query to say "...concat m_day > &VAR1" using upper-case for the replacement name.

Like CRinger400, I use the same extra variable to handle single-quotes from CL and I name it "&q" in the query as well as in CL. The QM query then looks like this:

SELECT * FROM mylib/myfile WHERE m_year concat m_month concat m_day > &q&var1&q

And the STRQMQRY command prm becomes:

STRQMQRY QMQRY(My_query) OUTPUT(*) NAMING(*SYS) SETVAR(('var1' &var1) ('q' &q))

Replacement names can be used many times in a QM query string. You define it once in Cl, and the value can show up wherever replacement values are used.

The CL variable name technically will be handled as upper-case by CL no matter what you do. But any CL reference to the QM replacement name will need to be case-sensitive. If you don't quote the name, it will be auto-converted to upper-case.