Here is what I get when I run this...(I just rename the colume to 'joe' to
make it shorter named)

With a value that does put a value into the v_meeting_id variable:

SQL> select generic_structure.check_date(to_date('MAR 27, 2002 05:30 AM','MON DD, YYYY HH:MI AM'),200) joe from dual;

JOE
----------
2

Now, if I run the same thing with a room number change to one that does not
exist in the table being tested..and therefore puts a null into the variable,
the hardcoded return value of 2 is NOT returned here:

SQL> select generic_structure.check_date(to_date('MAR 27, 2002 05:30 AM','MON DD, YYYY HH:MI AM'),199) joe from dual;

JOE
----------


I don't understand this...

One more thing...I have done the function with the NVL and it does the same
thing. Here it is with the NVL in it:

FUNCTION check_date (v_date IN DATE, v_room_id IN NUMBER) RETURN NUMBER IS

v_meeting_id NUMBER;

BEGIN

SELECT NVL(a.meeting_id,0) INTO v_meeting_id from booked_meeting_room a, meeting b
WHERE v_date between b.begin_date and b.end_date
AND v_room_id = a.room_id
AND a.meeting_id = b.meeting_id;


RETURN 2;

END check_date;

I thought this would clear it up, but, it hasn't. I still don't understand,
if the parameters are fed correctly...this function SHOULD return 2 regardless
of what the select into puts into the variable...???

Thanks..please keep the ideas coming...

Kelly


On Thu, Mar 28, 2002 at 02:50:10PM -0500, after pounding the keys randomly, Alok.[Email Address Removed] came up with....
> How are you using this function, try calling like this:
> sql>select check_date (v_date IN DATE, v_room_id IN NUMBER) from dual;
>
> Regards,
> Alok Kumar
>

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

A computer lets you make more mistakes faster than any other invention,
With the possible exceptions of handguns and tequila.

------------------
Oracle LazyDBA home page