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.