ORA-06550: line 41, column 34:
PLS-00103: Encountered the symbol "OVER" 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
On the following section of my total program, I am getting ORA-06550 error. Can someone help please?
-- Create the table
drop table tg_vsr_staffing_model_final;
CREATE TABLE tg_vsr_staffing_model_final AS
SELECT *
FROM tg_vsr_staffing_model2;
-- Declare constants and define PL/SQL block
DECLARE
vsr_pps1 CONSTANT NUMBER := 0.995098170855218;
vsr_pps14 CONSTANT NUMBER := 0.997061652994855;
vsr_pps27 CONSTANT NUMBER := 0.995701367929822;
vsr_pps40 CONSTANT NUMBER := 0.997055719366802;
vsr_pps53 CONSTANT NUMBER := 0.995504211749385;
vsr_fy23_hire_c CONSTANT NUMBER := 112.443934;
vsr_fy24_hire_c CONSTANT NUMBER := 121.513411;
vsr_fy25_hire_c CONSTANT NUMBER := 47.023203;
vsr_fy23_hire_p CONSTANT NUMBER := 5.2807420740;
vsr_fy24_hire_p CONSTANT NUMBER := 3.3012694585;
vsr_fy25_hire_p CONSTANT NUMBER := 3.3604281504;
fcst_count CONSTANT NUMBER := 78;
MAX_date CONSTANT DATE := TO_DATE('20May2023', 'DDMonYYYY');
-- Loop through the data
TYPE exp_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE lags_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE att_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
exp exp_array;
lags lags_array;
att att_array;
BEGIN
FOR rec IN (SELECT fte_type, pay_period_end_date, EXP1, EXP2, EXP3, EXP4, EXP5, EXP6, EXP7, EXP8, EXP9, EXP10,
EXP11, EXP12, EXP13, EXP14, EXP15, EXP16, EXP17, EXP18, EXP19, EXP20, EXP21, EXP22, EXP23,
EXP24, EXP25, EXP26, EXP27, EXP28, EXP29, EXP30, EXP31, EXP32, EXP33, EXP34, EXP35, EXP36,
EXP37, EXP38, EXP39, EXP40, EXP41, EXP42, EXP43, EXP44, EXP45, EXP46, EXP47, EXP48, EXP49,
EXP50, EXP51, EXP52, EXP53
FROM tg_vsr_staffing_model_final
ORDER BY fte_type, pay_period_end_date)
LOOP
FOR j IN 1..53 LOOP
IF rec.exp(j) IS NULL THEN
exp(j) := 0;
END IF;
lags(j) := LAG(rec.exp(j)) OVER (PARTITION BY rec.fte_type ORDER BY rec.pay_period_end_date);
END LOOP;
The issue is this line -
lags(j) := LAG(rec.exp(j)) OVER (PARTITION BY rec.fte_type ORDER BY rec.pay_period_end_date);
Regards,
Gabe