Re: ORDER BY

[Peter Morris <morrisp@KEA.LINCOLN.AC.NZ>]

I'd use CASE

eg
SELECT DISTINCT
XT.XLATLONGNAME
SCT.ACAD_PROG_PRIMARY
D.DESCRFORMAL
SCT.ACAD_LEVEL_BOT
SCT.EMPLID
SCT.ACAD_CAREER
PD.NAME
RO.RESIDENCY            &NZ_RESIDENCY
'   '                   &INTL_RESIDENCY
CASE SCT.ACAD_CAREER    WHEN 'RSCH' THEN 'A'
                        WHEN 'UGRD' THEN 'B'
                        WHEN 'PGRD' THEN 'C'
                        WHEN 'NONA' THEN 'D'
                        ELSE             'E'
                        END &CAREER_ORDER

FROM    PS_STDNT_CAR_TERM SCT,
        PS_RESIDENCY_OFF RO,
        PS_PERSONAL_DATA PD,
        PS_COMMUNICATION C,
        PS_DEGREE_TBL D,
        XLATTABLE XT
WHERE   RO.INSTITUTION    = SCT.INSTITUTION                     AND
        RO.EMPLID         = SCT.EMPLID                          AND
        RO.ACAD_CAREER    = SCT.ACAD_CAREER                     AND
        RO.EFFECTIVE_TERM = SCT.STRM                            AND
        PD.EMPLID         = SCT.EMPLID                          AND
        C.EMPLID          = SCT.EMPLID                          AND
        COMM_CONTEXT      = 'GENREG'                            AND
        COMM_DT           BETWEEN $From_Date AND $Thru_date     AND
        D.DEGREE          = SCT.ACAD_PROG_PRIMARY               AND
        SCT.STRM          = $Term                               AND
        D.EFF_STATUS      = 'A'                                 AND
        D.EFFDT           = (SELECT MAX(D1.EFFDT)
                             FROM   PS_DEGREE_TBL D1
                             WHERE  D.DEGREE = D1.DEGREE        AND
                                    D.EFFDT <= D1.EFFDT)        AND
        XT.FIELDNAME      = 'ACAD_CAREER'                       AND
        XT.FIELDVALUE     = SCT.ACAD_CAREER                     AND
        RO.RESIDENCY      IN ('AUS','NZL','NZP')

ORDER BY 10

Peter

> -----Original Message-----
> From: Discussion of SQR, Brio Technology's database reporting language
> [mailto:SQR-USERS@list.iex.net]On Behalf Of Chuck Harkness
> Sent: Thursday, 15 March 2001 12:18 pm
> To: SQR-USERS@list.iex.net
> Subject: ORDER BY
>
>
> Hello!
>   I am trying to use an order by clause on a char field, but the order
> doesn't come out quite like the customer wanted.  For example, I have the
> values: 1,1SL,1SM,1UP,100,197,198,199 and 1000.  The order by
> clause in SQR
> produces the following order:
>
> 1
> 100
> 197
> 198
> 199
> 1SL
> 1SM
> 1UP
> 1000
>
> I would like the order to be 1,1SL,1UP,100,197,198,199,100.  Does anyone
> have any suggestions?
>
> Thank you,
>   Chuck