Query Variables in CL

pts.
Tags:
AS/400
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!
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Not enough to go on.

Is the variable you are comparing alpha or numeric?
If it is alpha whay are you entering the search data in a numeric field?
Is the 9000 at the beginning of the variable or can it be anywhere within it?
What are you using to process the query? QM query? An SQL procedure? Passing it to an SQLRPGE program? OPNQRYF?

A listing of the CL program would be helpful

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • SheldonLinker
    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)||'%'
    30 pointsBadges:
    report
  • ChuckM
    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 |
    0 pointsBadges:
    report
  • ChuckM
    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 |
    0 pointsBadges:
    report
  • astradyne
    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
    370 pointsBadges:
    report
  • JPLamontre
    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 &quote *char 1 value('''') dcl &param ... dcl &paramquoted ... len is len of &param + 2 chgvar &paramquoted (&quote *cat &param *cat &quote) strqmqry ... setvar ((P1 &paramquoted)) 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
    0 pointsBadges:
    report
  • Kumari
    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.
    0 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, to add quotes, use a QM query like:
    select col1,col2 from myfile where col1 like &Q&PARM&Q
    Then in the CL:
    STRQMQRY QMQRY(myqry) SETVAR(  ( Q '''' ) (PARM &CLvar)  )
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: