Using the STRSQLSTM
Source file . . . . . . . . . . Name
Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB
Source member . . . . . . . . . Name
Commitment control . . . . . . . *CHG *CHG, *UR, *CS, *ALL, *RS...
Naming . . . . . . . . . . . . . *SYS *SYS, *SQL
What do I put for my source file? I am trying to get away from the DDS logical file.
message: 3
date: Wed, 22 May 2013 18:17:21 -0700
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: [no subject]
Crystal Reports on Wednesday, May 22, 2013 10:43 AM
It seems to me, if I remember, the logical file, when used in
Crystal Reports, it passing not only all the records of the file
OEOEHDOH, but also all the many fields. Is this correct?
I am unsure what CR does for whatever is the undescribed specific
scenario [i.e. per the LF ?"used in"? but no explanation of how], and
generally what CR even allows to be done with an LF...
But a database request to open the LF [as created from the DDS source
included later\below, albeit with some extraneous "R" characters that
are apparently ignored by the DDS compiler for an LF], would include all
columns from the PF. The rows available from that open, the Open Data
Path (ODP), are those defined to be selected per the select\omit logic.
The data from the ODP is returned in the order of the keys when the
file is opened using a keyed open access method, but for a query [an
implicitly non-keyed] open, the order is undefined except whatever is
specified in the query request.
Whether or not the open is a query open, only the rows selected
according to the logic in the LF should be returned to the program [or
SQL request] that opened the file. IIRC there were a few separate
message threads posted to this list earlier in v7 implying that some SQL
requests were not properly honoring the selection defined in the LF,
however I do not similarly recall that any followup messages were given
by the OP after the given response was effectively to "contact
IBM\service-provider". I finally found the following message from a
thread I recalled, and there is no post-report followup in the archived
thread for that month nor the next; that message is the first link
below, the second link below is a message where I give a link to another
message thread "like" that one, and the third link below is to the thread:
http://archive.midrange.com/midrange-l/201211/msg00484.html
http://archive.midrange.com/midrange-l/201211/msg00468.html
http://archive.midrange.com/midrange-l/201211/threads.html#00415
Avoiding use of the DDS LF on the FROM clause of a query should
prevent any such issues as alluded in the aforementioned; use a SQL VIEW
instead, or reference the TABLE with the necessary selection in the
query instead of the VIEW.
If the CR only is interested in say 5 fields, how can we code this
logical or to make a new one that will only pass less,
When no field-name specifications are listed under the Record
definition, then the Record Format for the Logical File is the same as
the RcdFmt of the Physical File named\specified on the PFILE(); i.e. the
LF /shares/ the record format of the PF. By specifying only a subset of
the field names from the PF, then only those field names will be
available from that LF, and thus only those columns can be referenced by
the program [or query request] that opens the LF.
is that a performance issue would you say?
Depends on what "that" intends to imply:
- Reducing the number of fields can be a boon for performance because
less data can be /carried/ [per row] between the database and the
program. Of course a query request that would select only those same or
fewer fields gets the same advantage, while not also limiting what
fields a non-query open or other query requests can access via the LF
which leaves all of the columns included.
- Creating a new keyed access path could have a negative impact on
overall performance for updates, if the new AccPth must be maintained
- For retrieval purposes and for any /statistics/ provided to the
SQL, the impact on performance for read activity could be improved with
a new maintained keyed AccPth with a new LF.
I have also added a temporary filter to this logical
R OEORHDRR PFILE(OEORHDOH)
K OHORDT R
K OHSHTC R
K OHORDD R DESCEND
O OHORDD COMP(LT 20130101) *** MY ADD
O OHSHTC COMP(EQ ' ')
The logical expression [as would be coded in SQL] that is the
equivalent to the above keyed accpth selection, is:
NOT ( (OHORDD < 20130101) OR (OHSHTC = ' ') )
That is the equivalent of the expression:
(OHORDD >= 20130101) AND (OHSHTC <> ' ')
Per the added ["*** MY ADD"] omit logic, the number of rows should
logically either stay the same or decrease; i.e. the selected rows
should be limited to only those which represent a [8 digit form of a]
date value since the beginning of the year rather than returning rows
from all years\dates.
If too many records are returned [e.g. perhaps seemingly all, but
unverified as such], I wonder if perhaps a variant of the LF had been
created and used whereby the logical AND was effected as the operator
instead of the OR operator, per having replaced the second O [Omit
designation] with a blank. That would have effected selection logic of
"(OHORDD>=20130101) OR (OHSHTC<>'')" which could greatly expand the
number of selected rows; or per my reference to past issues reporting a
concern of rows not matching the s/o specifications. FWiW I have
comments including a link to and a snippet of the docs for slt/omt
specification logic at the following link:
http://archive.midrange.com/rpg400-l/201202/msg00453.html
midrange.com
