Re: CHAIN to an SQL LF?

[Joep Beckeringh <joep@xxxxxxxxxxxxxxxxxxx>]

Tony,

The OS/400 (or CPF) system:
- Physical files contain the data
- Logical files can be a combination of:
  - Subset of fields ('view')
  - Derived fields (substring / concatenate)
  - Sorting (specifying keys)
  - Selection
  - Combining files 'horizontally' (join)
  - Combining files 'vertically' (multiple format logical files)

The SQL system:
- Tables contain the data
- Views can be a combination of:
  - Subset of fields
  - Derived fields (endless possibilities)
  - Selection
  - Combining files 'horizontally' (join)
  - Combining files 'vertically' (union; records must be identical)
- Indexes are used by the optimizer

When IBM brought SQL to the AS/400, it had to map the SQL artefacts to OS/400 
objects:
- Tables are physical files
- Views are logical files
- Indexes are logical files

As you noticed, this has some interesting consequences:
- A combination of a view with a sorting order can only be created with DDS (I 
never really understood why SQL does not allow you to specify an ORDER BY in a view)
- SQL views are logical files and can be read by RPG, but you cannot specify any 
sorting order (theoretically it is possible that two runs of the same program 
get two totally different orders)
- SQL indexes are logical files and most can be read by RPG (EVIs cannot)

As others have explained, your problem can quite easily be solved with DDS. 
Another option (which could be interesting, because the possibilities for 
deriving fields in SQL views are much more powerful than those of DDS) would be 
to use OPNQRYF on the SQL view for the sorting.

Joep Beckeringh



Tony Carolla wrote:

> I have a file with a memo field, and I want an LF over this file that
> only shows records where the first character of the memo field
> contains a '6'.  I dont' know that there is a way to do this using
> DDS, so I have built an SQL LF using the RUN SQL SCRIPTS applet over
> the file, that only selects records based on this condition.  The
> problem is I can't CHAIN to this file because it is non-keyed.
>
> Is there a way to create an SQL LF with 'keys'?  
>
> I don't have the iSeries SQL development LPs either...