Re: Sorting in SQR

[Steve Hall <srh@CONVOY.COM>]

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