Oracle Form - ORDER BY clause

From: <suisum_at_ecn.ab.ca>
Date: 22 Apr 99 00:01:19 GMT
Message-ID: <371e66cf.0_at_ecn.ab.ca>

[Quoted] [Quoted] I have the following tables. I want to get a list of movie rentals in [Quoted] movie title for a particular customer. I have created the following function in Oracle Form for sorting purpose:

  FUNCTION sort_movie_title_fn
    (p_tape_id NUMBER)
    RETURN VARCHAR2
  IS

    v_movie_title movies.title%TYPE := 'UNKNOWN';

  CURSOR cur_title_rec IS
    SELECT title

      FROM movies
     WHERE id = (SELECT movie_id FROM tapes WHERE id = p_tape_id);

  BEGIN
    OPEN cur_title_rec;
    FETCH cur_title_rec INTO v_movie_title;     CLOSE cur_title_rec;
    RETURN v_movie_title;
  END; [Quoted] However, when I put the following clause in the Rentals ORDER BY clause property, the form complains the invalid column name. I believe the error is the tape_id.

   sort_movie_title_fn (tape_id)

Please help.

---

Table definitions

---

   

SQL> desc tapes;

 Name                            Null?    Type


 ------------------------------- -------- ----

 ID                              NOT NULL NUMBER
 MOVIE_ID                                 NUMBER
 STATUS                                   CHAR(1)

SQL> desc movies;
 Name                            Null?    Type


 ------------------------------- -------- ----

 ID                              NOT NULL NUMBER
 TITLE                           NOT NULL VARCHAR2(30)

SQL> desc rentals;
 Name                            Null?    Type


 ------------------------------- -------- ----

 CUSTOMER_ID                     NOT NULL NUMBER
 TAPE_ID                         NOT NULL NUMBER

--
Best regards,

Received on Thu Apr 22 1999 - 02:01:19 CEST