Пользователь
Регистрация: 12.02.2016
Сообщений: 16
|
Ошибка PLS-00103
Доброго времени суток. Пытаюсь скомпилировать функцию, но выдается ошибка:
Код:
CREATE OR REPLACE
FUNCTION policy (state INTEGER DEFAULT NULL, client_type INTEGER)
RETURN typepolicylist pipelined AS
BEGIN
IF state IS NULL THEN
IF client_type = 1 THEN
FOR i IN (SELECT PLC.POLICY_ID, LNG.NAME AS POLICY_STATE, PLC.POLICY_NO, PLC.INSR_BEGIN, PLC.INSR_END, INS.NAME AS PRODUCT,
'Страхователь' AS MAN_TYPE, OFFICE.NAME AS DEPARTMENT, PPSTAFF.NAME AS STAFF, CL.MAN_ID, PLC.POLICY_STATE
FROM insis_cnt_v1.policy PLC
join INSIS_CNT_V1.p_clients cl ON plc.client_id = cl.client_id
join insis_cnt_v1.p_people pp ON pp.man_id = cl.man_id
JOIN INSIS_CNT_V1.cfg_nom_language_table LNG ON LNG.TABLE_NAME = 'HST_POLICY_STATE' AND LNG.ID = PLC.POLICY_STATE AND LNG.LANGUAGE = 'RUSSIAN'
JOIN INSIS_CNT_V1.HT_INSR_TYPE INS ON INS.ID = PLC.INSR_TYPE
JOIN INSIS_CNT_V1.cnt_offices OFFICE ON OFFICE.OFFICE_ID = PLC.OFFICE_ID
JOIN INSIS_CNT_V1.P_STAFF STAFF ON STAFF.STAFF_ID = PLC.STAFF_ID
JOIN INSIS_CNT_V1.P_PEOPLE PPSTAFF ON STAFF.MAN_ID = PPSTAFF.MAN_ID)
LOOP
pipe ROW (typepolicy(I.POLICY_ID, I.POLICY_STATE, I.POLICY_NO, I.INSR_BEGIN, I.INSR_END, I.PRODUCT,
I.MAN_TYPE, I.DEPARTMENT, I.STAFF, I.MAN_ID, I.POLICY_STATE));
END LOOP;
ELSE IF client_type = 2 THEN
FOR i IN (SELECT PLC.POLICY_ID, LNG.NAME AS POLICY_STATE, PLC.POLICY_NO, PLC.INSR_BEGIN, PLC.INSR_END, INS.NAME AS PRODUCT,
'Застрахованный водитель' AS MAN_TYPE, OFFICE.NAME AS DEPARTMENT, PPSTAFF.NAME AS STAFF, CL.MAN_ID, PLC.POLICY_STATE
FROM insis_cnt_v1.policy plc
join INSIS_CNT_V1.INSURED_OBJECT_DRIVERS cl ON PLC.POLICY_ID = CL.POLICY_ID
join insis_cnt_v1.p_people pp ON pp.man_id = cl.man_id
JOIN INSIS_CNT_V1.cfg_nom_language_table LNG ON LNG.TABLE_NAME = 'HST_POLICY_STATE' AND LNG.ID = PLC.POLICY_STATE AND LNG.LANGUAGE = 'RUSSIAN'
JOIN INSIS_CNT_V1.HT_INSR_TYPE INS ON INS.ID = PLC.INSR_TYPE
JOIN INSIS_CNT_V1.cnt_offices OFFICE ON OFFICE.OFFICE_ID = PLC.OFFICE_ID
JOIN INSIS_CNT_V1.P_STAFF STAFF ON STAFF.STAFF_ID = PLC.STAFF_ID
JOIN INSIS_CNT_V1.P_PEOPLE PPSTAFF ON STAFF.MAN_ID = PPSTAFF.MAN_ID)
LOOP
pipe ROW (I.POLICY_ID, I.POLICY_STATE, I.POLICY_NO, I.INSR_BEGIN, I.INSR_END, I.PRODUCT,
I.MAN_TYPE, I.DEPARTMENT, I.STAFF, I.MAN_ID, I.POLICY_STATE));
END LOOP;
ELSE IF client_type = 3 THEN
FOR i IN (SELECT plc.policy_id, LNG.NAME AS POLICY_STATE, PLC.POLICY_NO, PLC.INSR_BEGIN, PLC.INSR_END, INS.NAME AS PRODUCT,
'Выгодопреобретатель' AS MAN_TYPE, OFFICE.NAME AS DEPARTMENT, PPSTAFF.NAME AS STAFF, cl.man_id, plc.policy_state
FROM insis_cnt_v1.policy plc
LEFT JOIN INSIS_CNT_V1.INSURED_OBJECT OBJ ON OBJ.POLICY_ID = PLC.POLICY_ID
join INSIS_CNT_V1.O_OBJECT_PERSONS cl ON OBJ.OBJECT_ID = CL.OBJECT_ID
join insis_cnt_v1.p_people pp ON pp.man_id = cl.man_id
JOIN INSIS_CNT_V1.cfg_nom_language_table LNG ON LNG.TABLE_NAME = 'HST_POLICY_STATE' AND LNG.ID = PLC.POLICY_STATE AND LNG.LANGUAGE = 'RUSSIAN'
JOIN INSIS_CNT_V1.HT_INSR_TYPE INS ON INS.ID = PLC.INSR_TYPE
JOIN INSIS_CNT_V1.cnt_offices OFFICE ON OFFICE.OFFICE_ID = PLC.OFFICE_ID
JOIN INSIS_CNT_V1.P_STAFF STAFF ON STAFF.STAFF_ID = PLC.STAFF_ID
JOIN INSIS_CNT_V1.P_PEOPLE PPSTAFF ON STAFF.MAN_ID = PPSTAFF.MAN_ID)
LOOP
pipe ROW (typepolicy(I.POLICY_ID, I.POLICY_STATE, I.POLICY_NO, I.INSR_BEGIN, I.INSR_END, I.PRODUCT,
I.MAN_TYPE, I.DEPARTMENT, I.STAFF, I.MAN_ID, I.POLICY_STATE));
END LOOP;
ELSE IF client_type = 4 THEN
FOR i IN (SELECT plc.policy_id, LNG.NAME AS POLICY_STATE, PLC.POLICY_NO, PLC.INSR_BEGIN, PLC.INSR_END, INS.NAME AS PRODUCT,
'Застрахованный' AS MAN_TYPE, OFFICE.NAME AS DEPARTMENT, PPSTAFF.NAME AS STAFF, cl.man_id, plc.policy_state
FROM insis_cnt_v1.policy plc
LEFT JOIN INSIS_CNT_V1.INSURED_OBJECT OBJ ON OBJ.POLICY_ID = PLC.POLICY_ID
join INSIS_CNT_V1.O_ACCINSURED cl ON OBJ.OBJECT_ID = CL.OBJECT_ID
join insis_cnt_v1.p_people pp ON pp.man_id = cl.man_id
JOIN INSIS_CNT_V1.cfg_nom_language_table LNG ON LNG.TABLE_NAME = 'HST_POLICY_STATE' AND LNG.ID = PLC.POLICY_STATE AND LNG.LANGUAGE = 'RUSSIAN'
JOIN INSIS_CNT_V1.HT_INSR_TYPE INS ON INS.ID = PLC.INSR_TYPE
JOIN INSIS_CNT_V1.cnt_offices OFFICE ON OFFICE.OFFICE_ID = PLC.OFFICE_ID
JOIN INSIS_CNT_V1.P_STAFF STAFF ON STAFF.STAFF_ID = PLC.STAFF_ID
JOIN INSIS_CNT_V1.P_PEOPLE PPSTAFF ON STAFF.MAN_ID = PPSTAFF.MAN_ID)
LOOP
pipe ROW (I.POLICY_ID, I.POLICY_STATE, I.POLICY_NO, I.INSR_BEGIN, I.INSR_END, I.PRODUCT,
I.MAN_TYPE, I.DEPARTMENT, I.STAFF, I.MAN_ID, I.POLICY_STATE));
END LOOP;
END IF;
END IF;
RETURN;
END;
Ошибки:
Error(32,46): PLS-00103: Encountered the symbol "," when expecting one of the following: . ( ) * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Error(39,25): PLS-00103: Encountered the symbol "LEFT" when expecting one of the following: ) , for group having intersect minus order start union where connect
Хотя запросы написаны правильною Не могу понять в чем дело)
|