Re: Joining to a join in SQL?

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).