Getting wildcard results from OPNQRYF
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
Software/Hardware used:
Software/Hardware used:
ASKED:
October 23, 2008 2:07 PM
UPDATED:
October 24, 2008 6:59 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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!