As400 sql: How to create a view with keys?

Sorry:
RPG Program (and not ROG)

Hi Bob,

A view looks just like a table to a query.  Since the view is an extraction from a table, it cannot be indexed.

That said, if the view does not contain a filter or aggregation, any filter applied to a query of a view will attempt to use the indexes of the underlying table.  If the view uses a filter or aggregation, it results in a derived table that the query will then have to scan and filter.

Can't help with the RPG question...


Kent

Ok, but,
I need a VIEW with KEYS,
as it is possible to have a LF DDS with keys,
I need to have samothing like a view with keys,
because I have a field which is the result of a calculation
and this field is the key.
And I don't think I have to create a news table with this field and so
create a LF DDS file (or index).

Something like ORDER BY???

Hi Bob,

I'm not sure what you mean by, "view with keys".

Ok:
I need this:
Create View DDSF0023 as                            
select FNRDOC, FDTDOC, FNRRIG, FIVA, FCDAG,        
MOD(FNRDOC, 10000) as CDBILL from ddsf002          
ORDER BY FCDAG, CDBILL, FDTDOC                    

THE COMPILATOR SAYS THAT ORDER BY IS NOT VALID.

In DDS Logical File, you can declare Keys (K) so,
in RPGLE program you can chain these records with
the right key.

Ah. Ok...

DB2 doesn't propogate the alias CDBILL to the subquery's filter. You'll have to substitute the MOD function.

Create View DDSF0023 as
select FNRDOC, FDTDOC, FNRRIG, FIVA, FCDAG,
MOD(FNRDOC, 10000) as CDBILL
from ddsf002
ORDER BY FCDAG, MOD(FNRDOC, 10000), FDTDOC

The alias WILL propogate out to an outer query, but that usually adds another step to the query and reduces performance.


Kent

I made so. thanks.
(option 3) because I transformed a key field before chain it.