<Maxstring>File+6^%qarmac / Caché, Ensemble, DeepSee, MiniM, IRIS, GT.M

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Caché, Ensemble, DeepSee, MiniM, IRIS, GT.M [игнор отключен] [закрыт для гостей] / <Maxstring>File+6^%qarmac

9 сообщений из 34, страница 2 из 2

все

<Maxstring>File+6^%qarmac

#38120036

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

DDL не разрешен. Только Select! Буду разбираться. Как прийду к чему нибудь так отпишусь

...

Рейтинг:

0 / 0

22.01.2013, 17:09

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120676

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

Странно что Cache не обрабатывает запрос такой длины

...

Рейтинг:

0 / 0

23.01.2013, 05:43

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120741

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

Все супер... Выгружается на отлично)))) Код SQL получился следующий:

Код: 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.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.

SELECT DISTINCT
	cast(To_number(Substring(Insurerfk->RegNumberPFR from 4 for 3)) as numeric(3,0)) as "RAION",
	cast(To_number(Substring(Insurerfk->RegNumberPFR from 7 for 6)) as numeric(6,0)) as "REGNUMB",
	cast(To_number(Insurerfk->RegNumberPFR) as numeric(12,0)) as "ENTNMB", 
	cast(IsNull(to_number(Year),0) as numeric(4,0)) as "YEAR",
	cast(To_number(Period) as numeric(2,0)) as "PERIOD",
	cast(IsNull(to_number(CountPeople),0) as numeric(7,0)) as "CNTZL",
	cast(IsNull(to_number(MeanWorker),0) as numeric(7,0)) as "MWRK",
	cast(IsNull(FormatVersion, 1) as numeric(1,0)) as "FVersion",
	cast(IsNull(+$piece(Fields,'R100_C3:',2),0) as numeric(15,2)) as "STR_100", 
	cast(IsNull(+$piece(Fields,'R100_C4:',2),0) as numeric(15,2)) as "NAK_100", 
	cast(IsNull(+$piece(Fields,'R110_C3:',2),0) as numeric(15,2)) as "STR_110", 
	cast(IsNull(+$piece(Fields,'R110_C4:',2),0) as numeric(15,2)) as "NAK_110", 
	cast(IsNull(+$piece(Fields,'R120_C3:',2),0) as numeric(15,2)) as "STR_120", 
	cast(IsNull(+$piece(Fields,'R120_C4:',2),0) as numeric(15,2)) as "NAK_120", 
	cast(IsNull(+$piece(Fields,'R140_C3:',2),0) as numeric(15,2)) as "STR_140", 
	cast(IsNull(+$piece(Fields,'R140_C4:',2),0) as numeric(15,2)) as "NAK_140", 
	cast(IsNull(+$piece(Fields,'R150_C3:',2),0) as numeric(15,2)) as "STR_150", 
	cast(IsNull(+$piece(Fields,'R150_C4:',2),0) as numeric(15,2)) as "NAK_150",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_200", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_210", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_214", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_215",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_216", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_220", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_221", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R300_C3:',2),0)
	as numeric(15,2)) as "STR_300", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',4),0) else 0 end) +
	IsNull(+$piece(Fields,'R310_C3:',2),0)
	as numeric(15,2)) as "STR_310", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R314_C3:',2),0)
	as numeric(15,2)) as "STR_314", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R315_C3:',2),0)
	as numeric(15,2)) as "STR_315", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R316_C3:',2),0)
	as numeric(15,2)) as "STR_316", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',4),0) else 0 end) +
	IsNull(+$piece(Fields,'R320_C3:',2),0)
	as numeric(15,2)) as "STR_320", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R321_C3:',2),0)
	as numeric(15,2)) as "STR_321",
	cast(0 as numeric(15,2)) as "STR_252",
	cast(0 as numeric(15,2)) as "STR_201_N3",
	cast(0 as numeric(15,2)) as "STR_202_N3",
	cast(0 as numeric(15,2)) as "STR_203_N3",
	cast(0 as numeric(15,2)) as "STR_211_N3",
	cast(0 as numeric(15,2)) as "STR_212_N3",
	cast(0 as numeric(15,2)) as "STR_213_N3",
	cast(0 as numeric(15,2)) as "STR_231_N3",
	cast(0 as numeric(15,2)) as "STR_232_N3",
	cast(0 as numeric(15,2)) as "STR_233_N3",
	cast(0 as numeric(15,2)) as "STR_250_N3",
	cast(0 as numeric(15,2)) as "STR_251_N3",
	cast(0 as numeric(15,2)) as "STR_201_03",
	cast(0 as numeric(15,2)) as "STR_202_03",
	cast(0 as numeric(15,2)) as "STR_203_03",
	cast(0 as numeric(15,2)) as "STR_211_03",
	cast(0 as numeric(15,2)) as "STR_212_03",
	cast(0 as numeric(15,2)) as "STR_213_03",
	cast(0 as numeric(15,2)) as "STR_231_03",
	cast(0 as numeric(15,2)) as "STR_232_03",
	cast(0 as numeric(15,2)) as "STR_233_03",
	cast(0 as numeric(15,2)) as "STR_250_03",
	cast(0 as numeric(15,2)) as "STR_251_03"
FROM 
	ASV_Entities.CalcStatementTitle  
where
	ID in (
		select id 
		from ASV_Entities.CalcStatementTitle a1 
		where
			Period = 2 and
			Year = 2010 and 
			Type = 1 and 
			IsActual = 1 and
			CorrectNumber = (SELECT MAX(CorrectNumber) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual) = 1 AND (a1.InsurerFK = a2.InsurerFK)) and
			EnterDate = (SELECT MAX(EnterDate) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK))
		UNION
		select id
		from ASV_Entities.CalcStatementTitle a1
		where
			Period = 1 and
			Year = 2010 and 
			Type = 1 and 
			IsActual = 1 and
			CorrectNumber = (SELECT MAX(CorrectNumber) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual) = 1 AND (a1.InsurerFK = a2.InsurerFK)) and
			EnterDate = (SELECT MAX(EnterDate) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK)) and
			not exists
			(SELECT InsurerFK FROM ASV_Entities.CalcStatementTitle a2 WHERE (IsNull(+$piece(a2.Fields,'R110_C3:',2),0) + IsNull(+$piece(a2.Fields,'R110_C4:',2),0) > 0) AND (2 = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK))
		UNION
		select id
		from ASV_Entities.CalcStatementTitle a1
		where
			Period = 4 and
			Year = 2010 and 
			Type = 1 and 
			IsActual = 1 and
			CorrectNumber = (SELECT MAX(CorrectNumber) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual) = 1 AND (a1.InsurerFK = a2.InsurerFK)) and
			EnterDate = (SELECT MAX(EnterDate) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK))
		UNION
		select id
		from ASV_Entities.CalcStatementTitle a1
		where
			Period = 3 and
			Year = 2010 and 
			Type = 1 and 
			IsActual = 1 and
			CorrectNumber = (SELECT MAX(CorrectNumber) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual) = 1 AND (a1.InsurerFK = a2.InsurerFK)) and
			EnterDate = (SELECT MAX(EnterDate) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK)) and
			not exists
			(SELECT InsurerFK FROM ASV_Entities.CalcStatementTitle a2 WHERE (4 = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK))
		
		)
UNION
SELECT DISTINCT
	cast(To_number(Substring(Insurerfk->RegNumberPFR from 4 for 3)) as numeric(3,0)) as "RAION",
	cast(To_number(Substring(Insurerfk->RegNumberPFR from 7 for 6)) as numeric(6,0)) as "REGNUMB",
	cast(To_number(Insurerfk->RegNumberPFR) as numeric(12,0)) as "ENTNMB", 
	cast(IsNull(to_number(Year),0) as numeric(4,0)) as "YEAR",
	cast(To_number(Period) as numeric(2,0)) as "PERIOD",
	cast(IsNull(to_number(CountPeople),0) as numeric(7,0)) as "CNTZL",
	cast(IsNull(to_number(MeanWorker),0) as numeric(7,0)) as "MWRK",
	cast(IsNull(FormatVersion, 1) as numeric(1,0)) as "FVersion",
	cast(IsNull(+$piece(Fields,'R100_C3:',2),0) as numeric(15,2)) as "STR_100", 
	cast(IsNull(+$piece(Fields,'R100_C4:',2),0) as numeric(15,2)) as "NAK_100", 
	cast(IsNull(+$piece(Fields,'R110_C3:',2),0) as numeric(15,2)) as "STR_110", 
	cast(IsNull(+$piece(Fields,'R110_C4:',2),0) as numeric(15,2)) as "NAK_110", 
	cast(IsNull(+$piece(Fields,'R120_C3:',2),0) as numeric(15,2)) as "STR_120", 
	cast(IsNull(+$piece(Fields,'R120_C4:',2),0) as numeric(15,2)) as "NAK_120", 
	cast(IsNull(+$piece(Fields,'R140_C3:',2),0) as numeric(15,2)) as "STR_140", 
	cast(IsNull(+$piece(Fields,'R140_C4:',2),0) as numeric(15,2)) as "NAK_140", 
	cast(IsNull(+$piece(Fields,'R150_C3:',2),0) as numeric(15,2)) as "STR_150", 
	cast(IsNull(+$piece(Fields,'R150_C4:',2),0) as numeric(15,2)) as "NAK_150",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R200_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_200", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R210_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_210", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R214_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_214", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R215_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_215",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R216_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_216", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R220_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_220", 
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R221_C3:',4),0) else 0 end)
	as numeric(15,2)) as "STR_221", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R200_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R300_C3:',2),0)
	as numeric(15,2)) as "STR_300", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R210_C3:',4),0) else 0 end) +
	IsNull(+$piece(Fields,'R310_C3:',2),0)
	as numeric(15,2)) as "STR_310", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R214_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R314_C3:',2),0)
	as numeric(15,2)) as "STR_314", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R215_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R315_C3:',2),0)
	as numeric(15,2)) as "STR_315", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R216_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R316_C3:',2),0)
	as numeric(15,2)) as "STR_316", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R220_C3:',4),0) else 0 end) +
	IsNull(+$piece(Fields,'R320_C3:',2),0)
	as numeric(15,2)) as "STR_320", 
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R221_C3:',4),0) else 0 end) + 
	IsNull(+$piece(Fields,'R321_C3:',2),0)
	as numeric(15,2)) as "STR_321",
	cast(
	IsNull(+$piece(Fields,'R252_C4:',2),0) + IsNull(+$piece(Fields,'R252_C5:',2),0) + IsNull(+$piece(Fields,'R252_C6:',2),0) +
	IsNull(+$piece(Fields,'R252_C4:',3),0) + IsNull(+$piece(Fields,'R252_C5:',3),0) + IsNull(+$piece(Fields,'R252_C6:',3),0) +
	IsNull(+$piece(Fields,'R252_C4:',4),0) + IsNull(+$piece(Fields,'R252_C5:',4),0) + IsNull(+$piece(Fields,'R252_C6:',4),0) 
	as numeric(15,2)) as "STR_252",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R201_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R201_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R201_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_201_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R202_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R202_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R202_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_202_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R203_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R203_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R203_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_203_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R211_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R211_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R211_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_211_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R212_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R212_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R212_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_212_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R213_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R213_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R213_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_213_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R231_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R231_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R231_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_231_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R232_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R232_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R232_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_232_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R233_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R233_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R233_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_233_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R240_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R240_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R240_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_250_N3",
	cast(
	(case when IsNull(TariffCode, '00') <> '03' then IsNull(+$piece(Fields,'R241_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') <> '03' then IsNull(+$piece(Fields,'R241_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') <> '03' then IsNull(+$piece(Fields,'R241_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_251_N3",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R201_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R201_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R201_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_201_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R202_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R202_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R202_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_202_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R203_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R203_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R203_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_203_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R211_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R211_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R211_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_211_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R212_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R212_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R212_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_212_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R213_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R213_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R213_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_213_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R231_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R231_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R231_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_231_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R232_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R232_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R232_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_232_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R233_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R233_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R233_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_233_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R240_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R240_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R240_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_250_03",
	cast(
	(case when IsNull(TariffCode, '00') = '03' then IsNull(+$piece(Fields,'R241_C3:',2),0) else 0 end) + 
	(case when IsNull(TariffCode2, '00') = '03' then IsNull(+$piece(Fields,'R241_C3:',3),0) else 0 end) + 
	(case when IsNull(TariffCode3, '00') = '03' then IsNull(+$piece(Fields,'R241_C3:',4),0) else 0 end)  
	as numeric(15,2)) as "STR_251_03"
FROM 
	ASV_Entities.CalcStatementTitle
where
	ID in (
		select id
		from ASV_Entities.CalcStatementTitle a1
		where
			Year >= 2011 and 
			Type = 1 and 
			IsActual = 1 and
			CorrectNumber = (SELECT MAX(CorrectNumber) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK)) and
			EnterDate = (SELECT MAX(EnterDate) AS Expr1 FROM ASV_Entities.CalcStatementTitle a2 WHERE (a1.Period = a2.Period) AND (a1.Year = a2.Year) AND (a1.Type = a2.Type) AND (IsActual = 1) AND (a1.InsurerFK = a2.InsurerFK))
		)

...

Рейтинг:

0 / 0

23.01.2013, 09:05

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120745

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

doublefintdartveider13 Может у вас есть задумка как можно упростить мой запрос?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

 Select 
  --код извлечения данных один раз
 From  ASV_Entities.CalcStatementTitle
 Where ID In (
   Select ID From ASV_Entities.CalcStatementTitle Where (year=2010 And period
   Union
   Select ID From ASV_Entities.CalcStatementTitle Where (year=2011 And period
   Union
   Select ID From ASV_Entities.CalcStatementTitle Where (year=2012 And period
)

Спасибище огромное... Вот такого рода конструкция спасла

...

Рейтинг:

0 / 0

23.01.2013, 09:07

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120803

servit

Участник

Откуда: г. Кишинёв, Республика Молдова

Сообщения: 3 167

Рейтинг: 0 / 0

dartveider13Странно что Cache не обрабатывает запрос такой длиныПроверил на версии 2013.1 запрос ~45Кб - отрабатывает нормально.

...

Рейтинг:

0 / 0

23.01.2013, 09:58

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120910

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

У нас версия 2011

...

Рейтинг:

0 / 0

23.01.2013, 11:01

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38120919

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

методом исключения стало понятно что данная версия не отрабатывает запросы который весят более 40 кб. Фигня какая то

...

Рейтинг:

0 / 0

23.01.2013, 11:09

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38124097

dartveider13

Участник

Откуда: Омск

Сообщения: 766

Рейтинг: 0 / 0

Наш отдел информационных технологий выяснил, что запросы весом более 40 кб не работали, т к был включен какой то сбор статистики в СУБД. После того как его отключили, запрос стал выполнятся!!!!!! Возьмите на заметку

...

Рейтинг:

0 / 0

25.01.2013, 07:37

| Ответить | Цитировать | Написать

<Maxstring>File+6^%qarmac

#38124228

servit

Участник

Откуда: г. Кишинёв, Республика Молдова

Сообщения: 3 167

Рейтинг: 0 / 0

dartveider13 ,

Если это был встроенный в Caché сбор статистики, тогда это баг и нужно сообщить в WRC для его исправления.
Можно узнать что именно включали?

...

Рейтинг:

0 / 0

25.01.2013, 10:23

| Ответить | Цитировать | Написать

9 сообщений из 34, страница 2 из 2

все

Форумы / Caché, Ensemble, DeepSee, MiniM, IRIS, GT.M [игнор отключен] [закрыт для гостей] / <Maxstring>File+6^%qarmac

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=39&msg=38120036&tid=1557249]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	164ms
get topic data:	12ms
get forum data:	3ms
get page messages:	52ms
get tp. blocked users:	2ms
others:	13ms

total:	278ms