MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: Joining to a join in SQL?



fixed

On 02 Oct 2012 17:13, James Lampert wrote:
Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx wrote:
If they are SQL views then yes you can create a view over it

Hmm. But as I just determined empirically, while one can also join to
a DDS join-logical in an SQL ad hoc query, one can't do so in a
CREATE VIEW.

Correct. A SQL VIEW can have a VIEW dependency [a VIEW can be the based-on file of another VIEW as shown by DSPDBR of the based-on file, IIRC presented with a special dependency type other than "Data"], but a DDS LF can not. A based-on file of a DDS LF can only be a PF.

FWiW: In releases prior to v3r1 there were some SQL catalog VIEW "system" files that were exempted from the rule, but AFaIK even those can no longer exist nor be created\restored; even if only because IBM no longer creates the based-on logical files and the upgrade code [at least up to N+2] would delete them as part of OS install.

And I just realized there's another issue: as I understand it, in
SQL, views and indexes are separate entities. Is there a way to make
an SQL view look like a keyed logical to an RPG program that uses
native RLA?

Using OPNQRYF of the VIEW, with matching [to the PGM declaration] KEYFLD specifications. Implicitly then, however, the query will be implemented using CQE. I believe some VIEW definitions for which SQE implementation is required, could make some VIEWs inoperable for use by OPNQRYF.? Minimally, for when OPNQRYF is prevented from allowing temporary results into which the SQE-implemented sets can be placed for use by the CQE query; i.e. according to ALWCPYDTA ¿and other? specification(s).






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact