Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: How to order by a lookup ?


    I have a problem using developer in oracle 9i. I want to order a query by a lookup field, but I don't how to do it. If I put the name of the field to be ordered, the form compiles but I get an error in runtime. I know that field doesn't exist in the table, but I can't put two table in the "Query Data Source Name" property of the "Data Block".

    Thank you.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: How to order by a lookup ?

    You can set the ORDER BY clause to this:

    (SELECT lookup_name FROM lookup_table WHERE lookup_id = base_table.lookup_id)

    e.g. in SQL:

    SQL> select ename, deptno from emp
    2 order by (select dname from dept where dept.deptno = emp.deptno);

    ---------- ----------
    CLARK 10
    KING 10
    MILLER 10
    SMITH 20
    ADAMS 20
    FORD 20
    SCOTT 20
    JONES 20
    ALLEN 30
    BLAKE 30
    MARTIN 30
    JAMES 30
    TURNER 30
    WARD 30

    (By an unfortunate coincidence, departments 10-30 sort the same whether by deptno or dname so you can't see the difference between this and "ORDER BY deptno"!)

  3. #3
    Join Date
    Jan 2004
    Hey ! It works !

    Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts