Need help with this function

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 30 Oct 2002 12:25:07 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702DBA3F3@lnewton.leeds.lfs.co.uk>

Barry,

You are mixing different joins - you have the following :

CREATE FUNCTION get_workshop (workshop_num NUMBER, workshop_start_date DATE, module_num NUMBER)
RETURN NUMBER IS workshop_num NUMBER;
BEGIN
    SELECT workshop_num
    FROM workshop
    JOIN MODULE ON workshop.module_num = MODULE.module_num     WHERE MODULE.module_num = workshop.module_num     AND work_start_date = (SELECT MIN(work_start_date)

                           FROM   workshop 
                           WHERE  module_num=workshop.module_num 
                           AND SYSDATE <= workshop_start_date)

 ELSE RETURN NULL;
END;
/

Either have the JOIN ....... or the WHERE ......... but not both.

Next up, in functions you need to 'select into' and then return that. The following is a very simple example :

CREATE FUNCTION get_workshop (workshop_num NUMBER, workshop_start_date DATE, module_num NUMBER)
RETURN NUMBER IS    workshop_num NUMBER;
   ErrorReturn CONSTANT NUMBER := -9999; BEGIN
    SELECT workshop_num -- I'm assuming that this is the column on the workshop table ???

    INTO workshop_num
    FROM workshop
    WHERE MODULE.module_num = workshop.module_num     AND work_start_date = (SELECT MIN(work_start_date)

                           FROM   workshop 
                           WHERE  module_num=workshop.module_num 
                           AND SYSDATE <= workshop_start_date);

    RETURN workshop_num;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN -99999;
END;
/

So you must select a column into a variable from a table as shown above.

The exception handler is a MUST, and traps the tiomes when there is no data in the table matching your query criteria, however, your function returns NUMBER, and you are returning NULL - it isn't a good idea to mix and match return types, so define a suitbal value to return in the event of an error - say '-9999', and have the caller deal with it. Example :

CREATE FUNCTION get_workshop (workshop_num NUMBER, workshop_start_date DATE, module_num NUMBER)
RETURN NUMBER IS    workshop_num NUMBER;
   ErrorReturn CONSTANT NUMBER := -9999; BEGIN
....
....
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN ErrorReturn;
END;
/

Have fun !

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: Barry Warner [mailto:bwarner_at_blueyonder.co.uk] Posted At: Wednesday, October 30, 2002 11:27 AM Posted To: server
Conversation: Need help with this function Subject: Need help with this function

Create a function - Get_Workshop - <SNIP> Received on Wed Oct 30 2002 - 06:25:07 CST