× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.