Re: CHAIN to an SQL LF? -- RPG400-L

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...