Author Topic: Need help for the following question encountered...  (Read 2491 times)

akihabara85

  • Newbie
  • *
  • Posts: 1
    • View Profile
Need help for the following question encountered...
« on: November 29, 2009, 01:17:30 AM »
Hi all, im new in this forum.. I googled so long but is not able tofind any help for my assignment.. The question is as follow...

Question 1: I am required to implement a stored PL/SQL functionLONGTRIP(DLNUM) that finds the length( the total number of legs) of thelongest trip performed by a driver identified by a driving licensenumber( L# attribute in table DRIVER and parameter DLNUM parameter inthe function). Remember about the drivers that performed no trips; thatis to say, do not ignore drivers that do not performed any trip.
Usea stored function LONGTRIP in SELECT statement to list the names of alldrivers together with the length of the longest trip performed by eachdriver.

Appreciate very much if anyone would be able to guide me through whatam i require to do.. Currently, im only about to come up with the belowcode but there is problems...

CREATE OR REPLACE FUNCTION LONGTRIP(DLNUM NUMBER) RETURN NUMBER
IS
   MAX_LEG TRIP.T#%TYPE;
BEGIN 
      SELECT COUNT(T#)
          INTO MAX_LEG
      FROM TRIP
      HAVING COUNT(T#) = (SELECT MAX(T.TOTAL_TRIP)
      FROM(SELECT COUNT(T#) AS TOTAL_TRIP FROM TRIP GROUP BY L#)T);
 
  RETURN MAX_LEG;
END LONGTRIP;
/

When i execute the follow command:

Select name, longtrip(1) from dual;

The output i get is different from what i am required to do... Anyone can kindly guide me along?

newbielink:http://dbaforums.org/oracle/index.php?act=attach&type=post&id=329 [nonactive]
My sample databases can be found in the above links..