opnqryf - IBM AS400

This is a discussion on opnqryf - IBM AS400 ; I haven't worked much with OPENQRYF. Is there a good on-line source that would have some examples I could follow that you could give me a link to? I have a physical file that contains the date in two separate ...

+ Reply to Thread
Results 1 to 10 of 10

Thread: opnqryf

  1. opnqryf

    I haven't worked much with OPENQRYF. Is there a good on-line source
    that would have some examples I could follow that you could give me a
    link to?

    I have a physical file that contains the date in two separate fields
    both defined as numeric.:

    PFYR = YYYY
    PFMD = MMDD

    Both of these fields are defined as numeric.

    I would like to use OPNQRYF to select a range of records on the fly
    which includes certain dates.

    How do I do this in a QRYSLT statement? Do I need to first create a
    concatenated field of PFYR & PFMD into DATEYYMD through a CL Program
    or can I do this with a MAPFLD parm on the OPNQRYF and then do a range
    like select like:

    'QRYSLT('DATEYYMD * EQ %RANGE("19990101" "20051231") ')

    Do I need the quotes around the two dates? I'm not sure what the
    ramifications are with the dates in the PF being numeric and maybe for
    the qryslt statement they need to be character?

    Another basic question. I want to use this file with an RPG program.
    If my physical file is FILEA I assume in the C/L I do something like::

    OVRDBF file(OQFFILEA) tofile(FILEA) share(*yes)

    OPNQRYF FILE(FILEA) FORMAT(OQFFILEA)
    MAPFLD...
    QRYSLT...

    CALL PGM (and this program should have FILEA as a file???? but it will
    use my OQFFILEA file because of the OVRDBF

    CLOF FILE(FILEA)

    DLTOVR FILE(OQFFILEA)

    When does OQFFILEA go away???? Is it just a temp file???

    thanks,
    ga
    ga
    nospam@nospam.fmctc.com

  2. Re: opnqryf

    On Mon, 06 Aug 2007 14:32:26 -0500, ga wrote:

    >I haven't worked much with OPENQRYF. Is there a good on-line source
    >that would have some examples I could follow that you could give me a
    >link to?
    >
    >I have a physical file that contains the date in two separate fields
    >both defined as numeric.:
    >
    >PFYR = YYYY
    >PFMD = MMDD
    >
    >Both of these fields are defined as numeric.
    >
    >I would like to use OPNQRYF to select a range of records on the fly
    >which includes certain dates.
    >
    >How do I do this in a QRYSLT statement? Do I need to first create a
    >concatenated field of PFYR & PFMD into DATEYYMD through a CL Program
    >or can I do this with a MAPFLD parm on the OPNQRYF and then do a range
    >like select like:
    >
    >'QRYSLT('DATEYYMD * EQ %RANGE("19990101" "20051231") ')
    >
    >Do I need the quotes around the two dates? I'm not sure what the
    >ramifications are with the dates in the PF being numeric and maybe for
    >the qryslt statement they need to be character?
    >
    >Another basic question. I want to use this file with an RPG program.
    >If my physical file is FILEA I assume in the C/L I do something like::
    >
    >OVRDBF file(OQFFILEA) tofile(FILEA) share(*yes)
    >
    >OPNQRYF FILE(FILEA) FORMAT(OQFFILEA)
    > MAPFLD...
    > QRYSLT...
    >
    >CALL PGM (and this program should have FILEA as a file???? but it will
    >use my OQFFILEA file because of the OVRDBF
    >
    >CLOF FILE(FILEA)
    >
    >DLTOVR FILE(OQFFILEA)
    >
    >When does OQFFILEA go away???? Is it just a temp file???
    >
    >thanks,
    >ga
    >ga


    Off the top of my head!

    OVRDBF FILE(FILEA) SHARE(*YES)

    OPNQRYF FILE((FILEA)) +
    QRYSLT('DATEYYMD=%RANGE(19990101 20051231)') +
    MAPFLD(DATEYYMD '(PFYR * 10000) + PFMD')

    ...or..

    OPNQRYF FILE((FILEA)) +
    QRYSLT('((PFYR*10000)+PFMD)=%RANGE(19990101 20051231)')

    ...or..

    OPNQRYF FILE((FILEA)) +
    QRYSLT('((PFYR*10000)+PFMD)=%RANGE(' || &LODATE || ' ' || &HIDATE || ')')

    (*EQ and *CAT can also be used depending on your preference)

    CLOF FILE(FILEA)
    DLTOVR FILE(FILEA)

    From your example I don't think you need to switch file formats. This is more typically
    done when joining files and then presenting, in effect, a third file to the RPG. File
    OQFFILEA would need to exist for compile purposes only (memberless if you prefer) and it
    gets temporarily created by the OPNQRYF. The override would then be to the primary file.

    --
    Mark



  3. Re: opnqryf

    On Aug 6, 3:32 pm, ga wrote:
    > I haven't worked much with OPENQRYF. Is there a good on-line source
    > that would have some examples I could follow that you could give me a
    > link to?
    >
    > I have a physical file that contains the date in two separate fields
    > both defined as numeric.:
    >
    > PFYR = YYYY
    > PFMD = MMDD
    >
    > Both of these fields are defined as numeric.
    >
    > I would like to use OPNQRYF to select a range of records on the fly
    > which includes certain dates.
    >
    > How do I do this in a QRYSLT statement? Do I need to first create a
    > concatenated field of PFYR & PFMD into DATEYYMD through a CL Program
    > or can I do this with a MAPFLD parm on the OPNQRYF and then do a range
    > like select like:
    >
    > 'QRYSLT('DATEYYMD * EQ %RANGE("19990101" "20051231") ')
    >
    > Do I need the quotes around the two dates? I'm not sure what the
    > ramifications are with the dates in the PF being numeric and maybe for
    > the qryslt statement they need to be character?
    >
    > Another basic question. I want to use this file with an RPG program.
    > If my physical file is FILEA I assume in the C/L I do something like::
    >
    > OVRDBF file(OQFFILEA) tofile(FILEA) share(*yes)
    >
    > OPNQRYF FILE(FILEA) FORMAT(OQFFILEA)
    > MAPFLD...
    > QRYSLT...
    >
    > CALL PGM (and this program should have FILEA as a file???? but it will
    > use my OQFFILEA file because of the OVRDBF
    >
    > CLOF FILE(FILEA)
    >
    > DLTOVR FILE(OQFFILEA)
    >
    > When does OQFFILEA go away???? Is it just a temp file???
    >
    > thanks,
    > ga
    > ga
    > nos...@nospam.fmctc.com


    A couple of suggestions when using OPNQRYF

    1. Quotes are needed when comparing to a character field.
    2. MAPFLD is only need if you need to create a new field (over an
    existing field)
    3. FORMAT is only required when you are changing the format of the
    original file
    4. Use a variable (like &QRYSLT). Build the query select using
    CHGVAR. The pass &QRYSLT as the parm to OPNQRYF. This allows for
    building the query piecemeal, for conditionally adding parts of the
    query, and you can dump the variable to debug.
    5. Use SQL whenever possible to avoid OPNQRYF. SQL uses the faster
    SQE engine, and usually easier and more flexible to setup.

    If you do decide you want to pursue OPNQRYF, a good book is "Open
    Query File Magic" by Ted Holt.


  4. Re: opnqryf

    The original example presumably does not express well, the alluded
    intent for more generic date ranges.? That is because it would seem to
    be excessive to create any expressions, just for a selection by year.
    For the given example, to request a date range of 1999-01-01 to
    2005-12-31, simply request:
    QRYSLT('PFYR=%range(1999 2005)')

    When the range of dates is within the same year, any calculations are
    also unnecessary, such that the selection can be [optionally changing
    the year range to a simple equivalence test]:
    QRYSLT('PFYR=%range(1999 1999) *and PFMD=%range(0401 0630)')

    A non-trivial example would best be expressed, requesting a date
    range of 1999-04-01 to 2005-06-30

    Thus only for a multi-year span [for other than full years] need the
    expression be generated:
    QRYSLT('((PFYR*10000)+PFMD)=%RANGE(19990401 20050630)')

    Even then, depending on the available indexes on the two fields, the
    sparseness of the data in the indexes, the overall number of rows, or
    even if there will be a full table scan anyway, the best performance may
    be effected from generating the following three-part expression [to
    avoid both the selection on an expression, and any *OR condition]:
    QRYSLT('PFYR=%range(1999 2005) *and
    *not (PFYR=1999 *and PFMD=%range(0101 0331)) *and
    *not (PFYR=2005 *and PFMD=%range(0701 1231)) ')

    The cost of ensuring the appropriate coded logic to effect the above
    special cased selections, may outweigh the benefits of simply coding the
    expression and accepting any performance impact from using that
    expression in the selection.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    ga wrote:
    > I haven't worked much with OPNQRYF. Is there a good on-line source
    > that would have some examples I could follow that you could give me a
    > link to?
    >
    > I have a physical file that contains the date in two separate fields
    > both defined as numeric.:
    >
    > PFYR = YYYY
    > PFMD = MMDD
    >
    > Both of these fields are defined as numeric.
    >
    > I would like to use OPNQRYF to select a range of records on the fly
    > which includes certain dates.
    >
    > How do I do this in a QRYSLT statement? Do I need to first create a
    > concatenated field of PFYR & PFMD into DATEYYMD through a CL Program
    > or can I do this with a MAPFLD parm on the OPNQRYF and then do a range
    > like select like:
    >
    > 'QRYSLT('DATEYYMD * EQ %RANGE("19990101" "20051231") ')
    >
    > Do I need the quotes around the two dates? I'm not sure what the
    > ramifications are with the dates in the PF being numeric and maybe for
    > the qryslt statement they need to be character?
    > <>


  5. Re: opnqryf

    Chuck, Graybeard, Mark...

    Thanks to all of you for your suggestions and examples. They helped a
    great deal and I have it working, but my next step is to polish it up
    and use prompting, etc. . Chuck you are correct that it might span odd
    dates, i.e. 1999-03-06 through 2003-05-08, not necessarily full years.

    Here is another question though...

    Let's say I have the current year records in one file. And the 4
    prior years in another file. Both files are identical in layout.

    Can I do an opnqryf over both files at once? What would be the best
    way to join them. Let's say I need to get records from 2003-05-1
    through 2007-03-28. Some records in the current year file, some in
    the 4-yr history file.

    Can I use OPNQRYF to span both files and select records since the same
    format is used, or do I need to link the files together with a CRTLF
    or some other method.

    Just fishing for ideas. I appreciated all the tips and your examples
    Chuck, on how to exclude (using *Not), really open some ideas up for
    me. Sometimes we forget to think outside the box or use other
    methods.

    I appreciate all of your kind response and no one belittling my lack
    of knowledge and experience. I have gotten so much help from this
    forum and it's great to have "mentors" so readily available.

    Thanks again,
    ga

    CRPence wrote:

    > The original example presumably does not express well, the alluded
    >intent for more generic date ranges.? That is because it would seem to
    >be excessive to create any expressions, just for a selection by year.
    >For the given example, to request a date range of 1999-01-01 to
    >2005-12-31, simply request:
    > QRYSLT('PFYR=%range(1999 2005)')
    >
    > When the range of dates is within the same year, any calculations are
    >also unnecessary, such that the selection can be [optionally changing
    >the year range to a simple equivalence test]:
    > QRYSLT('PFYR=%range(1999 1999) *and PFMD=%range(0401 0630)')
    >
    > A non-trivial example would best be expressed, requesting a date
    >range of 1999-04-01 to 2005-06-30
    >
    > Thus only for a multi-year span [for other than full years] need the
    >expression be generated:
    > QRYSLT('((PFYR*10000)+PFMD)=%RANGE(19990401 20050630)')
    >
    > Even then, depending on the available indexes on the two fields, the
    >sparseness of the data in the indexes, the overall number of rows, or
    >even if there will be a full table scan anyway, the best performance may
    >be effected from generating the following three-part expression [to
    >avoid both the selection on an expression, and any *OR condition]:
    > QRYSLT('PFYR=%range(1999 2005) *and
    > *not (PFYR=1999 *and PFMD=%range(0101 0331)) *and
    > *not (PFYR=2005 *and PFMD=%range(0701 1231)) ')
    >
    > The cost of ensuring the appropriate coded logic to effect the above
    >special cased selections, may outweigh the benefits of simply coding the
    >expression and accepting any performance impact from using that
    >expression in the selection.
    >
    >Regards, Chuck


    ga
    nospam@nospam.fmctc.com

  6. Re: opnqryf

    What is desired in that case, is a UNION of the data. That can be
    done by either a Multi-Format logical file or SQL UNION. In the noted
    case, with same-named formats, just accessing different files using the
    PFILE kwd in DDS. The SQL now allows UNION in a VIEW, so that is an
    easier solution to code [IMO, as compared to DDS]. Just request:
    create view library.filename as (
    select * from library.currentyear
    UNION ALL
    select * from library.yearspast )
    If the ordering of data is by date, simply creating two OPNQRYF Open
    Data Paths [ODP] with one for each file, would enable the program to
    declare two files and process each successively. This of course because
    the files already provide the logical separation of the data by date.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    ga wrote:
    > <>
    > Let's say I have the current year records in one file. And the 4
    > prior years in another file. Both files are identical in layout.
    >
    > Can I do an opnqryf over both files at once? What would be the best
    > way to join them. Let's say I need to get records from 2003-05-1
    > through 2007-03-28. Some records in the current year file, some in
    > the 4-yr history file.
    >
    > Can I use OPNQRYF to span both files and select records since the same
    > format is used, or do I need to link the files together with a CRTLF
    > or some other method.
    > <>


  7. Re: opnqryf

    Chuck,

    I created a "view" from the two files which worked great.

    I then did:

    ovrdbf file(pgmfile) tofile(viewfile) lvlchk(*no) share(*yes)
    Opnqryf file((pgmfile) qryslt('(TTTYP="T") * and
    ((tttyr*10000)+TTMD=%range(20061001 270228)') keyfld((ttsb) (TTT#))
    Call PGM
    CLOF OPNID(pgmfile)
    DLTOVR FILE(TADTICT)

    The opnqryf seem to work, but when the program loads I get a RPG1011
    (undefined record type is found in SLSRC1 00000000 pgmfile).
    Apparently I cannot use OPNQRYF with a file created like this?? I
    suppose if I go to SQL to extract the records I want maybe it will
    work fine? If I do a DSPFFD on the viewfile, the fields/positions are
    the same...not sure what is going on here?

    I am sorting the data by two different fields but the opnqryf part
    seems to work; for some reason, it's bombing on the program.

    Sorry to waste your time but any suggestions?

    thanks chuck,
    ga

    CRPence wrote:

    > What is desired in that case, is a UNION of the data. That can be
    >done by either a Multi-Format logical file or SQL UNION. In the noted
    >case, with same-named formats, just accessing different files using the
    >PFILE kwd in DDS. The SQL now allows UNION in a VIEW, so that is an
    >easier solution to code [IMO, as compared to DDS]. Just request:
    > create view library.filename as (
    > select * from library.currentyear
    > UNION ALL
    > select * from library.yearspast )
    > If the ordering of data is by date, simply creating two OPNQRYF Open
    >Data Paths [ODP] with one for each file, would enable the program to
    >declare two files and process each successively. This of course because
    >the files already provide the logical separation of the data by date.
    >
    >Regards, Chuck


    ga
    nospam@nospam.fmctc.com

  8. Re: opnqryf

    Row Level Access [RLA] functionality could still be somewhat limited
    for the new SQL UNION VIEW files. Using OPNQRYF should eliminate most
    problems. That said, I am not sure what the RPG1011 is trying to say,
    unless perhaps the program was compiled against the physical file
    instead of the VIEW; and the physical file had a different format name.
    That might also explain why LVLCHK(*NO) is being used. And that
    should not be a requirement, if the compile was directed against the VIEW.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    ga wrote:
    > Chuck,
    >
    > I created a "view" from the two files which worked great.
    >
    > I then did:
    >
    > ovrdbf file(pgmfile) tofile(viewfile) lvlchk(*no) share(*yes)
    > Opnqryf file((pgmfile) qryslt('(TTTYP="T") * and
    > ((tttyr*10000)+TTMD=%range(20061001 270228)') keyfld((ttsb) (TTT#))
    > Call PGM
    > CLOF OPNID(pgmfile)
    > DLTOVR FILE(TADTICT)
    >
    > The opnqryf seem to work, but when the program loads I get a RPG1011
    > (undefined record type is found in SLSRC1 00000000 pgmfile).
    > Apparently I cannot use OPNQRYF with a file created like this?? I
    > suppose if I go to SQL to extract the records I want maybe it will
    > work fine? If I do a DSPFFD on the viewfile, the fields/positions are
    > the same...not sure what is going on here?
    >
    > I am sorting the data by two different fields but the opnqryf part
    > seems to work; for some reason, it's bombing on the program.
    >
    > Sorry to waste your time but any suggestions?


  9. Re: opnqryf

    An SQL VIEW is just a logical representation describing the what and
    how about the /physical/ data that is to be extracted and presented to
    the program/interface which [when, at run-time, it] accesses the data
    from that VIEW. Thus whatever underlying changes are performed to the
    physical data, have no impact to the VIEW itself. The only impacts are
    to what data will be extracted when the VIEW is accessed; i.e. during
    the run-time, when the file is accessed/opened to actually extract the
    underlying/referenced data.
    The VIEW is basically an OPNQRYF ODP without the dynamic ability to
    specify the selection. Adding the OPNQRYF over the VIEW to effect the
    added dynamic selection [and ordering] is conceptually /stacking/
    OPNQRYF requests [except in this case, OPNQRYF does not have UNION
    capabilities].
    Unlike a keyed LF [logical file], the VIEW has no /access path/
    [index] to be updated when the data changes. If the access path exists
    to enable better run-time access of the data, it would generally be an
    access path associated with an SQL INDEX [which is implemented as a
    keyed LF]. That is important to understand; the SQL returns a SET of
    [unordered] data, so the data set returned by the VIEW has no ORDER
    [capability].
    Neither the PF [Physical File] nor the PFM [Physical File Member] can
    be deleted by DLTF [Delete File] nor RMVM [Remove Member] because the
    database does not allow the underlying physical data to be removed while
    there is a dependent logical file [which a VIEW is; see DSPDBR PFname
    and WRKMSGD CPF3219].

    So [I think what is desirable in the given scenario, is to] change
    the CREATE VIEW to specify the RCDFMT name, and to compile the program
    against the VIEW instead of the PF. Then the OVRDBF [having elminated
    the LVLVHK(*NO)] and OPNQRYF preceding the CALL to the program should
    work. Any changes to the data will be seen in the program again since
    the last call.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    ga wrote:
    > <>
    > Next question. Any issues with "views"??? I assume they will be
    > updated just as any other logical file built over physical files? If
    > I do a file reorg on the physical file(s), does that cause a problem
    > if the view is still there??? As long as I don't delete the physical
    > file? As records are added to either of the physical files, I assume
    > the view updates also??
    >
    > Since I have created the view, and then renamed it, and then changed
    > my RPG pgm to use the rename file and the member originally created on
    > the "create view", it all works. Just want to make sure my view will
    > continue to update as records are added/changed/deleted to the
    > physical files, just like any other logical file.
    >
    > I'm sure you'll tell me a simpler way to do this.
    >
    > thanks,
    > ga


  10. Re: opnqryf

    On Aug 9, 2:56 pm, CRPence wrote:
    > An SQL VIEW is just a logical representation describing the what and
    > how about the /physical/ data that is to be extracted and presented to
    > the program/interface which [when, at run-time, it] accesses the data
    > from that VIEW. Thus whatever underlying changes are performed to the
    > physical data, have no impact to the VIEW itself. The only impacts are
    > to what data will be extracted when the VIEW is accessed; i.e. during
    > the run-time, when the file is accessed/opened to actually extract the
    > underlying/referenced data.
    > The VIEW is basically an OPNQRYF ODP without the dynamic ability to
    > specify the selection. Adding the OPNQRYF over the VIEW to effect the
    > added dynamic selection [and ordering] is conceptually /stacking/
    > OPNQRYF requests [except in this case, OPNQRYF does not have UNION
    > capabilities].
    > Unlike a keyed LF [logical file], the VIEW has no /access path/
    > [index] to be updated when the data changes. If the access path exists
    > to enable better run-time access of the data, it would generally be an
    > access path associated with an SQL INDEX [which is implemented as a
    > keyed LF]. That is important to understand; the SQL returns a SET of
    > [unordered] data, so the data set returned by the VIEW has no ORDER
    > [capability].
    > Neither the PF [Physical File] nor the PFM [Physical File Member] can
    > be deleted by DLTF [Delete File] nor RMVM [Remove Member] because the
    > database does not allow the underlying physical data to be removed while
    > there is a dependent logical file [which a VIEW is; see DSPDBR PFname
    > and WRKMSGD CPF3219].
    >
    > So [I think what is desirable in the given scenario, is to] change
    > the CREATE VIEW to specify the RCDFMT name, and to compile the program
    > against the VIEW instead of the PF. Then the OVRDBF [having elminated
    > the LVLVHK(*NO)] and OPNQRYF preceding the CALL to the program should
    > work. Any changes to the data will be seen in the program again since
    > the last call.
    >
    > Regards, Chuck
    > --
    > All comments provided "as is" with no warranties of any kind
    > whatsoever and may not represent positions, strategies, nor views of my
    > employer
    >
    >
    >
    > ga wrote:
    > > <>
    > > Next question. Any issues with "views"??? I assume they will be
    > > updated just as any other logical file built over physical files? If
    > > I do a file reorg on the physical file(s), does that cause a problem
    > > if the view is still there??? As long as I don't delete the physical
    > > file? As records are added to either of the physical files, I assume
    > > the view updates also??

    >
    > > Since I have created the view, and then renamed it, and then changed
    > > my RPG pgm to use the rename file and the member originally created on
    > > the "create view", it all works. Just want to make sure my view will
    > > continue to update as records are added/changed/deleted to the
    > > physical files, just like any other logical file.

    >
    > > I'm sure you'll tell me a simpler way to do this.

    >
    > > thanks,
    > > ga- Hide quoted text -

    >
    > - Show quoted text -


    I have successfully done this before. Reference the VIEW name in the
    RPG pgm as suggested. Put a RENAME option on the F spec to rename the
    format to something other than the file name. It should work.

    A more "correct" way of doing this would be to put a SELECT statement
    inside the RPG program and use a cursor to read the view, thus
    eliminating the OPNQRYF completely.



+ Reply to Thread