RE: Improve SQLRPGLE performance

[Joel Cochran <jrc@xxxxxxxxxx>]

On Wed, 2004-08-04 at 13:44, mwalter@xxxxxxxxxxxxxxx wrote:
> CREATE VIEW myView AS SELECT DATE(myTimeStamp) as myDate, otherfields FROM
> myPF
> 
> Thanks,
> 
> Mark
> 
> Mark D. Walter
> Senior Programmer/Analyst
> CCX, Inc.
> mwalter@xxxxxxxxxx
> http://www.ccxinc.com

As we discussed (and I learned) recently, a View is not an index and
doens't produce one.  I think Peter was looking for a way to index the
date portion.  I tried creating an index with a columnar function like
so:

create index joellib/teststamp1 on joellib/teststamp ( date(stamp) )

The system wouldn't allow it, so I guess that isn't an option.  Maybe
the best answer is to create an index over the timestamp field and then
convert the date field to a timestamp in the program (or the SQL
statement):

create index joellib/teststamp1 on joellib/teststamp ( stamp )

<psuedo-code>
 /free
    myStamp = %timestamp( myDate );
 /end-free
c/exec sql
c+ select someFields from myFile
c+   where <<someCondition>>
c+   order by myStamp
c/end-exec
</psuedo-code>

Joel
http://www.rpgnext.com