opnqry
Hi
I have a problem with the syntax of OPNQRY command
I am getting values from a display file using RCVF and then using the qryslt parameter in OPNQRY to select a few records;See the command below
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT('APCMPY *EQ ''*CAT XPCMP *CAT''')
Where XPCMP(numeric) is the screen field name.This command doesnt execute because of invalid quote(') positions. I still have to append four more fields to this but not able to execute even 1.
Software/Hardware used:
Software/Hardware used:
ASKED:
December 15, 2008 2:58 PM
UPDATED:
December 17, 2008 2:56 PM
Answer Wiki:
The query select is processed twice - once by CL - processing your string commands
then by the database manager.
When this string is processed by CL
'APCMPY *EQ ''*CAT XPCMP *CAT'''
It gets.
APCMPY *EQ ' *CAT XPCMP *CAT'
But thats not what the query processor needs
which would look like this - if the value were 00250
APCOMP *EQ "00250"
to get that
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT('APCMPY *EQ '' ' *CAT &XPCMP *CAT ' '' ')
Where '' represents to CL a put a quote here don't close the string
so this reads
'APCMPY *EQ ''put a quote here don't close the string 'close the string
the string needed to be closed so CL would recognize the *CAT and the field name
*CAT &XPCMP *CAT
which adds the field to the string (note & to indicate a value/field
followed by 'start string ''put a quote here don't close the string 'close the string)
What a monster
I generally have a varable &QSEL
build the value in it and send it do myself during testing, that way I see what
will be sent to the query engine.
Phil
Note that Phil has replaced XPCMP with &XPCMP - all variables in a CL are prefixed by a & character.
To see all answers submitted to the Answer Wiki: View Answer History.
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT(‘(”APCMPY *EQ “”*CAT &XPCMP +
*CAT””)’)
this is what the system puts when i tried your suggestion and the command fails again.
Hi,
Type the command like Phil has in his example above. Don’t prompt the command – the command prompter can misunderstand the query select parameter and add extra quotes.
Regards,
Martin Gilbert.
fix #1 I had a couple of spaces that shouldn’t have been in the phrase
they would have caused the expression to read ‘ 00250 ‘ instead of ’00250′
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT(‘APCMPY *EQ ”’ *CAT &XPCMP *CAT ”’ ‘)
fix #2 because &XPCOMP is a numeric field this will probably fail on compile
DCL &wrkCOMP *CHAR 10
CHRVAR &wrkCOMP &XPCOMP
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT(‘APCMPY *EQ ”’ *CAT &wrkCMP *CAT ”’ ‘)
Fix #3 if APCMPY is numeric the value doesn’t need the quotes around it
OPNQRYF FILE((*LIBL/APH *FIRST *ONLY)) +
QRYSLT(‘APCMPY *EQ ‘ *CAT &wrkCMP)
Hey thanks all for ur support as usual.This forum is great for beginners like me.
Regarding the OPNQRY i eventually converted the Numeric variable to Char and did the QRYSLT and it finally worked after a long try.
The next task for me is to move these selected records in Query to a new flat file via RPG after some validations.Can you suggest coding logic to read and write these records to a flat file.
Also is it advisable to use OPNQRY for such a task or can a sole RPG perform this task(excepting SQLRPG) or is there other methods?
OPNQRYF is used a lot – it gives the ability to make single rpg program and give it a great deal of flexibility based on the QRYSLT rules. These rules can be built on the fly.
OpnQryF probably wouldn’t have the performance of SQL but you’re going to see a lot of OpnQryF
in most shops.
Sequence in the CL
OVRDBF on the file command with SHARE(*YES)
OPNQRYF
Call RPG program
DLTOVR
RPG program would use file APH for input
(your Query file APH looks structurally like the original file APH — this is important when compiling
and running the program, the OPNQRYF can also create files that don’t have the structure of the
parent file, this creates additional issues when compiling)
and would loop through the records (reads)
formating and writing each one to the output file
Thanks for your input…If you can elaborate on the RPG part it would be helpful that is the exact logic to read the records and write them to a flat file.The loop structure probably…
Not sure that’s what you need when you’re learning stuff.
Time for you to give it a shot and come back with questions as you go.