Re: SQL and defining data structures

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

In more recent releases one can also specify which columns to be 
included in an index - this can also be used in an F-spec and will 
likely be usable for external data structures.

Jack, you can make your own examples very easily - I'm sure there are 
not any out in the wild. If you have a header and detail set, go into 
STRSQL, say, and run CREATE VIEW using those files. Pick a subset of 
fields, ba-da-boom ba-da-bing - specify that view as the file used in a 
LIKEREC and there you are.

I encourage DIY in these lists!  :)

Cheers
Vern

On 8/31/2017 11:03 AM, Jack Tucky wrote:
> This is interesting.  If there are any examples of this please let me
> know.  I'll post if I find any.
>
> Thanks, Art
>
> On Thu, Aug 31, 2017 at 1:21 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
> wrote:
>
> > This happens not often, but this time I agree with Dieter.
> >
> > Create a view based on the complete select statement (with the exact field
> > selection you need and including all Common Table Expressions, Joins, Group
> > Bys, Nested-Sub-Selects etc. - without the Order By).
> > The view cannot only be used for accessing the data, but it can be
> > included as external data structure.
> >
> > In your program you define the view as external data structure.
> > In the Declare Statement you specify a simple SELECT * FROM VIEW (may be
> > with several additional WHERE conditions and an ORDER BY clause).
> > In your FETCH statement you fetch the data directly into the external data
> > structure.
> >
> > No need to create an additional file with all the fields or a data area or
> > what ever.
> > BTW a view is never keyed, it is nothing else than a stored select
> > statement.  In this way you theoretically can have thousands of views on
> > the same table without any performance impact.
> > ... and a view can be used where ever you use a table or physical file,
> > e.g. embedded SQL, ODBC, JDBC, Download to Excel, WebQuery and even
> > Query400 etc.
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser