RE: Sorting

Creating a permanent INDEX may be out via sql because:
CREATE TABLE ROB/ERIC (BIGCHAR CHAR (100 ) NOT NULL WITH DEFAULT)
CREATE INDEX ROB/ericl1 ON ROB/ERIC (substr(BIGCHAR,2,5))
Token ( was not valid. Valid tokens: ) ,. 

But you can still order on-the-fly
select * from eric 
order by substr(bigchar,2,5)

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/30/2003 04:31 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'Midrange Systems Technical Discussion'" 
<midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        RE: Sorting


SQL has pretty much been ruled out because:

1) the source data is non-described (flat file)
2) it's not possible to create an index on a derived field (substring)

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Jim Franz [mailto:franz400@xxxxxxxxxxxx]
Sent: Wednesday, April 30, 2003 4:24 PM
To: Midrange Systems Technical Discussion
Subject: Re: Sorting


you can substring in sql selects.
jim

----- Original Message -----
From: "Metz, Zak" <Zak_Metz@xxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, April 30, 2003 4:42 PM
Subject: RE: Sorting


> There I go mixing up DDS LFs with SQL views...but they look the same
> when I WRKOBJ! Yeah, it is indeed a "flat" file. So that pretty much
> eliminates SQL, except if I can deal with duplicating all the data to a
> second file, in which case I can do all the substringing I need. OPNQRYF
> is starting to look better, and yeah, my only problem with that is
> getting all the quotes straight, but I've managed before. QMQRY seems to
> have the advantage of being able to write over the same PF, as does the
> QLGSORT API, the most recent suggestion from Tom.
>
> Thanks for helping me hash this out, Eric.
>
> > -----Original Message-----
> > From: DeLong, Eric
> >
> > Uh, I don't think a view will work for the KEYFILE part....
> > That parm refers
> > to the access path to for the reordering of the PF data.  A
> > view by itself
> > has no access path, and I don't think SQL allows you to use
> > derived fields
> > as indexes..... :(
> NOTICE: This E-mail may contain confidential information. If you are not
> the addressee or the intended recipient please do not read this E-mail
> and please immediately delete this e-mail message and any attachments
> from your workstation or network mail system. If you are the addressee
> or the intended recipient and you save or print a copy of this E-mail,
> please place it in an appropriate file, depending on whether
> confidential information is contained in the message.
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.