Problem with the syntax of OPNQRY command

0 pts.
Tags:
OPNQRY
OPNQRYF
QRYSLT
RCVF
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 doesn't execute because of invalid quote(') positions. I still have to append four more fields to this but not able to execute even 1.
ASKED: December 15, 2008  2:58 PM
UPDATED: June 18, 2013  6:57 PM

Answer Wiki

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

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.

Discuss This Question: 7  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.

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
  • itanswers
    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.
    0 pointsBadges:
    report
  • Gilly400
    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.
    23,730 pointsBadges:
    report
  • philpl1jb
    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)
    48,575 pointsBadges:
    report
  • itanswers
    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?
    0 pointsBadges:
    report
  • philpl1jb
    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
    48,575 pointsBadges:
    report
  • itanswers
    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...
    0 pointsBadges:
    report
  • philpl1jb
    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.
    48,575 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