Re: Sorting in SQR

[Don Mellen <donm@ONTKO.COM>]

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"