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
_______________________________________________
This is the MI Programming on the AS400 / iSeries (MI400) mailing list
To post a message email: MI400-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mi400
or email: MI400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mi400.