×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 11 May 2012 07:31, Raul A. Jager W. wrote:
Using DDS I can create a "view" that has an index and some data from
one table and data from other joined tables.
The DDS Join Logical File (JLF) can include K-specs to define a keyed
access path on the /primary/ [for DDS, the first] file in the join. The
database implicitly creates the access path(s) for the joined-to file.
The join is dynamic when the file is opened, just as with a query. So
effectively the keyed JLF is a VIEW with an ORDER BY [but with a static
sort sequence] that could be applied to the columns of any one TABLE
that could be the join-primary.
Can I do the same using SQL? at V7.1
There is no ORDER BY in the fullselect of a VIEW. The SQL VIEW
merely defines the result-set, not the order. Ordering is always
dynamic for the environment\result-set. There is no CREATE INDEX on a
VIEW because an INDEX is [considered to be] on the data. The extension
of the CREATE INDEX to allow selection [WHERE clause] and some limited
column\expression does not include the capability to define the JOIN
indexes [no JOIN clause] as can be effected by the DDS JLF. Because the
JOIN is dynamic, various CREATE INDEX against the TABLE data can enable
the optimizer to use a\some of those existing indexes [access paths] to
access the data, possibly instead of creating access paths dynamically
at run-time. The run-time is the SELECT query against the VIEW.
Specify an ORDER BY on a SELECT from that VIEW, and the collation
reflects the Sort Sequence (SRTSEQ) that is defined for the statement by
the executable or the job.
If the VIEW is defined and a program would prefer the same effect as
the JLF for that SQL VIEW, then the OPNQRYF can generate a shared ODP
with ordering using the KEYFLD() and SRTSEQ() parameters. Some
restrictions may apply [because of or\]and the query may be forced to
run CQE. Of course just defining and using the JLF might be easiest.
If making a copy of the joined data is acceptable, a MQT or some
other copy of the join result set could be created [and maintained],
over which an INDEX can be created. And because the data is since, in
just one TABLE, the INDEX can be created over the data from [what was
prior, in] both TABLEs.
Regards, Chuck
midrange.com
