The OP is asking for something that is not possible. Rob, you address that, I think, with examples. I'll take a look from maybe a different angle.

A keyed LF can be used in all 3 environments. In SQL the key order will not be used, maybe not even if you specify the ORDER BY over the same columns

Generally speaking, unless there are select-omit criteria in the LF, SQL will ignore the LF, find its parent PF, then go through all the indexes or keyes LFs over that PF - might as well just specify the PF - again, unless there are S-O specs in it.

SQL cannot use any SQL index as the FROM file in a SELECT statement - period.  At least so far as I know.

I imagine that QRY/400 also cannot use an SQL index - it CAN, of course, use a keyed LF.

SQL views have no index - nada, zip, zilch, no way. You always have to use ORDER BY in SQL to ensure the sort order, and the optimizer might not use the LF or SQL index one expects.

An SQL index CAN be used in RPG - the sort order will be used, and a plain old index - no WHERE clause and no column list - will return the entire set of rows in the order of the index.

An SQL index can also look kind of like an LF - you can specify the columns to return when read, as well as selectivity with a WHERE clause that gives you select-omit behavior.

Enough said, I think.

Later, all!

On 11/6/2019 12:50 PM, Rob Berendt wrote:
I understand that using a DDS LF in your Query may have been a good practice in the past. For example, with IIM being the table, IIML01 may be a LF with not only an index but also record selection. In our case, "Active" records only.
And while you want to replace your DDS LF's with SQL indexes you would like to continue that "one stop shopping". I don't think that's possible.
Some may say that you should query against the table and it would then use that index. However would it really? Wouldn't it rather use an index with an index not just over your "key" but shouldn't the index also include your "where" clause? So your index would have your active record id column, then any key, or order by, columns. That index would not work for existing code using RPG RLA. So it would have to be in addition to. And you would still query the table or a view when using sql.

Rob Berendt
I need a keyed LF that can be used in RPG RLA, QRY/400, and SQL. If I use
a SQL view, I'm afraid I'll lose my >>keyed sequence.

Does that mean I have to use a DDS LF?

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by 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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.