SV: Sorting in SQR

[Frank Severinsson <franks@ALGONET.SE>]

Hi Don,

It seems that your solution is the one i´m looking for.
I have tried it in SQLTalk and it works very nicely.

Thanks
Frank

----------
> Från: Don Mellen <donm@ONTKO.COM>
> Till: Multiple recipients of list SQR-USERS <SQR-USERS@LIST.IEX.NET>
> Ämne: Re: Sorting in SQR
> Datum:  den 10 juli 1998 18:54
>
> Whereas I enjoy puzzle solving.  While it's a little ugly, in Oracle, you
> can use "DECODE" to order by the third segment doing something like...
>
>   select
>   text
>   from table
>   order by
>     decode(instr(text,'-',1,2),0,'-',
>       substr(
>              substr(text, instr(text,'-',1,2) + 1)
>              ,1
>              ,decode(
>                      instr(text,'-',1,3)
>                      ,0,300
>                      ,instr(text,'-',1,3) - instr(text,'-',1,2) - 1
>                     )
>             )
>           )
>
> Of course, if the third segments are the same on two records, you will
> have to decide if you want to order by the second and first segments, or
> just let them fall where they may.  Dedode is an "if-then-else" kind of
> function.
>
> HTH,
> Don
>
>
> On Fri, 10 Jul 1998, Steve Hall wrote:
>
> > One way that I, a lazy guy, prefer is to create a temp table, insert
the strings in the temp table, then extract
> > using sql like
> > ....
> > begin-select
> > seg1
> > seg3
> > seg3
> >  from glaccount
> > ORDER BY SEG3
> >
> > -----Original Message-----
> > From:   Frank Severinsson [SMTP:franks@ALGONET.SE]
> > Sent:   Friday, July 10, 1998 5:10 AM
> > To:     Multiple recipients of list SQR-USERS
> > Subject:        Sorting in SQR
> >
> > I have a problem with sorting in SQR/SQL.
> >
> > I this sounds crazy but I have a client that have a GLACCOUNT-string
with
> > different lengths and different number of segments
> > and they want to sort their transactions on segment 3 (if there is
one).
> > So, I wonder, is it possible to sort on a part of a column and the only
> > thing I know about the part, is that it begins after the second '-'.
> > Example:
> >
> > The GLACCOUNT-strings could look like this:
> >
> > B-1234-43546-12
> > B-1245
> > C
> > C-343-5678
> > C-768-767898-34545
> > B
> > B-4345
> > B-6567-30
> > C-5456-30
> >
> > And I want the result to be like this:
> >
> > B
> > B-1245
> > B-4345
> > C
> > B-5456-30
> > B-6567-30
> > B-1234-43546-12
> > B-343-5678
> > B-768-767898-34545
> >
> > I have tried the SQL-functions SUBSTR(), INST(), LPAD(), RPAD(),
LTRIM(),
> > RTRIM() in all kind of ways but without success.....
> >
> > Does anyone have any idea ?
> >
> > Frank Severinsson
> > PSDI Norden
> >
>
> -----------------------------------------------------------------------
> Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
> donm@ontko.com |  "In the beginning, there was nothing, which exploded"