Could someone please explain how I can use CL to get a variable into a Query. So that I can use the query parameter LIKE and have the ability to use the %.
Basically what I'm trying to do is create a screen, on that screen the user will enter a number for example 9000, I want the query that runs behind it to return results of 9000% to the screen, but I just can't get it to work. It's got to be really simple hasn't it?
This is all I want
Select A B C D from lib/file where A is like "Variable" order by A B C D
But I don't know how to pass a variable into a query. I tried SQL but when I built the query in that it came back with '9000%' so didn't like it.
Please note this is numeric data they are inputting and not alpha whcih may cause further problems.
Could someone please help it's driving me mad!
First, regaring LIKE and numeric data: It won't work. Perhaps if what you have is a number, then what you want is
VARCHAR(field) LIKE VARCHAR(argument)||'%'
Second, there are certain restrictions when coding SQL from CL. I find it easier to write a C/SQL (any other language/SQL) command, and call it from CL. Inside such a program, the line above will have an added variable marker:
VARCHAR(field) LIKE VARCHAR(:argument)||'%'
SamanthaD, In general, when you want to pass a variable into a query statement, you make the entire statement a text string and concatenate the value of the variable into it.
Here's a little example that may get you going:
PGM DCL VAR(&PART1) TYPE(*CHAR) LEN(80) +
VALUE('Select A B C D from lib/file where A IS LIKE "')
DCL VAR(&VARIABLE) TYPE(*CHAR) LEN(10) VALUE('9000')
DCL VAR(&PART2) TYPE(*CHAR) LEN(80) VALUE('%" Order BY A B C D')
DCL VAR(&QRYSTMT) TYPE(*CHAR) LEN(200)
CHGVAR VAR(&QRYSTMT) VALUE(&PART1 |
Sorry...finger check.
As I was trying to say, you have to create a text string that contains the query statment with the contents of your variable concatenated within. See if this little example doesn't get you going:
PGM
DCL VAR(&PART1) TYPE(*CHAR) LEN(80) +
VALUE('Select A B C D from lib/file where A IS LIKE "')
DCL VAR(&VARIABLE) TYPE(*CHAR) LEN(10) VALUE('9000')
DCL VAR(&PART2) TYPE(*CHAR) LEN(80) VALUE('%" Order BY A B C D')
DCL VAR(&QRYSTMT) TYPE(*CHAR) LEN(200)
CHGVAR VAR(&QRYSTMT) VALUE(&PART1 |
Hi Samantha
In the past when I've wanted to do this I've gone down the route of creating a Query/400 query and then using the RTVQMQRY route to convert it so that it can use parameters.
There are lots of articles on this process, just Google RTVQMQRY or visit https://search400.techtarget.com/tip/1,289483,sid3_gci530291,00.html for an exmple of how to use it.
All the best
Jonathan
some notes to have in mind :
-1- a variable transmitted to a qmqry is up to 55 chars.
-2- a variable in a query can not be in a litteral (between quotes)
wrong : select ... like '&P1'...
correct : select ... like &P1...
this means you have to add quotes yourself in the cl :
dcl "e *char 1 value('''')
dcl ¶m ...
dcl ¶mquoted ... len is len of ¶m + 2
chgvar ¶mquoted ("e *cat ¶m *cat "e)
strqmqry ... setvar ((P1 ¶mquoted))
if qmqry has too much limitations for your job, you can easily replace it by executesql or execsql, two tools at http://jplamontre.free.fr/jpltools
I think this will solve your problem -
OPNQRYF FILE((FILENAME))QRYSLT(FIELDNAME *EQ %WLDCRD(9000*)
Please let me know, this solved your problem or how else did you solve it.
From what you asked, it's a CHAR column that contains '9000%' as a possible value. When 9000 is input, you want to retrieve the '9000%' value. If that's true, there shouldn't be any problem.
The numeric 9000 is going to be a character literal when it gets into the QM query statement. You just need to ensure that quotes get placed in the proper positions and that the "%" wild-card also gets included.
Tom
The &Q query substitution variable receives a single quote as its value. You can re-use it wherever needed in the query statement.
No need to add the quotes in CL. Let QM query do it for you. Just tell it where.
Tom
Hmmm... this "answer" editor doesn't like four single-quotes in a row. It changed them into two double-quotes. Well, if what I said made sense, you'll figure it out.
Tom
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 9  Replies