Morning folks!
I am trying to find the next scheduled appointment date (Including any day after today) for a patient which may not be the maximum date for that person. I am trying to do this in Oracle Forms. My Query is working in SQL*Plus but is not working in Forms.
FUNCTION get_next_sched_date(P_PATIENT_ID in varchar2) RETURN DATE IS
v_next_scheduled_date patient_visit.target_date%TYPE;
BEGIN
select next_target_date into v_next_scheduled_date
from ( select v.*, max(target_date) over (partition by patient_id) max_target_date,
lead(target_date) over (partition by patient_id order by target_date) next_target_date
from patient_visit v)
where patient_id = P_PATIENT_ID
and next_target_date >= SYSDATE
and max_target_date > next_target_date;
return( v_next_scheduled_date );
EXCEPTION
when NO_DATA_FOUND then
return(NULL);
When I compile this in Oracle Forms, it gives me an Error.Encountered the symbol "(" when expecting one of the following: ,from.
I also noticed that Oracle Forms is not liking keywords such as LAG and LEAD. I am working on Oracle Forms 9i.
Any idea what I am doing wrong here? Thanks for listening to my Monday harping. :-)
Forms [32 Bit] Version 9.0.4.0.19 (Production)
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
Edited by: Roxyrollers on Aug 27, 2012 8:43 AM
Edited by: Roxyrollers on Aug 27, 2012 8:46 AM