Re: Forms 4.5 - Using Order By to order by non base table field

You can create a database stored function that decodes the non base table field id and use this to order the results of the select statement

ie ,

   create or replace function get_name (p_rep_id number) return

        char is

  cursor cu_get_name is
    select report_name
    from ref_report_definitions
    where report_id = p_rep_id;

  l_val varchar2(30);

begin

   open cu_get_name;

   fetch cu_get_name into l_val;
   close cu_get_name;
   return l_val;

end;

use this function in an select statement ie ,

  select resc.report_id
  from report_schedules resc
  order by get_name ( resc.report_id )

this will return the IDs in the alphabetcal order of the report_names

Hope this helps
-Mark

On Thu, 29 May 1997 19:41:54 +0100, Eric Hartzenberg <eric_at_erichome.demon.co.uk> wrote:

>In article <01bc6bea$41fc3be0$4bd8cbc0_at_bobby-bakshi>, Bobby
><bobbyb_at_datacom.co.nz> writes
>>Hi,
>>This is rgds. to using Forms 4.5
>>I am trying to use a base table block and order by a field which is a non
>>base table field on the same block. I am trying this using the default
>>ORDER BY CLAUSE of the block. The block is being populated with an 'Execute
>>query' and the non base table field is being populated on the post query.
>>
>>Is there any way that I can use to order the block by the non base table
>>field. The other option is to include the field on the base table and use
>>it in the order by clause. But the database design does not let me add this
>>field to the table.
>>
>>I hope someone out ther can help me.
>>
>>Email - bobbyb_at_datacom.co.nz
>>
>>Bobby Bakshi.
>You probably need to build a VIEW (joining the two tables). This gives
>you problems with insert/update/delete, so you will then need to do non-
>base-table DML on the original table.
> ________________________________________________________________
>| ~~~~~~~~~~~~~~~~~~~~ | ERIC HARTZENBERG | ~~~~~~~~~~~~~~~~~~~ |
>| Email : eric_at_erichome.demon.co.uk Compuserve : 100073,21 |
>| Homepage : www.erichome.demon.co.uk |
>|________________________________________________________________|
Received on Fri May 30 1997 - 00:00:00 CEST