RE: QMQRY question

[Joel Fritz <JFritz@xxxxxxxxxxxxxxxx>]

I went through something like this last week.  I thought I'd make a quick
and dirty utility(tee hee.)

I think the problem you're having is with quotes in CL.  Below is the CL
code that produced the statement I wanted to pass to the query.  The string
I was trying to pass was, in the query: vnd = &vendor AND item = &itemc AND
sty = &style. vnd and sty are character variables in the database, and itm
is a three position numeric.  &vendor, &itemc, and &style are all character
variables.  Here is the tortured CL snipped that constructs the phrase:

 CHGVAR &ITEMC &ITEM                                           
 CHGVAR &ITEMCODE ('VND = ' *CAT '''' *CAT &VENDOR *CAT '''')  
 CHGVAR     VAR(&ITEMCODE) VALUE(&ITEMCODE *TCAT ' and itm +   
              = ' *CAT &ITEMC)                                 
 CHGVAR     VAR(&ITEMCODE) VALUE(&ITEMCODE *TCAT ' and +       
              sty = ' *CAT '''' *CAT &STYLE *CAT '''')         

The trick for me was the quadruple apostrophe.  Note that &itemc is not
enclosed in quotes but &vendor and &style are.  The query string wants
character literals in quotes and numbers not in quotes.  You do have to pass
the numeric as a character, though. &itemcode is a 55 position character
variable.  I haven't tested it, but I believe you need to pass dates as a
character string with separators.  This is an example of something that
would be easier in a procedural language. I had a lot of fun working this
out. <g> 
  

> -----Original Message-----
> From: Dennis Munro [mailto:DMunro@badgerminingcorp.com]
> Sent: Monday, July 19, 1999 10:02 AM
> To: 'Midrange Users'
> Subject: QMQRY question
> 
> 
> I am converting some Query/400 queries to AS/400 Query 
> Management queries as
> a way to learn Query Management and I need some help.  
> Running on an S20
> with V4R2M0 and current on ptf's.
> 
> I am having trouble getting a STRQMQRY query/form with 
> variables to work
> within a CL program.  There is a sample in the DB2 for AS/400 Query
> Management Programming book using numeric & character variables but my
> variable is a date field defined as *ISO Type L in the DDS.  
> I have a "from"
> and "thru" date I am entering to pick out the date range of 
> transactions I
> want to print.
> 
> When I create the variables in the CL program, I get the error message
> "QWM2701 - STRQMQRY has failed".  I "D" the message and look at the
> variables & they look okay.
> 
> Seeing as how the field being compared to is an *ISO date field & the
> variables are defined as TYPE(*CHAR) LEN( 8), is that my 
> problem or does it
> have nothing to do with that.  I even defined the variables 
> as LEN(10) and
> that didn't help.  Starting to get desperate in trying to 
> understand my
> problem.
> 
> Running the query interactively & entering the variables as 19990601 &
> 19990630 gives me the error "Comparison operator >= operands not
> compatible".
> 
> Running the query interactively & entering the variables as 
> '19990601' &
> '19990630' gives me the error "Syntax of date, time, or 
> timestamp value not
> valid".
> 
> Running the query interactively & entering the variables as 
> '1999-06-01' &
> '1999-06-30' and it works just fine.  So I CHGVAR in the CL 
> program to get
> to this format and it still fails. The book talks about extra 
> quotes being
> needed but doesn't tell you why so I keep running the CL and 
> Dumping the
> variables when it fails but I still can't get it to work.<NAVBG>
> 
> FEU displays the data as '1999-06-03' or whatever date is in 
> the record.
> 
> I have two other queries similar to this except there the 
> date fields are
> defined as numeric and the variables are created like '19990601' &
> '19990630' and they work correctly.
> 
> I have spent several hours trying to figure this out & I am 
> no closer now
> than when I started, just more frustrated. Any idea as to 
> what I need to
> correct to get this to work?
> 
> TIA
> 
> Dilbert's Words Of Wisdom:
> "I love deadlines.  I especially like the whooshing sound 
> they make as they
> go flying by."
> 
> Dennis Munro  
> Badger Mining Corporation
> dmunro@badgerminingcorp.com <mailto:dmunro@badgerminingcorp.com> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to 
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: 
> david@midrange.com
> +---
> 
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---