|
Ошибка - слишком длинная процедура
#39538148
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
[DCC Error] fmReportExecutionProfitU.pas(960): E2283 Too many local constants. Use shorter procedures
Там практически один оператор - присвоение текста SQL-запросу. Запрос длинный, с вложенными подзапросами.
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.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
procedure TfmReportExecutionProfit.ChangeQueryCoopS;
begin
ADQueryCoopS.SQL.Text :=
'SELECT '#13#10 +
'SUM(sum_fact_prr_1) AS s_sum_fact_prr_1, '#13#10 +
'SUM(sum_fact_other_1) AS s_sum_fact_other_1, '#13#10 +
'SUM(sum_fact_teo_1) AS s_sum_fact_teo_1, '#13#10 +
'SUM(sum_fact_fares_1) AS s_sum_fact_fares_1, '#13#10 +
'SUM(sum_fact_storage_1) AS s_sum_fact_storage_1, '#13#10 +
'SUM(sum_fact_prr_1) + '#13#10 +
'SUM(sum_fact_other_1) + '#13#10 +
'SUM(sum_fact_teo_1) + '#13#10 +
'SUM(sum_fact_fares_1) + '#13#10 +
'SUM(sum_fact_storage_1) AS s_sum_fact_1, '#13#10 +
'SUM(sum_fact_prr_2) AS s_sum_fact_prr_2, '#13#10 +
'SUM(sum_fact_other_2) AS s_sum_fact_other_2, '#13#10 +
'SUM(sum_fact_teo_2) AS s_sum_fact_teo_2, '#13#10 +
'SUM(sum_fact_fares_2) AS s_sum_fact_fares_2, '#13#10 +
'SUM(sum_fact_storage_2) AS s_sum_fact_storage_2, '#13#10 +
'SUM(sum_fact_prr_2) + '#13#10 +
'SUM(sum_fact_other_2) + '#13#10 +
'SUM(sum_fact_teo_2) + '#13#10 +
'SUM(sum_fact_fares_2) + '#13#10 +
'SUM(sum_fact_storage_2) AS s_sum_fact_2, '#13#10 +
'SUM(sum_fact_prr_4) AS s_sum_fact_prr_4, '#13#10 +
'SUM(sum_fact_other_4) AS s_sum_fact_other_4, '#13#10 +
'SUM(sum_fact_teo_4) AS s_sum_fact_teo_4, '#13#10 +
'SUM(sum_fact_fares_4) AS s_sum_fact_fares_4, '#13#10 +
'SUM(sum_fact_storage_4) AS s_sum_fact_storage_4, '#13#10 +
'SUM(sum_fact_prr_4) + '#13#10 +
'SUM(sum_fact_other_4) + '#13#10 +
'SUM(sum_fact_teo_4) + '#13#10 +
'SUM(sum_fact_fares_4) + '#13#10 +
'SUM(sum_fact_storage_4) AS s_sum_fact_4, '#13#10 +
'SUM(sum_fact_prr) AS s_sum_fact_prr, '#13#10 +
'SUM(sum_fact_other) AS s_sum_fact_other, '#13#10 +
'SUM(sum_fact_teo) AS s_sum_fact_teo, '#13#10 +
'SUM(sum_fact_fares) AS s_sum_fact_fares, '#13#10 +
'SUM(sum_fact_storage) AS s_sum_fact_storage, '#13#10 +
'SUM(sum_fact_prr) + '#13#10 +
'SUM(sum_fact_other) + '#13#10 +
'SUM(sum_fact_teo) + '#13#10 +
'SUM(sum_fact_fares) + '#13#10 +
'SUM(sum_fact_storage) AS s_sum_fact '#13#10 +
'FROM ';
ADQueryCoopS.SQL.Add(
'(SELECT '#13#10 +
// 1 район
// подзапрос
'ROUND('#13#10 +
'IFNULL((SELECT COALESCE(SUM(iwn.sum_rub), 0) '#13#10 +
'FROM invoicing_working_norm iwn '#13#10 +
'INNER JOIN contract_working_norm_rate cwnr ON cwnr.id = iwn.contract_working_norm_rate_id '#13#10 +
'INNER JOIN additional_service a1 ON a1.id = cwnr.service_id '#13#10 +
'INNER JOIN report_code rc1 ON rc1.id = a1.report_code_id '#13#10 +
'INNER JOIN invoicing i ON i.id = iwn.invoicing_id '#13#10 +
'INNER JOIN cargo_zone cz1 ON cz1.id = i.cargo_zone_id '#13#10 +
'LEFT JOIN invoice_type it ON it.id = i.invoice_type_id '#13#10 +
'INNER JOIN contract_cargo_head cch1 ON cch1.id = i.contract_cargo_head_id '#13#10 +
'INNER JOIN contract c1 ON c1.id = cch1.contract_id '#13#10 +
'INNER JOIN cargo_nomenclature cn ON cn.id = cch1.cargo_id '#13#10 +
'WHERE UPPER(it.invoice_type_name) IN (''ИМПОРТ'', ''ЭКСПОРТ'', ''КАБОТАЖ'') '#13#10 +
'AND i.cooperated = ''Y'' '#13#10 +
'AND c1.cargo_status = pcp.cargo_status '#13#10 +
'AND cn.cargo_group_id = cg.id '#13#10 +
'AND i.cargo_zone_id = cz.id '#13#10 +
'AND cz1.cargo_zone_number = ''1'' '#13#10 +
'AND i.invoicing_date BETWEEN :date_from AND :date_to '#13#10 +
'AND UPPER(rc1.report_code_name) = ''ПЕРЕГРУЗКА''), 0) '#13#10 +
'+ '#13#10 +
'IFNULL((SELECT COALESCE(SUM(e.sum_rub), 0) '#13#10 +
'FROM invoicing_additional_service_editing e '#13#10 +
'INNER JOIN additional_service a ON a.id = e.service_id '#13#10 +
'INNER JOIN report_code rc ON rc.id = a.report_code_id '#13#10 +
'INNER JOIN invoicing i ON i.id = e.invoicing_id '#13#10 +
'INNER JOIN cargo_zone cz1 ON cz1.id = i.cargo_zone_id '#13#10 +
'LEFT JOIN invoice_type it ON it.id = i.invoice_type_id '#13#10 +
'INNER JOIN contract_cargo_head cch1 ON cch1.id = i.contract_cargo_head_id '#13#10 +
'INNER JOIN contract c1 ON c1.id = cch1.contract_id '#13#10 +
'INNER JOIN cargo_nomenclature cn ON cn.id = cch1.cargo_id '#13#10 +
'WHERE e.show_in_report = ''Y'' '#13#10 +
'AND UPPER(it.invoice_type_name) IN (''ИМПОРТ'', ''ЭКСПОРТ'', ''КАБОТАЖ'') '#13#10 +
'AND i.cooperated = ''Y'' '#13#10 +
'AND c1.cargo_status = pcp.cargo_status '#13#10 +
'AND cn.cargo_group_id = cg.id '#13#10 +
'AND i.cargo_zone_id = cz.id '#13#10 +
'AND cz1.cargo_zone_number = ''1'' '#13#10 +
'AND i.invoicing_date BETWEEN :date_from AND :date_to '#13#10 +
'AND UPPER(rc.report_code_name) = ''ПЕРЕГРУЗКА''), 0) '#13#10 +
', 2) AS sum_fact_prr_1, '#13#10 +
...
//таких подзапросов еще 18
...
'FROM plan_cargo_processing pcp '#13#10 +
'INNER JOIN cargo_group cg ON cg.id = pcp.cargo_group_id '#13#10 +
'INNER JOIN cargo_zone cz ON cz.id = pcp.cargo_zone_id '#13#10 +
'WHERE pcp.cooperated = ''Y'' '#13#10 +
'AND pcp.year = :year '#13#10 +
'AND pcp.month = :month ');
ADQueryCoopS.ParamByName('year').AsInteger := FindYear;
ADQueryCoopS.ParamByName('month').AsInteger := FindMonth;
ADQueryCoopS.ParamByName('date_from').AsDateTime := EncodeDate(FindYear, FindMonth, 1);
ADQueryCoopS.ParamByName('date_to').AsDateTime := EncodeDateTime(FindYear, FindMonth, DaysInAMonth(FindYear, FindMonth), 23, 59, 59, 0);
ADQueryCoopS.SQL.Add('GROUP BY cg.id) iq');
ADQueryCoopS.Open;
end;
Там около 18 длинных подзапросов, по количеству столбцов отчета. И выдает эту ошибку. Без разницы, делаешь все одним текстом
ADQueryCoopS.SQL.Text := '...'
или с помощью нескольких Add
ADQueryCoopS.SQL.Add(...);
Как обойти проблему. Более коротким запрос сделать не получается. |
|
|
