4 Replies Latest reply: Nov 25, 2012 6:47 PM by 976079 RSS

    how to run my PL/SQL function?

    976079
      I have 4 table

      1st is EMPLOYEE(E#, ENAME), Primary Key is E#

      2nd is DRIVER(E#,L#), Primary Key is E# ref EMPLOYEE

      3rd is TRIP(T#,L#), Primary Key is T#, foreign key L# from DRIVER

      4th is TRIPPT(T#,PT#) Primary Key is T# from TRIP

      I have create a Function that finds the length(total number of pt#) of the longest point perform by the driver
      create or replace function LONGPT (DL# in TRIP.L#%type)
         return TRIPPT.PT#%type
      IS
         TRIPPT#   TRIPPT.PT#%type;
      begin
         select max (PT#)
           into TRIPPT# 
           from TRIPPT
          where T# in (select T#
                         from TRIP
                        where L# = DL#);
      
         return nvl (TRIPPT#, 0);
      end LONGPT;
      / 
      How can i do a select query to display the NAME of the employee, LONGPT. The Driver name that perform no trip point need to be in the query as well.

      I have try
       
      SELECT DRIVER.L# AS License_No, LONGTPT(TRIP.L#) AS "LONGEST POINT" 
      FROM DRIVER 
         LEFT OUTER JOIN TRIP on DRIVER.L# = TRIP.L#;
      This only query the L# and the LONGEST Point.
      Could anyone guide me how to do the select statement on display the ENAME and with my function LONGPT.

      Edited by: user4757127 on Nov 25, 2012 10:11 AM

      Edited by: user4757127 on Nov 25, 2012 10:17 AM

      Edited by: user4757127 on Nov 25, 2012 10:18 AM
        • 1. Re: how to run my PL/SQL function?
          damorgan
          1. NAME is a reserved word
          2. Please read the FAQ and learn how to post listings using
           tags so what you post can be read.
          
          Thank you.                                                                                                                                                                                                                                                                                                
          • 2. Re: how to run my PL/SQL function?
            976079
            I'm new to post qns here thanks for the help.
            • 3. Re: how to run my PL/SQL function?
              Frank Kulash
              Hi,

              Welcome to the forum!
              user4757127 wrote:
              ... How can i do a select query to display the NAME of the employee, LONGPT. The Driver name that perform no trip point need to be in the query as well.
              I'm not sure what you mean by "the employee, LONGPT".

              My best guess is that you need to join the employee table into your current query:
              SELECT  driver.l#          AS License_No
              ,     employee.ename                              -- *****  NEW  *****
              ,     longtpt (trip.l#)      AS "LONGEST POINT" 
              FROM           driver 
              JOIN          employee     ON   driver.e#     = employee.e#     -- *****  NEW  *****
              LEFT OUTER JOIN trip          ON   driver.l#     = trip.l#
              ;
              Again, this is just a guess, and guessing isn't always the best way to solve problems.


               

              I hope this answers your question.
              If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
              Explain, using specific examples, how you get those results from that data.
              Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              • 4. Re: how to run my PL/SQL function?
                976079
                Thanks for the help. it works.