Thanks for the quick response...

In the real thing..I am returning v_meeting_id. But, in testing...I was just
trying to see why it wouldn't return anything.

In the original query part I was actually selecting NVL(a.meeting_id,0) into v_meeting_id to insure a non-null value was selected. In desperation, I
just tried hard coding a return value to see if it would return anything.

The was it is with the hard coded value, you would expect it to return the
value 2 no matter what happened in the query, right? Whether a v_meeting_id
was null, or if 0 with the NVL part in the statement....well, that is what is
puzzling me.

Even with the 2 as a hard coded return value...having nothing to do with
the select into statement...it only returns the 2 IF a null value is not
selected into v_meeting_id...if the query doesn't put a null into the variable,
then the 2 is returned...this doesn't make sense to me...

Any further ideas?

TIA,

Kelly

On Thu, Mar 28, 2002 at 02:43:18PM -0500, after pounding the keys randomly, [Email Address Removed] came up with....
>
> Try putting RETURN v_meeting_id. For testing purposes make sure your query
> returns a non-null meeting id. I assume your where clause will always
> return
> 0 or 1 record otherwise an exception will be raised.
>
> Rick
>
> <snip>
> Here is an example:
>
> FUNCTION check_date (v_date IN DATE, v_room_id IN NUMBER) RETURN NUMBER IS
>
> v_meeting_id NUMBER;
>
> BEGIN
>
> SELECT a.meeting_id 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;
>
> If I do this, where there is a meeting on this date/time, in the room
> in the booked_meeting_room table...it will return the 2. But, if there
> is not a meeting_id for the date and room...it returns a null...even though
> the return value is not even involved with the query here....
>
> Any ideas?
>
> TIA,
>
> Kelly
>
------------------

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

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