Re: Sorting file based on variable string

[CRPence <CRPbottle@xxxxxxxxx>]

  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.