Unconfigured Ad Widget

**ukpi1b** · 07-22-2004, 08:10 AM

STRQMQRY pass variable for substring via CL program

You may check ukpi1b "Using variable parms in STRQMQRY" 8/19/02 8:47am

**K.Forsythe** · 07-22-2004, 08:25 AM

STRQMQRY pass variable for substring via CL program

SETVAR((COND1 ''' *CAT &YEAR *TCAT ''') (COND2 ''' *CAT &YEAR *TCAT ''')) Does not yield 'yy' and 'mm' it yields ' *CAT &YEAR *TCAT ' and ' *CAT &YEAR *TCAT ' The first quote starts a character literal and the last quote ends it, every pair of quotes in between force a quote into the literal value. ''' is not a quote, '''' is a single quote as a literal. One to start, one to stop, two in the middle. I hope that makes it clear. Kevin

**Guest.Visitor** · 07-23-2004, 05:18 AM

STRQMQRY pass variable for substring via CL program

Kevin, I tried 4 quotes, but got CL compiler errors: 3800- STRQMQRY QMQRY(*LIBL/TIOGATRAD2) OUTPUT(*PRINT)+ 3900 QMFORM(*LIBL/TIOGATRAD2) + 4000 SETVAR((COND1 '''' *CAT &YY *TCAT '''') + 4100 (COND2 '''' *CAT &MM *TCAT ''''))[*] CPD0120 30 Syntax of expression in parameter SETVAR not valid.[*] CPD0120 30 Syntax of expression in parameter SETVAR not valid. I put all the quotes and cats into a variable like the example link posted by ukpi1b did and that got it to compile. But when I run it, SQL asks me to enter the variable. (after I posted the initial message yesterday, I changed variables from mm & yy to 6 digit start date and end date, format yymmdd) JOblog shows this: 4300 - STRQMQRY QMQRY(*LIBL/TIOGATRAD2) OUTPUT(*PRINT) QMFORM(*LIBL/TIOGATRAD2) SETVAR((COND1 '''040101''') (COND2 '''040101''')) Type a value for variable "cond1" and press Enter. ? 010104 Type a value for variable "cond2" and press Enter. ? 013104 SQL still does not like what I am doing. ---Dale

Code

**Guest.Visitor** · 07-23-2004, 06:36 AM

STRQMQRY pass variable for substring via CL program

dalejanus wrote: > 1500 - STRQMQRY QMQRY(SPC5LIB/TIOGASQL) OUTPUT(*PRINT) > QMFORM(*LIBL/TIOGATRADE) SETVAR((COND1 '01') (COND2 '04')) Dale, Here is a code fragment from some work here: ChgVar var(&cotext) value('''' *tcat &coname *tcat '''') StrQmQry qmqry(csr_status) qmform(*qmqry) + setvar((company &cotext)) Bill

**Guest.Visitor** · 07-23-2004, 10:33 AM

STRQMQRY pass variable for substring via CL program

Bill, I have used your example (and others) to get the 4 quotes into a CL variable and that compiles ok. But SQL still asks me to enter the variable data. Is there something I'm missing? SQL requires the passed parameter to be character. But my data is decimal. Does that matter? ---Dale

**M.Savino** · 07-24-2004, 10:28 AM

STRQMQRY pass variable for substring via CL program

Dale, This may or maynot help you solve your Parameter Passing problem. But hopefully it will simplify your code at some time when you've got to include Quotes within a Variable. Whenever I've got to do that I define a Variable that is a single quote. That makes the Concatenations a lot easier for me to deal with. Then I know that anytime I see a Quote in the code, it's being used to wrap a literal value. The code below will put a single quote before and after the word TESTING in variable &TWELVE. And it avoids the confusion about how the quotes are being used. You can also define the single quote as a constant in an RPG program which comes in handy for all the same reasons. Mike

Code

**Guest.Visitor** · 07-26-2004, 06:23 AM

STRQMQRY pass variable for substring via CL program

I have used &QUOTE on occasion in the past, but I keep forgetting about it. Your use of hex 7D makes it stand out more. I'll try it next time. I have lots of trouble with quotes in OPNQRYF and other places. Couldn't the designers of OS400 come up with something better, like S36's ?parm? or DOS Batch file's %parm%? Oh well, "What cannot be cured, must be endured". (note proper use of double quotes)(grin) I was able to get the data I wanted into the parameter, but now STRQMQRY gives me error QWM1913. Message . . . . : Type a value for variable "DATE1" and press Enter. Cause . . . . . : You want Query Management to use variable "DATE1", but that variable does not exist in the global variable pool. Recovery . . . : Type a value for "DATE1" and press Enter. The variable will be created with the value typed and will exist until the Query Management RUN QUERY command completes. The special value *BLANK can be typed if you want the value for variable "DATE1" to be a single blank. The special value *NONE can be typed if you want variable "DATE1" to have no value. If you press Enter without typing a value, or you press F3 (Exit) or F12 (Cancel), the RUN QUERY command will be ended.

Code

**Guest.Visitor** · 07-26-2004, 06:33 AM

STRQMQRY pass variable for substring via CL program

I just realized that in all my testing, I changed the paramater names several times, but did not reflect that change in my CL code. I can't try to pass COND1 if the SQL is expecting DATE1.

**buck.calabro@commsoft.net** · 07-26-2004, 11:53 AM

STRQMQRY pass variable for substring via CL program

> Couldn't the designers of OS400 come up > with something better, like S36's ?parm? > or DOS Batch file's %parm%? > Oh well, "What cannot be cured, > must be endured". There's nothing to be cured. There is no computer anywhere that doesn't have this situation. For instance, post the OCL needed to set ?parm? equal to a single question mark, or the DOS bat file statements that will set %parm% to the value of the percent sign. There's no way to do it except to designate some special 'escape' character. In RPG, for instance, the escape character is -- a single quote! To set a field to be equal to a single quote, you must do this: 1) Use a single quote to designate 'Here begins a literal' 2) Use a single quote to designate the literal value Because that literal is a single quote, it might be confused with 'this is the end of the literal' so we add another one. Two single quotes in a row mean 'this is a single quote' 3) Use a single quote to designate 'Here ends a literal' You'd have move '''' quote 1 This is essentially how escaping works in every language on every computer. --buck

**Guest.Visitor** · 08-02-2004, 04:21 AM

STRQMQRY pass variable for substring via CL program

I am trying new techniques using STRQM, Query manager. I am new to SQL, but I have used query manager successfully in the past for ad-hoc one-off reports. I have created a query that selects records based on a substring of the date. (Date is YYMMDD, qmqry requires I convert decimal to character first.) I converted the promted query to SQL. I want to pass month and year from a CL program which calls STRQMQRY. I am having difficulty with the decimal/character part. The query runs fine when prompted for the year and month, as long as I enter them with single quotes around them. year='04' month ='01' The problem is passing them from CL program , I get sql401 error. The job log says this: 1500 - STRQMQRY QMQRY(SPC5LIB/TIOGASQL) OUTPUT(*PRINT) QMFORM(*LIBL/TIOGATRADE) SETVAR((COND1 '01') (COND2 '04')) Comparison operator = operands not compatible. RUN QUERY command failed with SQLCODE -401. RUN QUERY command ended due to error. STRQMQRY command failed. QWM2701 received by TIOGATEST at 1500. (C D I R) If I change my cl code to include quotes, it passes syntax but does not return any data. Where is the middle ground? What is correct sytax to pass some digits as character so the substring will work? ---Dale

Code

**K.Forsythe** · 08-02-2004, 04:21 AM

STRQMQRY pass variable for substring via CL program

When using the prompt (F4) function within SEU for a CL program, the editor sometimes gets confused about what you are doing and puts quotes in strange places. Can you show us the current code? Take care, Kevin

Unconfigured Ad Widget

Announcement

STRQMQRY pass variable for substring via CL program

STRQMQRY pass variable for substring via CL program

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment