Re: Problem with an SQL View that has selects/omits

James H. H. Lampert wrote:

Mark S. Waterbury wrote:

You do know that an SQL VIEW does not include any index
automatically, right?

Right, and if there's a process for attaching an index to an SQL
VIEW, so that RLA can access it by key, I don't see it, and if I
knew about it, I don't remember it. (There certainly OUGHT to be
one, if there isn't!)

That would be accomplished via a shared ODP created by OPNQRYF KEYFLD(), the program compiled against & for keyed open & read activity of the equivalent record format.

One difference: when I check the number of records, the DDS LF
returns the number of records in the PF (10, for the "guinea pig"
library I was given), while the SQL VIEW returns the number that
the WHERE clause finds (only 1).

How was "check the number of records" effected? If by DSPFD, such that the access path detail for the logical file member suggests all physical rows are accessible via that LF index, then any rows not returned by a read are excluded only by the cursor selection; i.e. excluded by dynamic selection. If checked\counted by keyed open and read, then the select\omit rules do not match the WHERE clause.?

But other than that, I just ran a trace of everything that goes
on between returning from the SETLL-equivalent call, and issuing
the READ-equivalent call, and in some 150-odd instructions, the
only difference is an extra iteration of a loop that massages the
aforementioned total number of records, so it can be displayed.

So the MI program loops one more time and thus issues one more READ [i.e. call to QDBGETSQ] when processing the VIEW as compared to when processing the DDS LF?

I'm now going to do something in RPG (thanks, Chuck), that will hopefully simulate the point-of-failure, to see what *it* does.

I see that was already done, but I am still confused about the actual file definitions, the physical data, and the selection defined for each. I am beginning to think the LF must be a non-keyed LF?

Regards, Chuck