Уважаемые форумчане,
помогите исправить ошибку.
Есть код, который тестирую в
http://sqlfiddle.com/
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
CREATE TYPE O_SALE AS OBJECT
( SALE_MONTH INTEGER,
SALE_AMOUNT NUMBER);/
CREATE TYPE T_SALE AS TABLE OF O_SALE;/
CREATE OR REPLACE FUNCTION "GET_SALES_MONTH" (I_MONTH_NUMBER IN INTEGER)
RETURN T_SALE
PIPELINED AS
L_O_SALE O_SALE := NULL;
L_T_SALE T_SALE := T_SALE();
BEGIN
FOR I IN (SELECT S.SALE_AMOUNT,
S.SALE_DATE,
EXTRACT(MONTH FROM S.SALE_DATE) AS DATE_MONTH_7,
EXTRACT(MONTH FROM S.SALE_DATE - 1) AS DATE_MONTH_1,
EXTRACT(MONTH FROM S.SALE_DATE - 4) AS DATE_MONTH_4,
EXTRACT(MONTH FROM S.SALE_DATE - 5) AS DATE_MONTH_5,
EXTRACT(MONTH FROM S.SALE_DATE - 6) AS DATE_MONTH_6,
S.SALE_AMOUNT / 5 AS AMOUNT_DAY
FROM SALE S) LOOP
L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_1,
SALE_AMOUNT => I.AMOUNT_DAY);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_4,
SALE_AMOUNT => I.AMOUNT_DAY);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_5,
SALE_AMOUNT => I.AMOUNT_DAY);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_6,
SALE_AMOUNT => I.AMOUNT_DAY);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_7,
SALE_AMOUNT => I.AMOUNT_DAY);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
END LOOP;
FOR T IN (SELECT SUM(S.SALE_AMOUNT) AS SUM_SALE_AMOUNT, S.SALE_MONTH
FROM (TABLE(L_T_SALE)) S
WHERE S.SALE_MONTH = NVL(I_MONTH_NUMBER, S.SALE_MONTH)
GROUP BY S.SALE_MONTH) LOOP
L_O_SALE := O_SALE(SALE_MONTH => T.SALE_MONTH,
SALE_AMOUNT => T.SUM_SALE_AMOUNT);
L_T_SALE.EXTEND;
L_T_SALE(L_T_SALE.COUNT) := L_O_SALE;
PIPE ROW(L_O_SALE);
END LOOP;
RETURN;
END GET_SALES_MONTH;
Ругается на строчку " EXTRACT(MONTH FROM S.SALE_DATE - 6) AS DATE_MONTH_6, "
Но почему именно на неё не понятно. Она такая же как предыдущие
