|
VF OLE DB 9.0 - ошибка "SQL: Statement too long"
#34344979
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Выполняю вот такой зарпос через VF OLE DB 9.0:
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.
SELECT
TabCFO.ID AS [ЦФО],
TabCFO.SP15984 As СортЦФО,
TabSotr.DESCR As Менеджер,
TabSotr.ID As [МенеджерИД],
TabKat.SP15984 As СортКатегорияКл,
TabKat.ID As [КатегорияКл0],
TabKl.ID As [Клиент],
TabKl.DESCR As НаимКлиента,
' 0 ' As [Товар],
' 0 ' AS [Единица],
0 As СуммаБезНДС,
0 AS Кво,
0 As Вес,
0 As Доход,
(SELECT SUM(TabRegVZ.SP1143-TabRegVZ.SP9167)
FROM RA1147 AS TabRegVZ
INNER JOIN 1SJOURN As TabJournVZ ON (TabRegVZ.IDDOC = TabJournVZ.IDDOC)
WHERE (TabRegVZ.SP1138 = ' 2 ')
AND (TabRegVZ.SP1139 = TabKl.ID)
AND ((TabRegVZ.DEBKRED = 1 )
OR ((TabRegVZ.DEBKRED = 0 ) AND (TabJournVZ.IDDOCDEF = ' 4UA')))
AND (TabJournVZ.Date >= Date( 2007 , 1 , 1 ))
AND (TabJournVZ.Date <= Date( 2007 , 1 , 15 ))
) As Оплачено,
(SELECT SUM(TabRegVZ.SP1143)
FROM RA1147 AS TabRegVZ
INNER JOIN 1SJOURN As TabJournVZ ON (TabRegVZ.IDDOC = TabJournVZ.IDDOC)
WHERE (TabRegVZ.SP1138 = ' 2 ')
AND (TabRegVZ.SP1139 = TabKl.ID)
AND ((TabRegVZ.DEBKRED = 1 )
OR ((TabRegVZ.DEBKRED = 0 ) AND (TabJournVZ.IDDOCDEF = ' 4UA')))
AND (TabJournVZ.Date >= Date( 2007 , 1 , 1 ))
AND (TabJournVZ.Date <= Date( 2007 , 1 , 15 ))
) As ОплаченоСНДС,
(SELECT SUM(TabRegVZ2.SP16478)
FROM RA1147 AS TabRegVZ2
INNER JOIN 1SJOURN As TabJournVZ2 ON (TabRegVZ2.IDDOC = TabJournVZ2.IDDOC)
WHERE (TabRegVZ2.SP1138 = ' 2 ')
AND (TabRegVZ2.SP1139 = TabKl.ID)
AND ((TabRegVZ2.DEBKRED = 1 )
OR ((TabRegVZ2.DEBKRED = 0 ) AND (TabJournVZ2.IDDOCDEF = ' 4UA')))
AND (TabJournVZ2.Date >= Date( 2007 , 1 , 1 ))
AND (TabJournVZ2.Date <= Date( 2007 , 1 , 15 ))
) As ОплаченоВСрок,
(SELECT SUM(TabRegVZ2.SP16478)
FROM RA1147 AS TabRegVZ2
INNER JOIN 1SJOURN As TabJournVZ2 ON (TabRegVZ2.IDDOC = TabJournVZ2.IDDOC)
WHERE (TabRegVZ2.SP1138 = ' 2 ')
AND (TabRegVZ2.SP1139 = TabKl.ID)
AND ((TabRegVZ2.DEBKRED = 1 )
OR ((TabRegVZ2.DEBKRED = 0 ) AND (TabJournVZ2.IDDOCDEF = ' 4UA')))
AND (TabJournVZ2.Date > {d, 2007 , 1 , 15 })
AND (TabJournVZ2.Date <= {d, 2007 , 2 , 14 })
) AS Опла30
FROM RA6869 As TabRegOb
INNER JOIN 1SJOURN As TabJourn ON (TabRegOb.IDDOC = TabJourn.IDDOC)
LEFT OUTER JOIN SC514 As TabGroupKl ON ((TabRegOb.SP6856 = TabGroupKl.PARENTEXT) AND (TabGroupKl.ISMARK <> '*'))
LEFT OUTER JOIN SC520 As TabKat ON (TabGroupKl.SP512 = TabKat.ID)
LEFT OUTER JOIN SC520 As TabKat2 ON (TabKat2.ID = TabKat.PARENTID)
LEFT OUTER JOIN SC520 As TabKat3 ON (TabKat3.ID = TabKat2.PARENTID)
LEFT OUTER JOIN SC520 As TabKat4 ON (TabKat4.ID = TabKat3.PARENTID)
LEFT OUTER JOIN SC520 As TabKat5 ON (TabKat5.ID = TabKat4.PARENTID)
LEFT OUTER JOIN SC520 As TabKat6 ON (TabKat6.ID = TabKat5.PARENTID)
INNER JOIN SC191 As TabKl ON (TabRegOb.SP6856 = TabKl.ID)
LEFT OUTER JOIN SC258 As TabSotr ON (TabKl.SP10494 = TabSotr.ID)
LEFT OUTER JOIN SC520 As TabCFO ON (TabKat.SP16335 = TabCFO.ID)
LEFT OUTER JOIN SC191 As TabKlGr1 ON (TabKl.PARENTID = TabKlGr1.ID)
LEFT OUTER JOIN SC191 As TabKlGr2 ON (TabKlGr1.PARENTID = TabKlGr2.ID)
LEFT OUTER JOIN SC191 As TabKlGr3 ON (TabKlGr2.PARENTID = TabKlGr3.ID)
LEFT OUTER JOIN SC191 As TabKlGr4 ON (TabKlGr3.PARENTID = TabKlGr4.ID)
INNER JOIN SC289 As TabTov ON (TabRegOb.SP6854 = TabTov.ID)
LEFT OUTER JOIN SC517 As TabGroupTov ON ((TabRegOb.SP6854 = TabGroupTov.PARENTEXT) AND (TabGroupTov.ISMARK <> '*'))
LEFT OUTER JOIN SC520 As TabKatTov ON (TabGroupTov.SP515 = TabKatTov.ID)
LEFT OUTER JOIN SC520 As TabKatTov2 ON (TabKatTov2.ID = TabKatTov.PARENTID)
LEFT OUTER JOIN SC520 As TabKatTov3 ON (TabKatTov3.ID = TabKatTov2.PARENTID)
LEFT OUTER JOIN SC520 As TabKatTov4 ON (TabKatTov4.ID = TabKatTov3.PARENTID)
LEFT OUTER JOIN SC520 As TabKatTov5 ON (TabKatTov5.ID = TabKatTov4.PARENTID)
LEFT OUTER JOIN SC520 As TabKatTov6 ON (TabKatTov6.ID = TabKatTov5.PARENTID)
LEFT OUTER JOIN SC289 As TabTovGr1 ON (TabTov.PARENTID = TabTovGr1.ID)
LEFT OUTER JOIN SC289 As TabTovGr2 ON (TabTovGr1.PARENTID = TabTovGr2.ID)
LEFT OUTER JOIN SC289 As TabTovGr3 ON (TabTovGr2.PARENTID = TabTovGr3.ID)
LEFT OUTER JOIN SC289 As TabTovGr4 ON (TabTovGr3.PARENTID = TabTovGr4.ID)
WHERE (TabRegOb.SP6853 = ' 2 ')
AND (TabJourn.Date >= Date( 2007 , 1 , 1 ))
AND (TabJourn.Date <= Date( 2007 , 1 , 15 ))
AND ((TabKat6.ID = ' 36 ') OR (TabKat5.ID = ' 36 ') OR (TabKat4.ID = ' 36 ') OR (TabKat3.ID = ' 36 ') OR (TabKat2.ID = ' 36 ') OR (TabKat.ID = ' 36 '))
AND ((TabKlGr1.ID = ' D ') OR (TabKlGr2.ID = ' D ') OR (TabKlGr3.ID = ' D ') OR (TabKlGr4.ID = ' D '))
AND (TabSotr.ID = ' C ')
GROUP BY
TabCFO.ID,
TabCFO.SP15984,
TabSotr.DESCR,
TabSotr.ID,
TabKat.SP15984,
TabKat.ID,
TabKl.ID,
TabKl.DESCR
HAVING (SUM(TabRegOb.SP6862) <> 0 )
OR (SUM(TabRegOb.SP6863) <> 0 )
В ответ вот такое сообщение: "SQL: Statement too long.".
Понимаю что запрос неоптимальный, его можно переписать без использования троих подзапросов для получения сумм, но больше интерсует почему возникает таквя ошибка, причем если последний подзапрос (... AS Опла30) убрать - то запрос выполняется без проблем.
Нашел на MSDN ссылку: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/err1812.asp
но чего то никакого внятного овтета не получил, подскажите, где может быть ошибка?
Заранее спасибо. |
|
|
