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



Given the characteristics of the scenario, the following script shows an expression that can be used for an ORDER BY clause to "sort" the results of the database flat file using the data which describes the "last name" data. The script uses a one-field SQL TABLE instead of a program described database file, and limits the field length for convenience; the difference should be effectively moot. For the given ORDER BY expression to function correctly, the assumptions stated must be true, whereby the "A2" prefixed "field" data must *not* be the first "field" *nor* the last "field" in a row. While not explicitly stated as a given for the data, the given ORDER BY expression assumes and thus requires that an "A2" prefixed "field" data must exist in every row; that if untrue, the expression would need to be revised.

<code>

create table qtemp/thefile (thefield char(44) for bit data)
; -- insert some data; "field" data separated by x'1C'
insert into qtemp/thefile values
( 'A1FirstJnk' concat x'1C' concat 'A2Last1Xyz' concat x'1C'
concat 'A3AddrStuf' concat x'1C' concat 'A4MoreAddr' ) /* 1234 */
, ( 'A1FirstA' concat x'1C' concat 'A2Last2' concat x'1C'
concat 'A3AddrStuf' concat x'1C' concat 'A4AL4' ) /* 1234 */
, ( 'A3Addr3' concat x'1C' concat 'A11st' concat x'1C'
concat 'A2Last3Lng' concat x'1C' concat 'A4XtraAddr' ) /* 3124 */
, ( 'A4AL4' concat x'1C' concat 'A3AL3' concat x'1C'
concat 'A2Last4X' concat x'1C' concat 'A1FirstNam' ) /* 4321 */
; -- x'1C' was not visible via my 5250 so show '_' instead:
select translate(thefield, '_', x'1C') from TheFile
order by 1 /* srtseq(*hex) */
; -- report for above SELECT follows:
....+....1....+....2....+....3....+....4....
TRANSLATE
A1FirstA_A2Last2_A3AddrStuf_A4AL4
A1FirstJnk_A2Last1Xyz_A3AddrStuf_A4MoreAddr
A3Addr3_A11st_A2Last3Lng_A4XtraAddr
A4AL4_A3AL3_A2Last4X_A1FirstNam
******** End of data ********
; -- expression for ORDER BY of "last name" "field" data:
select translate(thefield, '_', x'1C') from TheFile
order by
substr( TheField
, locate(x'1C' concat 'A2', TheField)+3
, locate(x'1C', TheField
, locate(x'1C' concat 'A2', TheField)+3) -1
) desc
; -- report for above SELECT follows:
....+....1....+....2....+....3....+....4....
TRANSLATE
A4AL4_A3AL3_A2Last4X_A1FirstNam
A3Addr3_A11st_A2Last3Lng_A4XtraAddr
A1FirstA_A2Last2_A3AddrStuf_A4AL4
A1FirstJnk_A2Last1Xyz_A3AddrStuf_A4MoreAddr
******** End of data ********
; -- To show\clarify the data being used for ordering:
select
substr( TheField
, locate(x'1C' concat 'A2', TheField)+3
, locate(x'1C', TheField
, locate(x'1C' concat 'A2', TheField)+3) -1
)
from TheFile
order by 1 desc
; -- report for above SELECT follows:
....+....1....+....2....+....3....+....4....
SUBSTR
Last4X
Last3Lng
Last2
Last1Xyz
******** End of data ********
;

</code>

Regards, Chuck

On 21-Jan-2012 14:31 , Vinay Gavankar wrote:
<<SNIP>> the key I want to sort with need not be the second field
separated by x'1C'. It is not the first, it is not the last, but
could be anywhere in between. The only uniqueness is that it starts
with x'1C'A2 and ends with another x'1C', which is the beginning of
the next field. (A2 is the field tag that identifies the field I
need).

<<SNIP>>

<<SNIP an intermediate reply using STMF>>

On 21-Jan-2012 11:10 , Vinay Gavankar wrote:
I have a flat file, which has fields delimited by field
separators and a 2 character field tag. The actual fields are
variable length, so not in a fixed position.

For example, my field separator is hex '1C' and the field tags
are A1 thru A6. Tag
A1 denotes First name,
A2 Last name,
A3 Address Line 1,
A4 Address Line 2 etc.

I want to sort by last name, which will be a variable length
field, and could occur anywhere in the record (as the First name
is also of variable length). It will always be enclosed between
hex '1C'A2 and hex '1C' (it will never be the first or the last
field in the record).

What would be the best way of doing this? (The only solution I
have is to write a program to create an intermediate file, with
the Last Name as a separate field).

The file is actually 4000 bytes and has about 18 million records,
but this would be a one time effort.


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.