Re: order by problem

[Felix Rodriguez <rodriguez@UHAVAX.HARTFORD.EDU>]

At 10:50 AM 10/18/96, you wrote:
>Hi again. Okay, the problem is this: I have two fields, A and B. Both
>fields hold the same kind of data (a 9-digit number). Most of the time I
>wish to order by field A, but when field B is filled in, I wish to
>include it in the ordering. For example:
>
>        Field A         Field B
>        -------         -------
>          1             empty
>          4               5
>          7               6
>          9             empty
>
>I would like the final ordering to be 1, 5, 6, 9. Is this possible?
>
>Thanks in advance,
>        Adem
If you are using Oracle you can use the Decode Statement to do your order by.

Begin-Select
Decode(FieldB,NULL,FieldA,FieldB) SortField &SortField
>From FieldTable
OrderBy SortField
End-Select

If FieldB is Null then only the value of FieldA will will appear in the column.
If FieldB is Not Null then FieldB will appear in the column. Finally the
OrderBy Clause will sort by the Value of the Decode Column. I hope this
Helps.

Felix Rodriguez
Senior Systems Analyst
University of Hartford
rodriguez@uhavax.hartford.edu
http://uhavax.hartford.edu/~rodriguez