Code:
CREATE FUNCTION FN_APPCONSULTANTS(DOCIDPATIENTVISIT VARCHAR,ADMDATE DATE,ELS NUMBERIC)
RETURNS VARCHAR
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
DECLARE strAPPCONSULTANTS VARCHAR(2000);
DECLARE v_EMPNAME VARCHAR(100);
DECLARE GETAPPCONS CURSOR with return to client for select EMP.FIRSTNAME || ' ' || EMP.MIDDLENAME || ' ' || EMP.LASTNAME EMPNAME
from nmcfo.PATIENTAPPOINTMENTS PA, nmcfo.EMPLOYEES EMP,nmcfo.PATIENTVISITS PV
where PA.RESOURCECODE = EMP.EMPLOYEECODE AND PA.RESOURCEENTITYCODE = 'CONS' AND
(PA.APMNTDATETIME >= ADMDATE AND PA.APMNTDATETIME <= dateadd('dd',ELS, ADMDATE)) AND
PA.DOCIDPATIENTS IN
(SELECT PV.DOCIDPATIENTS FROM nmcfo.PATIENTVISITS PV
WHERE PV.DOCID = DOCIDPATIENTVISIT);
OPEN GETAPPCONS
LOOP
FETCH GETAPPCONS INTO v_EMPNAME
strAPPCONSULTANTS = v_EMPNAME ||' , '|| strAPPCONSULTANTS;
END LOOP;
RETURN (strAPPCONSULTANTS);
END
!
Thanks in advance
Anand