Re: SQL statement question...

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 11 May 1999 09:24:59 GMT
Message-ID: <3737f4b6.10297822_at_news.siol.net>

On Tue, 11 May 1999 07:34:37 -0700, Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:

>Hello all,
> I have a table with column A of number data type.
> I want to select column A and convert A values to char type as
>follows:
>
> select to_char(A) from table order by A
>
> However, I find that the column A cannot display in order. This is
>because after the function to_char, the order is in character, not in
>number. So I get the following results:
>
> 1,11,12,2,22,23 ... not 1,2,11,22,23...

This can happen only if you put an alias to to_char(A) and you reference this alias in you order by. See the examples below.

> How can I get the above results (order by A numeric value and
>convert to character data type) by using a single SQL statement?

SCOTT_at_PO73> select * from test_table;

         A

---

         1
        10
         2
       111
        21

SCOTT_at_PO73> select a from test_table order by a;

         A

---

         1
         2
        10
        21
       111

SCOTT_at_PO73> select to_char(a) from test_table order by a;

TO_CHAR(A)

---

1
2
10
21
111

SCOTT_at_PO73> select to_char(a) a from test_table order by a;

A

---

1
10
111
2
21

SCOTT_at_PO73> select to_char(a) a from test_table order by test_table.a;

A

---

1
2
10
21
111

>Thanks,
>Jimmy

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue May 11 1999 - 11:24:59 CEST