Re: Help!! order by on non base table items

Mark wrote:
>
> Hi there...
>
> Anyone got any clever methods of allowing forms 4.5 to include a non
> base table item in a base table block in the ORDER BY clause?
>
> I've got a base table which brings back a foreign key (Fk) field. The
> Fk is used in a Post query trigger to look up the description for this
> field. Unfortunatley, the module specification requires that the query
> results are ordered by the fk description followed by items in the
> base table.
>
> I'm trying to achieve this without using a view (if thats possible)
>
> Cheers
>
> MarkHow to order by a non-base table column in Oracle forms.

   In the following example, we have two masters, EMPLOYEE and    PROJECT.
   An EMPLOYEE may be a member of one or more PROJECTs.    A PROJECT may be having one or more EMPLOYEEs.

   An EMPLOYEE is uniquely identified by a SYSTEM ASSIGNED number.    A PROJECT is uniquely identified by a SYSTEM ASSIGNED number.

   Thus we need an intersection entity EMPLOYEE_PROJECT.

   An EMPLOYEE may be a member of one and only one EMPLOYEE_PROJECT.    An EMPLOYEE_PROJECT must consist of one or more EMPLOYEEs.

   A PROJECT may be contained in one and only one EMPLOYEE_PROJECT.    An EMPLOYEE_PROJECT must be made of one or more PROJECTs.

   EMPLOYEE >-----------< PROJECT

   This leads to
   EMPLOYEE >------------ EMPLOYEE_PROJECT    PROJECT >------------ EMPLOYEE_PROJECT    Table Structure of EMPLOYEE

   EMPLOYEE_NUMBR     NUMBER       (SYSTEM ASSIGNED - PRIMARY KEY)
   EMPLOYEE_NAME      VARCHAR2(60)
   EMPLOYEE_DOB       DATE
   .

   Table Structure of PROJECT

   PROJECT_NUMBR      NUMBER       (SYSTEM ASSIGNED - PRIMARY KEY)
   PROJECT_NAME       VARCHAR2(60)
   PROJECT_START_DATE DATE
   .

   Because EMPLOYEE_PROJECT's primary key will be made of foreign    keys coming from EMPLOYEE and PROJECT, it will look like

   Table Structure of EMPLOYEE_PROJECT

   EMPLOYEE_NUMBR     NUMBER          (COMPOSITE PRIMARY KEY)
   PROJECT_NUMBR      NUMBER          (COMPOSITE PRIMARY KEY)

   When designing the screen to maintain the EMPLOYEE_PROJECT,    the screen will be look like

   EMPLOYEE

   PROJECT_NAME               (Fetched as a Lookup)
   PROJECT_NAME               (Fetched as a Lookup)
   PROJECT_NAME               (Fetched as a Lookup)

   Oracle Forms cannot order by non-base table column.

   To order by PROJECT_NAME (non-base table column) do the    following:

1. Create a database function with IN parameters as PROJECT_NUMBR returning PROJECT_NAME.

     CREATE OR REPLACE FUNCTION Get_project_name(p_project_numbr
                                IN NUMBER)
     RETURN
     VARCHAR2 as

     CURSOR c1 (l_project_numbr IN NUMBER) IS
            SELECT   project_name
            FROM     project
            WHERE    project_numbr = l_project_numbr;
     l_project_name project.project_name%type;

     BEGIN
        OPEN c1(p_project_numbr);
        FETCH c1
        INTO  l_project_name;

        IF c1%FOUND THEN
           close c1;
           return l_project_name;
        ELSE
           close c1;
           return NULL;
        END IF;
     END;
     /

     2. In the ORDER BY clause of EMPLOYEE_PROJECT use this function
        ORDER BY get_project_name(project_numbr)

     3. Query the block and HEY PRESTO ! you can see the records
        ordered by non-base table column PROJECT_NAME.