Re: Need help with this function

I can not find my PL/SQL book so I am not 100% sure, but you query looks wrong.
It looks like it is aliasing the workshop table to the word JOIN. It then sees the word MODULE and does not know what to do. It is looking for either a ',' for another table or a WHERE, GROUP BY or ORDER BY clause.

The query should look something like this SELECT workshop_num
FROM workshop,
Module
WHERE workshop.module_num = module.module_num  and work_start_date = (select min(work_start_date) from workshop where module_num=workshop.module_num AND sysdate <= workshop_start_date)

I am assuming the rest of your query is correct.

I make no promises, this is just what it looks like to me. Marc G. Parkinson
Senior Oracle Engineer.

"Barry Warner" <bwarner_at_blueyonder.co.uk> wrote in message news:RlPv9.8308$k6.3464_at_news-binary.blueyonder.co.uk...
> Create a function - Get_Workshop - that will verify that there is an
> available workshop for a particular module that a student can be invited
to.
> This function should compare the current system date to the date each
future
> workshop for that module is scheduled. It should return the workshop
number
> for the first available workshop or NULL if there are no available
> workshops.

>
> This is what I've got so far
>
> CREATE function get_workshop (workshop_num number, workshop_start_date
date,
> module_num number)
> 2 return number is workshop_num number;
> 3 BEGIN
> 4 SELECT workshop_num FROM
> 5 workshop JOIN Module ON
> 6 workshop.module_num = module.module_num
> 7 WHERE module.module_num = workshop.module_num
> 8 and work_start_date = (select min(work_start_date) from workshop where
> module_num=workshop.module_num AND sysdate <= workshop_start_date)
> 9 else return null;
> 10 end;
> 11 /
>
> I'm getting a compile error at line 5 - encountered MODULE when expecting
,
> or ;
> So I'm guessing that the join is wrong maybe. I also need help with the
> 'else return null' statement. I'm thinking that else isn't the right
> keyword as there is no IF statement.
>
> Thanks
>
>
>
Received on Wed Oct 30 2002 - 21:47:19 CST