SQL Only returns false

[hannon565]

This is old college assignment am doing to sharpen my sql skills, its cat test centre and this funtion is checking if the mechanic is available on certain date which is input from user in the anonymous block. the problem i m having durning testing is that the function result will only return false and the data inputs are correct

create or replace
FUNCTION MECH_AVAILABLE(mech_id in MECHANIC.MECHANIC_ID%TYPE, 
todays_date in UNAVAILABILITY.UNAVAILABLE_DATE%TYPE)
return boolean
is
temp UNAVAILABILITY.UNAVAILABLE_DATE%TYPE;

begin
select unavailable_date
into temp
from unavailability
where mechanic_id = mech_id;

if todays_date = temp
then
RETURN true;
else
return false;
end if;
end mech_available;

[BitBlt]

Not sure what dialect of SQL you're using, but the one I know says that using SELECT...INTO... creates a table, not a variable assignment. perhaps you should try:

select temp = unavailable_date
from unavailability
where mechanic_id = mech_id;

May work, may not...worth a try?

[pratik_garg]

Hi hannon565,

In select ... into ... what happen if it return more then one row??
SELECT INTO raised a TOO_MANY_ROWS exception.
that's why it will return false value which is default for boolean data type...

if you are sure that in your database you have only one row for a perticular MECHANIC in unavailability table then you have to do some modification in your function---
This is date formate and you are checking without any formatation..
we are giving today_date in parameter so it would not having HHMISS (time portion)
may be it is a problem in your function cause for mismatching.....

do one thing modify your code as you are checking todays_date with temp in if condition to_char(todays_date,'YYYYMMDD') = to_char(temp,'YYYYMMDD')

why you are taking unavailable_date in temp variable?
you can create function as follow --

create or replace
FUNCTION MECH_AVAILABLE(mech_id in MECHANIC.MECHANIC_ID%TYPE, 
todays_date in UNAVAILABILITY.UNAVAILABLE_DATE%TYPE)
return boolean
is
temp_count number;

begin
select count(*) into temp_count from unavailability
where mechanic_id = mech_id
and  to_char(unavailable_date,'DDMMYYYY')= to_char(todays_date,'DDMMYYYY');

if temp_count <> 0 then
RETURN true;
else
return false;
end if;
end mech_available;

If you have today_date value for that mech_id then this function will give true value as return....

please let me know if you need any clarification or help on above function...

Make sure that the date unavailability.unavailable_date has no time component.
Make sure that the parameter todays_date has no time component.
Then, you won't need the TO_CHARs in the function.

Also... Trunc (date) will strip off the time.

[pratik_garg]

Good thought hfx642,

But there are so many if ... else ... make sure in your assumption/suggestion.
For safer side i told to use To_Char().
any ways it is a function taking about same time as taken by Trunc(),

so @hannon565 you can use to_char function also in your function without any problem.
even in industries generally you need to work with To_Char function only because we can assume that there is no time stamp present in given date field,

But think about this -- if you have time values also with your date field then ????

then it is good to learn use of To_char function in your program..