Re: Alt index to SQL keyed view

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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!
Vern

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
> --
-snip-
> > > 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?