Re: Issue using QMQRY selecting data using a variable

I posted an example of a complete module that uses variables and STRQMQRY.

http://code.midrange.com/294dfd26fa.html

On Wed, Sep 10, 2014 at 7:34 AM, Gary Thompson <gthompson@xxxxxxxxxxx>
wrote:

For QmQry we typically use two single quote characters like so:
DCL VAR(&QT) TYPE(*CHAR) LEN(1) VALUE('''')
CHGVAR VAR(&NAMEPARM) VALUE(&QT || &NAME || &QT)

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Jeff Young
Sent: Wednesday, September 10, 2014 7:16 AM
To: Midrange Systems Technical Discussion
Subject: Re: Issue using QMQRY selecting data using a variable

Steve/Vern,
Tried the double quotes:
STRQMQRY QMQRY(TEST) SETVAR((ITEM "10010")) Column "10010" not in
specified tables.
RUN QUERY command failed with SQLCODE -206.
RUN QUERY command ended due to error.
STRQMQRY command failed.

Any suggestions as to what I am missing here?

Jeff Young
Sr. Programmer Analyst

On Tue, Sep 9, 2014 at 10:48 PM, Vernon Hamberg <

vhamberg@xxxxxxxxxxxxxxx>

wrote:

That should work - when using STRQMQRY without any SETVAR, Jeff (OP?)
entered it with quotes - the system doubled up those quotes, so this
would be the result.

There is an easier way, IMO, that I learned years ago in these lists -
it gets rid of the need for all those funky quadruple quotes.

Remember that all SETVAR values have to be alpha - you are building a
character string, right? Avoid quotes problems by making it so that
all you put in are the alpha values, without quotes - ba-da-bing! For
numeric values, don't have &QT around the substitution variable. For
alpha values, wrap the sub variable with &QTs.

Make the statement in QMQRY to be like this -

SELECT IFCOMP, IFITEM FROM VINITMB WHERE IFITEM = &QT&ITEM&QT

Then you'd have STRQMQRY TEST SETVAR((ITEM '1010') (QT ''''))

This makes things so much easier in CL, too - much less need to handle
quotes (unless they are embedded in the value) - so the above STRQMQRY
could be like this, after a couple DCLs

DCL &ITEM *CHAR 4
DCL &QT *CHAR 1 VALUE('''')
STRQMQRY TEST SETVAR((ITEM &ITEM) (QT &QT))

Remember, the ampersands here are for CL variables, NOT the QM
substitution variables.

HTH
Vern


On 9/9/2014 9:33 PM, Steve Landess wrote:

Jeff -

Try running it this way:

STRQMQRY TEST SETVAR((ITEM '''1010'''))

-sjl


"Jeff Young" wrote in message news:mailman.3739.1410314271.
8590.midrange-l@xxxxxxxxxxxx...

The. & character is not allowed in the variable name, and when I use
the SETVAR((ITEM '101.')) syntax, I get the error indicating a data
exception trying to cast field IFITEM as integer. This is a character

field. .

On Sep 9, 2014 9:36 PM, "midrange" <franz9000@xxxxxxxxx> wrote:

Why this ? 'ITEM' your cl variable is &ITEM (with no quotes)

(not at a system to test this..) but ... setvar((&ITEM '1010))
Jim


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Alan Campin
Sent: Tuesday, September 09, 2014 7:34 PM
To: Midrange Systems Technical Discussion
Subject: Re: Issue using QMQRY selecting data using a variable

I assume you are running in cl? If yes, must be triple quoted.
'''1010'''.
On Sep 9, 2014 5:11 PM, "Jeff Young" <jyoung0950@xxxxxxxxx> wrote:

All,
I have a QMQRY in SQL format with the following statement:
SELECT IFCOMP, IFITEM FROM VINITMB WHERE IFITEM = &ITEM

Field IFITEM is defined as Char 20 and can contain numbers,
characters or a mix of both.

When I run this using STRQMQRY *without* specifying any values for
the SETVAR keyword, it prompts for the value of *"ITEM"*. i enter

*'1010'

*and get a report of all records with that value.

When I run it using the STRQMQRY *with* specifying the SETVAR
keyword as
follows:
STRQMQRY TEST SETVAR(('ITEM' ' '1010')) the query ends abnormally
with a message in the joblog indicating a selection error on field

IFITEM.

The prior messages indicate that it is attempting to CAST the
IFITEM field as INTEGER.

What am I missing here?
Is it not possible to pass a character value as a variable to a

QMQRY?

TIA,

Jeff Young
Sr. Programmer Analyst
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at

http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.