ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to add a keys to SQL View

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to add a keys to SQL View

    Hello,
    I would appreciate if anybody have an answer for the following.
    I need to add a key to SQL view and know that is not possible to do directly.
    however, there was a way around as in this code:
    CREATE VIEW SCHEMA_NAME/VIEW_NAME as SELECT * FROM (SELECT * FROM TABLE_NAME ORDER BY 1) AS t For some reasons this doesn't work even in interactive SQL (without create view).
    I would appreciate any comment for this problem.

    Thank you

  • #2
    Short answer, views are always unkeyed. A view can wrap everything that is allowed in an SQL SELECT-Statement with the exception of the ORDER BY.
    When executing SQL it is always the query optimizer who decides if or which access path (index or DDS described logical file) is used.

    At run time you can add an ORDER BY to your SELECT ... FROM VIEW statement.

    Birgitta

    Comment


    • #3
      If you need it keyed for using in standard RPG I/O (DCL-F... or F spec) then you'll need to create it using DDS - OR you can create it as an index rather than a view. You don't use Indexes in SQL Select statements - but you can use them in RPG

      Comment

      Working...
      X