Getting wildcard results from OPNQRYF

85 pts.
Tags:
AS/400 Query
iSeries
OPNQRYF
QRYSLT
Hi all, I am trying to query a PF to get back all records where fields OWNER1 and OWNER2 have an entered user value (ie; JOHN - I would want to return JOHN, JOHNATHON, JOHNS, ARJOHNS, etc) The best so far I have been able to come up with is: DCL VAR(&CF03) TYPE(*CHAR) DCL VAR(&INOWN) TYPE(*CHAR) LEN(10) DCL VAR(&OWNX) TYPE(*CHAR)LEN(15) DCLF FILE(NJCF/CON050FM) RCDFMT(SCRN01) SNDRCVF RCDFMT(SCRN01) CHGVAR VAR(&OWNX)VALUE(&INOWN *cat '*') OVRDBF FILE(CONNAM)TOFILE(TEST/CONNAM) SHARE(*YES) OPNQRYF FILE((TEST/CONNAM)) QRYSLT(' + OWNER1 *EQ %WLDCRD (''*CAT &OWNX *CAT'') ') CALL PGM(NJCF/CON050) CLOF OPNID(CONNAM) DLTOVR FILE(CONNAM) But I still cannot get the syntax right. I hope someone can show me what I am doing wrong or if there is a better way. Thank you. George

Answer Wiki

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

try this

OPNQRYF FILE((TEST/CONNAM)) QRYSLT(‘OWNER1 = +
%WLDCRD( ” * ‘ *cat &OWNX *cat ‘ * ” )’ )

Phil here
What you want is “*Name* ” so it’s wild before and after

CHGVAR VAR(&OWNX)VALUE( ‘*’ *cat &INOWN *cat ‘*’)

So &OWNX contains the *Name*

OPNQRYF FILE((TEST/CONNAM)) QRYSLT(‘OWNER1 = +
%WLDCRD( “‘ *cat &OWNX *cat ‘” )’ )

This looks odd
OPNQRYF FILE((TEST/CONNAM)) QRYSLT(‘OWNER1 = +
%WLDCRD( “‘ <– ”’ is a double+single puts a double quote and ends the string
OWNER1 = %WLDCRD(‘
*cat &OWNX adds to the string the value *OWNX*
*cat ‘” )’ ) single quote double adds to the string ”’)’ which is the string ‘)

To see what the string actually looks like send a message to yourself containing
the QRYSLT, for this reason it’s really good to create the QRYSLT as a variable.

As in
CHGVAR &QRYSLT value (‘OWNER1 = +
%WLDCRD( “‘ *cat &OWNX *cat ‘” )’ )
SNDMSG &qryslt ….
OPNQRYF FILE((TEST/CONNAM)) QRYSLT(&QRYSLT)

The message is exactly like it will be sent to the database query engine which must contain the value not the name of the variable enclosed in single or double quotes since it’s a string value.

Discuss This Question: 1  Reply

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Ohhh not CHGVAR VAR(&OWNX)VALUE( '*' *cat &INOWN *cat '*') but CHGVAR VAR(&OWNX)VALUE( '*' *cat &INOWN *Tcat '*') *cat would leave the blanks at the end so 'JOHN ' would be '*JOHN *' and would only match JOHN followed by a few blanks!
    49,720 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following