|
sql state s0002 table not found
#38930743
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
spas2001,
Пробую так сделать, но теперь выдает ошибку таблица #t_all_timelines already exist, в общем вот код процедуры, помогите разобраться, люди добрые))
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.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
ALTER PROCEDURE "DBA"."get_spec_employment"
( in s_id int, in ls_stat int, in ls_from datetime, in ls_to datetime, in ord int )
BEGIN
/********RECEIVE***********/
//s_id - specialist id (from dba.specialists)
//ls_stat - status of work (from dba.work_stats)
//ls_from - period from
//ls_to - period to
/**************************/
//IF ls_stat = 1 or ls_stat = 3 THEN////FOR OPENED 1 AND POSTOPENED 2 AND CLOSED 3!!!!
create table #t_all_timelines
(id integer, work_id integer, comments text, from_dt timestamp, to_dt timestamp, amount money);
IF ls_stat = 1 THEN
select id into #t_needed_works from "DBA"."works"
WHERE
spec_id = s_id and stat_id = ls_stat;
select
id, work_id, comments, from_dt, to_dt
into #t_all_timelines from "DBA"."work_hist"
WHERE
work_id in ( select id from #t_needed_works );
ELSEIF ls_stat = 2 THEN
select id into #t_needed_works from "DBA"."works"
WHERE
stat_id = ls_stat;
select
id, work_id, comments, from_dt, to_dt
into #t_all_timelines from "DBA"."work_hist"
WHERE
work_id in ( select id from #t_needed_works );
ELSEIF ls_stat = 3 THEN /// -1 for report
SELECT
id, work_id, comments, from_dt, to_dt
INTO #t_all_timelines
FROM "dba"."work_hist"
WHERE (
(
to_dt >= ls_to
and
from_dt <= ls_from
)
or
(
to_dt <= ls_to
and
to_dt >= ls_from
)
or
(
from_dt <= ls_to
and
from_dt >= ls_from
)
)
and work_id in (
select id from "DBA"."works"
WHERE
spec_id = (IF s_id > 0 THEN s_id ELSE spec_id ENDIF)
and stat_id = ls_stat
)
END IF;
select
distinct(t1.work_id) as w_id,
(select List(STRING('[',from_dt, ',', to_dt,']'), ',' ORDER BY from_dt) from "DBA"."work_hist" t2 WHERE t2.work_id = w_id)
as timestamps,
(select List(STRING('[',"comments",']'), ',' ORDER BY from_dt) from "DBA"."work_hist" t3 WHERE t3.work_id = w_id)
as comments,
(select List(STRING('[',"id",']'), ',' ORDER BY from_dt) from "DBA"."work_hist" t2 WHERE t2.work_id = w_id)
as timestamp_ids
into #t_needed_timelines
from #t_all_timelines t1 ;
//IF ls_stat = 1 or ls_stat = 2 or ls_stat = 3 THEN////FOR OPENED AND POSTOPENED AND CLOSED - ALL EVEN EMPTY!!!!
IF ls_stat = 1 or ls_stat = 2 THEN
insert into #t_needed_timelines (w_id, "timestamps", "comments", "timestamp_ids")
select id, '', '', ''
from "DBA"."works" tt
LEFT OUTER JOIN #t_needed_timelines t2
ON tt.id = t2.w_id
WHERE
tt.id in ( select distinct(id) from #t_needed_works )
and
tt.id not in ( select distinct(work_id) from #t_all_timelines )
END IF;
select
work_stats.value as stat_s,
t6.comments as main_comment,
t5.w_id as w_id,
t5.timestamps,
t5.comments,
work_stats.id as stat_int,
t5.timestamp_ids,
(IF t10.ticket_id is not NULL THEN 1 ELSE 0 ENDIF) as msgs
into #all_rows
from #t_needed_timelines t5
LEFT OUTER JOIN "dba"."works" t6
ON t5.w_id = t6.id
LEFT OUTER JOIN "dba"."work_stats"
ON work_stats.id = t6.stat_id
LEFT OUTER JOIN "dba"."ticket_hist" t9
ON w_id = t9.work_id
LEFT OUTER JOIN "dba"."ticket_msg" t10
ON t9.ticket_id = t10.ticket_id and t10.readed=0 and t10.creator_id != s_id
ORDER BY w_id;
select t7.*, t8.ticket_id, t9.value as place from #all_rows t7
LEFT OUTER JOIN "dba"."ticket_hist" t8
ON t7.w_id = t8.work_id
LEFT OUTER JOIN "dba"."tickets" t82
ON t8.ticket_id = t82.id
LEFT OUTER JOIN "dba"."places_all" t9
ON t82.place_id = t9.id
group by w_id,stat_s,main_comment,timestamps,comments,stat_int,timestamp_ids,ticket_id,place,msgs
order by
CASE WHEN ord = 1 THEN w_id ELSE '' END ASC,
CASE WHEN ord = 2 THEN w_id ELSE '' END DESC,
CASE WHEN ord = 3 THEN ticket_id ELSE '' END ASC,
CASE WHEN ord = 4 THEN ticket_id ELSE '' END DESC;
END
|
|
|
