Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Internal error: An expression services limit has been reached. Please look for potentially  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
версия:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 

написал запрос там много Case.
выдает ошибку:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

вот сам запрос:

select case    when  b.Тип in (1,2,3,4,5,6,7,8) 
               then '1  группа'
               when  b.Тип in (9,10,11,12,13) 
               then '2  литьевые изделия без камней'
               when  b.Тип in (14,15,16,17) 
               then '3  изделия с алмазной обработкой'
               when  b.Тип in (18,19,20,21,22) 
               then '4  изделия с фианитами'
               when  b.Тип in (23,24,25) 
               then '5  изделия с цветными фианитами'
               when  b.Тип=58 
               then '51 изделия с муранским стеклом'
               when  b.Тип in (26,27,28,29,30) 
               then '6  изделия с ониксом'
               when  b.Тип in (31,32,33,34,35,36) 
               then '7  изделия с натуральными вставками'
               when  b.Тип in (37,38) 
               then '8  Изделия Роберта'
               when  b.Тип in (39,40,41,42,43,44,45) 
               then '9  Штампованные изделия'
               when  b.Тип in (46,47,48,49) 
               then '91 Цепи, Браслеты и колье'
               when  b.Тип in (50,51,52,53,54) 
               then '92 Изделия ручной работы и машинного плетения'
               when  b.Тип in (55,56,57) 
               then '93 изделия'   
               else '0  без группы'
               end as Группа
               ,b.Тип, b.Артикул,b.brigada, b.[Отгрузка, шт], b.[Отгрузка, гр]
into #ot
from
(SELECT	
        case 
             when ((artikuls.innerartikul like N'01З%' and artikuls.innerartikul not like N'01З4%') or artikuls.innerartikul like N'01Ж%') and (brigada.brigadaID not in (4,32,8,24,25,30,7,23)) and artikuls.Proba=585
             then 1
             when (artikuls.innerartikul like N'01Н%') and (artikuls.innerartikul not like N'01Н4%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23) and artikuls.Proba=585  
             then 2
             when (artikuls.innerartikul like N'01Т%') and (artikuls.innerartikul not like N'01Т4%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23) and artikuls.Proba=585
             then 3
             when (artikuls.innerartikul like N'01Г%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 4
             when (((artikuls.innerartikul like N'01Ш%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23)) or ((artikuls.innerartikul like N'01В%' and artikuls.innerartikul not like N'01В3%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23))) and artikuls.Proba=585 
             then 5
	     when (artikuls.innerartikul like N'01С%') and artikuls.Proba=585  and  brigada.brigadaID in (28,11) 
             then 6
             when (artikuls.innerartikul like N'01И%') and (artikuls.innerartikul not like N'01И3%')  and  brigada.brigadaID in (28) and artikuls.Proba=585 
             then 7
             when  brigada.brigadaID in (4,30) and artikuls.Proba=585 
             then 8
             when (artikuls.innerartikul like N'01Д0%')  and artikuls.Proba=585 and brigada.brigadaID not in (4,32,8,24,25,30,27) 
             then 9
             when (artikuls.innerartikul like N'01П0%') and brigada.brigadaID not in (4,32,8,24,25,30,27) and artikuls.Proba=585 
             then 10
             when (artikuls.innerartikul like N'01Р0%') and brigada.brigadaID not in (4,32,8,24,25,30,27) and artikuls.Proba=585 
             then 11
             when (artikuls.innerartikul like N'01К0%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 12
             when (artikuls.innerartikul like N'01С0%') and brigada.brigadaID not in (4,32,8,24,25,30,39) and artikuls.Proba=585 
             then 13
             when (artikuls.innerartikul like N'01П7%') and brigada.brigadaID not in (4,32,8,24,25,30,26) and artikuls.Proba=585 
             then 14
             when (artikuls.innerartikul like N'01Р7%') and brigada.brigadaID  in (3,31) and artikuls.Proba=585 
             then 15
             when (artikuls.innerartikul like N'01К7%')  and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 16
             when (artikuls.innerartikul like N'01С7%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39) and artikuls.Proba=585 
             then 17
             when (artikuls.innerartikul like N'01Д1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 18
             when (artikuls.innerartikul like N'01П1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 19
             when (artikuls.innerartikul like N'01Р1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 20
             when (artikuls.innerartikul like N'01К1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 21
             when (artikuls.innerartikul like N'01С1%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39) and artikuls.Proba=585 
             then 22
             when (artikuls.innerartikul like N'01П2%' or artikuls.innerartikul like N'01Р2%' ) and brigada.brigadaID not in (4,32,8,24,25,30,40) and artikuls.Proba=585
             then 23
             when (artikuls.innerartikul like N'01К2%') and brigada.brigadaID not in (4,32,8,24,25,30,40) and artikuls.Proba=585 
             then 24
             when (artikuls.innerartikul like N'01С2%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39,40) and artikuls.Proba=585 
             then 25
             when (artikuls.innerartikul like N'01Т4%' or artikuls.innerartikul like N'01К4%' ) and brigada.brigadaID not in (4,32,8,24,25,30,23,7) and artikuls.Proba=585
             then 26
             when (artikuls.innerartikul like N'01П4%' or artikuls.innerartikul like N'01Р4%' ) and brigada.brigadaID not in (4,32,8,24,25,30,23,7) and artikuls.Proba=585
             then 27
             when (artikuls.innerartikul like N'01З4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 28
             when (artikuls.innerartikul like N'01Б4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 29
             when (artikuls.innerartikul like N'01Н4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 30
             when (artikuls.innerartikul like N'01И3%') and  brigada.brigadaID in (28) and artikuls.Proba=585 
             then 31
             when (((artikuls.innerartikul like N'01П3%' or artikuls.innerartikul like N'01П4%') and artikuls.innerartikul not like N'01П32%' and artikuls.innerartikul not like N'01П33%' )  or ((artikuls.innerartikul like N'01Р3%' or artikuls.innerartikul like N'01Р4%') and artikuls.innerartikul not like N'01Р32%' and artikuls.innerartikul not like N'01Р33%' )) and brigada.brigadaID not in (4,32,8,24,25,30,6,29) and artikuls.Proba=585
             then 32
             when ((artikuls.innerartikul like N'01Т3%' and artikuls.innerartikul not like N'01Т32%' and artikuls.innerartikul not like N'01Т33%' )  or ((artikuls.innerartikul like N'01К3%' or artikuls.innerartikul like N'01К4%') and artikuls.innerartikul not like N'01К32%' and artikuls.innerartikul not like N'01К33%' )) and brigada.brigadaID not in (4,32,8,24,25,30,6,29) and artikuls.Proba=585
             then 33
             when ((artikuls.innerartikul like N'01С3%' and artikuls.innerartikul not like N'01С32%' and artikuls.innerartikul not like N'01С33%' )  or (artikuls.innerartikul like N'01С4%'))
                   and brigada.brigadaID not in (4,32,8,24,25,30,28,6,29) and artikuls.Proba=585 
             then 34
             when ((artikuls.innerartikul like N'01Ш3%' and artikuls.innerartikul not like N'01Ш32%' and artikuls.innerartikul not like N'01Ш33%') or
                   (artikuls.innerartikul like N'01В3%' and artikuls.innerartikul not like N'01В32%' and artikuls.innerartikul not like N'01В33%')) 
                   and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 35
             when ((artikuls.innerartikul like N'01Б3%' and artikuls.innerartikul not like N'01Б32%' and artikuls.innerartikul not like N'01Б33%' )  or (artikuls.innerartikul like N'01Л3%' and artikuls.innerartikul not like N'01Л32%' and artikuls.innerartikul not like N'01Л33%')) and brigada.brigadaID not in (4,32,8,24,25,30,31,3) and artikuls.Proba=585
             then 36
             when (artikuls.innerartikul like N'0Р%' and artikuls.innerartikul not like N'0РБ%' and artikuls.innerartikul not like N'0РЛ%')  and  brigada.brigadaID in (33,2) and artikuls.Proba=585
             then 37
             when (artikuls.innerartikul like N'0РБ%' or artikuls.innerartikul like N'0РЛ%') and  brigada.brigadaID in (33,2) and artikuls.Proba=585 
             then 38
             when (artikuls.innerartikul like N'01О_1%' and artikuls.innerartikul not like N'01О71%') and brigada.brigadaID not in (4,32,8,30) and artikuls.Proba=585
             then 39
             when (artikuls.innerartikul like N'01О_6%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 40
             when (artikuls.innerartikul like N'01О71%') and brigada.brigadaID not in (4,32,8,30) and artikuls.Proba=585 
             then 41
             when (artikuls.innerartikul like N'01Д7%' or artikuls.innerartikul like N'01П7%' ) and brigada.brigadaID in (26) and artikuls.Proba=585
             then 42
             when (artikuls.innerartikul like N'01Д0%' or artikuls.innerartikul like N'01П0%' ) and brigada.brigadaID in (27) and artikuls.Proba=585
             then 43
             when (artikuls.innerartikul like N'01Р0%') and brigada.brigadaID in (27) and artikuls.Proba=585 
             then 44
             when (artikuls.innerartikul like N'01Р7%') and brigada.brigadaID in (26) and artikuls.Proba=585 
             then 45
             when (artikuls.innerartikul like N'01Б%' or artikuls.innerartikul like N'01Л%' or artikuls.innerartikul like N'01Ц%' ) and brigada.brigadaID in (27) and artikuls.Proba=585
             then 46
             when (artikuls.innerartikul like N'01Б7%' or artikuls.innerartikul like N'01Л7%') and brigada.brigadaID in (26) and artikuls.Proba=585
	     then 47
             when ((left(artikuls.innerartikul,3)=N'01Б' and left(artikuls.innerartikul,4)<>N'01Б4' and left(artikuls.innerartikul,5) not in (N'01Б32',N'01Б33'))
             or (left(artikuls.innerartikul,3)=N'01Л' and left(artikuls.innerartikul,4)<>N'01Л4' and left(artikuls.innerartikul,5) not in (N'01Л32',N'01Л33')))
             and brigada.brigadaID not in (4,32,8,24,25,30,26,27,36,19,31,3,39,38) and artikuls.Proba=585 
             then 48
             when (artikuls.innerartikul like N'01Б7%' or artikuls.innerartikul like N'01Л7%') and brigada.brigadaID not in (4,32,8,24,25,30,26,31,3,39) and artikuls.Proba=585
	     then 49
             when ((artikuls.innerartikul like N'01Б%' and artikuls.innerartikul not like N'01Б4%') or (artikuls.innerartikul like N'01Л%' and artikuls.innerartikul not like N'01Л4%') or (artikuls.innerartikul like N'01Ц%' and artikuls.innerartikul not like N'01Ц4%')) and brigada.brigadaID in (31,3) and artikuls.Proba=585
	     then 50
             when (artikuls.innerartikul like N'01Е%') and brigada.brigadaID not in (4,32,8,24,25,30,3,31) and artikuls.Proba=585 
             then 51
             when  (artikuls.innerartikul like N'01Е%') and brigada.brigadaID in (3,31) and artikuls.Proba=585 
             then 52
             when brigada.brigadaID in (32,8) and artikuls.Proba=585 
             then 53
             when brigada.brigadaID in (39) and artikuls.Proba=585 
             then 54
             when (artikuls.innerartikul like N'0___3%' and brigada.brigadaID not in (39)) or (artikuls.innerartikul like N'0___6%' and brigada.brigadaID in (25)) and artikuls.Proba=585
	     then 55
             when (artikuls.innerartikul like N'0___2%') and  brigada.brigadaID not in (39) and artikuls.Proba=585 
             then 56
             when (artikuls.Proba=750) 
             then 57
             when brigada.brigadaID=40
             then 58 
             else 0
        end as Тип,
	 artikuls.innerartikul as Артикул
        ,brigada.vidProizvodstva as brigada
        ,Sum(shtukOut) as [Отгрузка, шт]
        ,Sum(Jornal.vesOut) AS [Отгрузка, гр]
FROM 
       mainEx.dbo.docs as docs with(nolock) 
       INNER JOIN mainEx.dbo.docsPl as docsPl with (nolock) ON docs.docID=docsPl.docID
       INNER JOIN mainEx.dbo.docsDate as docsDate with (nolock) on docsDate.docID = docs.docID
       INNER JOIN mainEx.dbo.Jornal as Jornal with (nolock) ON docsPl.docID=Jornal.docID
        LEFT JOIN attrib.dbo.artikuls artikuls with (nolock) on jornal.artikul=artikuls.artikul and jornal.proba=artikuls.proba
        LEFT JOIN general.dbo.brigada_1 brigada with(nolock) ON isnull(artikuls.brigadaIDm,-1)=brigada.brigadaID 
WHERE 	
        docs.docType=N'накладные' And docsPl.oper=N'отгрузка' And docsPl.movement=N'Отгрузка клиенту' 
        and isnull(docsPl.deleted,0)=0 And docsdate.docExecuteDate>=@Начало_периода
        and DATEDIFF(day,docsdate.docExecuteDate,@Конец_периода)>=0 and docs.toIn<>N'остатки'
        and docsPl.category<>N'БМ.ВОЗВРАТ'  
        and docs.skladout in (select nstr from general.[dbo].[Stroka_vibor] (@Sklad,DEFAULT) where @Sklad<>N'')
        and artikuls.proizvid=1
GROUP BY artikuls.innerartikul,brigada.brigadaID,artikuls.Proba,brigada.vidProizvodstva
) b


select case    
               when  a.Тип in (1,2,3,4,5,6,7,8) 
               then '1  группа'
               when  a.Тип in (9,10,11,12,13) 
               then '2  литьевые изделия без камней'
               when  a.Тип in (14,15,16,17) 
               then '3  изделия с алмазной обработкой'
               when  a.Тип in (18,19,20,21,22) 
               then '4  изделия с фианитами'
               when  a.Тип in (23,24,25) 
               then '5  изделия с цветными фианитами'
               when  a.Тип=58 
               then '51 изделия с муранским стеклом'
               when  a.Тип in (26,27,28,29,30) 
               then '6  изделия с ониксом'
               when  a.Тип in (31,32,33,34,35,36) 
               then '7  изделия с натуральными вставками'
               when  a.Тип in (37,38) 
               then '8  Изделия Роберта'
               when  a.Тип in (39,40,41,42,43,44,45) 
               then '9  Штампованные изделия'
               when  a.Тип in (46,47,48,49) 
               then '91 Цепи, Браслеты и колье'
               when  a.Тип in (50,51,52,53,54) 
               then '92 Изделия ручной работы и машинного плетения'
               when  a.Тип in (55,56,57) 
               then '93 изделия'  
               else '0  без группы'
            end as Группа,
 a.Тип, a.Артикул, a.brigada,a.[Приход, шт], a.[Приход, гр]
into #vz
from
(
SELECT	
         case 
             when ((artikuls.innerartikul like N'01З%' and artikuls.innerartikul not like N'01З4%') or artikuls.innerartikul like N'01Ж%') and (brigada.brigadaID not in (4,32,8,24,25,30,7,23)) and artikuls.Proba=585
             then 1
             when (artikuls.innerartikul like N'01Н%') and (artikuls.innerartikul not like N'01Н4%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23) and artikuls.Proba=585  
             then 2
             when (artikuls.innerartikul like N'01Т%') and (artikuls.innerartikul not like N'01Т4%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23) and artikuls.Proba=585
             then 3
             when (artikuls.innerartikul like N'01Г%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 4
             when (((artikuls.innerartikul like N'01Ш%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23)) or ((artikuls.innerartikul like N'01В%' and artikuls.innerartikul not like N'01В3%') and brigada.brigadaID not in (4,32,8,24,25,30,7,23))) and artikuls.Proba=585 
             then 5
	     when (artikuls.innerartikul like N'01С%') and artikuls.Proba=585  and  brigada.brigadaID in (28,11) 
             then 6
             when (artikuls.innerartikul like N'01И%') and (artikuls.innerartikul not like N'01И3%')  and  brigada.brigadaID in (28) and artikuls.Proba=585 
             then 7
             when  brigada.brigadaID in (4,30) and artikuls.Proba=585 
             then 8
             when (artikuls.innerartikul like N'01Д0%')  and artikuls.Proba=585 and brigada.brigadaID not in (4,32,8,24,25,30,27) 
             then 9
             when (artikuls.innerartikul like N'01П0%') and brigada.brigadaID not in (4,32,8,24,25,30,27) and artikuls.Proba=585 
             then 10
             when (artikuls.innerartikul like N'01Р0%') and brigada.brigadaID not in (4,32,8,24,25,30,27) and artikuls.Proba=585 
             then 11
             when (artikuls.innerartikul like N'01К0%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 12
             when (artikuls.innerartikul like N'01С0%') and brigada.brigadaID not in (4,32,8,24,25,30,39) and artikuls.Proba=585 
             then 13
             when (artikuls.innerartikul like N'01П7%') and brigada.brigadaID not in (4,32,8,24,25,30,26) and artikuls.Proba=585 
             then 14
             when (artikuls.innerartikul like N'01Р7%') and brigada.brigadaID  in (3,31) and artikuls.Proba=585 
             then 15
             when (artikuls.innerartikul like N'01К7%')  and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 16
             when (artikuls.innerartikul like N'01С7%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39) and artikuls.Proba=585 
             then 17
             when (artikuls.innerartikul like N'01Д1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 18
             when (artikuls.innerartikul like N'01П1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 19
             when (artikuls.innerartikul like N'01Р1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 20
             when (artikuls.innerartikul like N'01К1%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 21
             when (artikuls.innerartikul like N'01С1%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39) and artikuls.Proba=585 
             then 22
             when (artikuls.innerartikul like N'01П2%' or artikuls.innerartikul like N'01Р2%' ) and brigada.brigadaID not in (4,32,8,24,25,30,40) and artikuls.Proba=585
             then 23
             when (artikuls.innerartikul like N'01К2%') and brigada.brigadaID not in (4,32,8,24,25,30,40) and artikuls.Proba=585 
             then 24
             when (artikuls.innerartikul like N'01С2%') and brigada.brigadaID not in (4,32,8,24,25,30,28,39,40) and artikuls.Proba=585 
             then 25
             when (artikuls.innerartikul like N'01Т4%' or artikuls.innerartikul like N'01К4%' ) and brigada.brigadaID not in (4,32,8,24,25,30,23,7) and artikuls.Proba=585
             then 26
             when (artikuls.innerartikul like N'01П4%' or artikuls.innerartikul like N'01Р4%' ) and brigada.brigadaID not in (4,32,8,24,25,30,23,7) and artikuls.Proba=585
             then 27
             when (artikuls.innerartikul like N'01З4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 28
             when (artikuls.innerartikul like N'01Б4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 29
             when (artikuls.innerartikul like N'01Н4%' ) and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 30
             when (artikuls.innerartikul like N'01И3%') and  brigada.brigadaID in (28) and artikuls.Proba=585 
             then 31
             when (((artikuls.innerartikul like N'01П3%' or artikuls.innerartikul like N'01П4%') and artikuls.innerartikul not like N'01П32%' and artikuls.innerartikul not like N'01П33%' )  or ((artikuls.innerartikul like N'01Р3%' or artikuls.innerartikul like N'01Р4%') and artikuls.innerartikul not like N'01Р32%' and artikuls.innerartikul not like N'01Р33%' )) and brigada.brigadaID not in (4,32,8,24,25,30,6,29) and artikuls.Proba=585
             then 32
             when ((artikuls.innerartikul like N'01Т3%' and artikuls.innerartikul not like N'01Т32%' and artikuls.innerartikul not like N'01Т33%' )  or ((artikuls.innerartikul like N'01К3%' or artikuls.innerartikul like N'01К4%') and artikuls.innerartikul not like N'01К32%' and artikuls.innerartikul not like N'01К33%' )) and brigada.brigadaID not in (4,32,8,24,25,30,6,29) and artikuls.Proba=585
             then 33
             when ((artikuls.innerartikul like N'01С3%' and artikuls.innerartikul not like N'01С32%' and artikuls.innerartikul not like N'01С33%' )  or (artikuls.innerartikul like N'01С4%'))
                   and brigada.brigadaID not in (4,32,8,24,25,30,28,6,29) and artikuls.Proba=585 
             then 34
             when ((artikuls.innerartikul like N'01Ш3%' and artikuls.innerartikul not like N'01Ш32%' and artikuls.innerartikul not like N'01Ш33%') or
                   (artikuls.innerartikul like N'01В3%' and artikuls.innerartikul not like N'01В32%' and artikuls.innerartikul not like N'01В33%')) 
                   and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 35
             when ((artikuls.innerartikul like N'01Б3%' and artikuls.innerartikul not like N'01Б32%' and artikuls.innerartikul not like N'01Б33%' )  or (artikuls.innerartikul like N'01Л3%' and artikuls.innerartikul not like N'01Л32%' and artikuls.innerartikul not like N'01Л33%')) and brigada.brigadaID not in (4,32,8,24,25,30,31,3) and artikuls.Proba=585
             then 36
             when (artikuls.innerartikul like N'0Р%' and artikuls.innerartikul not like N'0РБ%' and artikuls.innerartikul not like N'0РЛ%')  and  brigada.brigadaID in (33,2) and artikuls.Proba=585
             then 37
             when (artikuls.innerartikul like N'0РБ%' or artikuls.innerartikul like N'0РЛ%') and  brigada.brigadaID in (33,2) and artikuls.Proba=585 
             then 38
             when (artikuls.innerartikul like N'01О_1%' and artikuls.innerartikul not like N'01О71%') and brigada.brigadaID not in (4,32,8,30) and artikuls.Proba=585
             then 39
             when (artikuls.innerartikul like N'01О_6%') and brigada.brigadaID not in (4,32,8,24,25,30) and artikuls.Proba=585 
             then 40
             when (artikuls.innerartikul like N'01О71%') and brigada.brigadaID not in (4,32,8,30) and artikuls.Proba=585 
             then 41
             when (artikuls.innerartikul like N'01Д7%' or artikuls.innerartikul like N'01П7%' ) and brigada.brigadaID in (26) and artikuls.Proba=585
             then 42
             when (artikuls.innerartikul like N'01Д0%' or artikuls.innerartikul like N'01П0%' ) and brigada.brigadaID in (27) and artikuls.Proba=585
             then 43
             when (artikuls.innerartikul like N'01Р0%') and brigada.brigadaID in (27) and artikuls.Proba=585 
             then 44
             when (artikuls.innerartikul like N'01Р7%') and brigada.brigadaID in (26) and artikuls.Proba=585 
             then 45
             when (artikuls.innerartikul like N'01Б%' or artikuls.innerartikul like N'01Л%' or artikuls.innerartikul like N'01Ц%' ) and brigada.brigadaID in (27) and artikuls.Proba=585
             then 46
             when (artikuls.innerartikul like N'01Б7%' or artikuls.innerartikul like N'01Л7%') and brigada.brigadaID in (26) and artikuls.Proba=585
	      then 47
             when ((left(artikuls.innerartikul,3)=N'01Б' and left(artikuls.innerartikul,4)<>N'01Б4' and left(artikuls.innerartikul,5) not in (N'01Б32',N'01Б33'))
             or (left(artikuls.innerartikul,3)=N'01Л' and left(artikuls.innerartikul,4)<>N'01Л4' and left(artikuls.innerartikul,5) not in (N'01Л32',N'01Л33')))
             and brigada.brigadaID not in (4,32,8,24,25,30,26,27,36,19,31,3,39,38) and artikuls.Proba=585 
             then 48
             when (artikuls.innerartikul like N'01Б7%' or artikuls.innerartikul like N'01Л7%') and brigada.brigadaID not in (4,32,8,24,25,30,26,31,3,39) and artikuls.Proba=585
	     then 49
             when ((artikuls.innerartikul like N'01Б%' and artikuls.innerartikul not like N'01Б4%') or (artikuls.innerartikul like N'01Л%' and artikuls.innerartikul not like N'01Л4%') or (artikuls.innerartikul like N'01Ц%' and artikuls.innerartikul not like N'01Ц4%')) and brigada.brigadaID in (31,3) and artikuls.Proba=585
	     then 50
             when (artikuls.innerartikul like N'01Е%') and brigada.brigadaID not in (4,32,8,24,25,30,3,31) and artikuls.Proba=585 
             then 51
             when  (artikuls.innerartikul like N'01Е%') and brigada.brigadaID in (3,31) and artikuls.Proba=585 
             then 52
             when brigada.brigadaID in (32,8) and artikuls.Proba=585 
             then 53
             when brigada.brigadaID in (39) and artikuls.Proba=585 
             then 54
             when (artikuls.innerartikul like N'0___3%' and brigada.brigadaID not in (39)) or (artikuls.innerartikul like N'0___6%' and brigada.brigadaID in (25)) and artikuls.Proba=585
	      then 55
             when (artikuls.innerartikul like N'0___2%') and  brigada.brigadaID not in (39) and artikuls.Proba=585 
             then 56
             when (artikuls.Proba=750) 
             then 57
             when brigada.brigadaID=40
             then 58 
             else 0
         end as Тип,
		artikuls.innerartikul as Артикул,brigada.vidProizvodstva as brigada,
		Sum(isnull(shtukIn,0)) as [Приход, шт],
		Sum(isnull(Jornal.vesIn,0)) AS [Приход, гр]
FROM 	
        mainEx.dbo.docs as docs WITH( NOLOCK ) 
        INNER JOIN mainEx.dbo.docsPl as docsPl WITH( NOLOCK ) ON docs.docID=docsPl.docID 
        INNER JOIN mainEx.dbo.docsDate as docsDate WITH( NOLOCK ) on docsDate.docID = docs.docID 
        INNER JOIN mainEx.dbo.Jornal as Jornal WITH( NOLOCK ) ON docsPl.docID=Jornal.docID 
        LEFT JOIN attrib.dbo.artikuls as artikuls WITH( NOLOCK ) ON Jornal.artikul=artikuls.Artikul and jornal.proba=artikuls.proba
        LEFT JOIN general.dbo.brigada_1 brigada with(nolock) ON isnull(artikuls.brigadaIDm,-1)=brigada.brigadaID  
WHERE   movement not like '%переупак%' and movement not like '%ремонт%' and movement not like '%брак%' and komment not like '%переупак%' 
        and komment not like '%ремонт%' and komment not like '%брак%' and 
        docs.docType = N'приходники' And docsPl.oper in (N'ПРИЁМКА ИЗДЕЛИЙ', N'Приемка') 
        and isnull(docsPl.deleted,0)=0	
        And  docexecutedate>=@Начало_периода and DATEDIFF(day, docexecutedate,@Конец_периода)>=0 
        And docs.toIn<>N'остатки' and subjecttype='организации' 
	and departin in ('ПОСТАВЩИКИ','ПОДРЯДЧИКИ','ПРОИЗВОДСТВО')
	and docs.skladout in (select nstr from general.[dbo].[Stroka_vibor] (@Sklad,DEFAULT) where @Sklad<>N'')
	    AND artikuls.proizvid=1
	   
GROUP BY artikuls.innerartikul, brigada.brigadaID,artikuls.Proba,brigada.vidProizvodstva
) a 




select 
stuff(coalesce(c.Группа,d.Группа),1,3,'') as Группа1
,coalesce(c.Группа,d.Группа) as Группа
,coalesce(c.Тип,d.Тип) as Тип1
,Typ.typ as Тип
,coalesce(c.артикул,d.Артикул) as Артикул
,coalesce(c.brigada,d.brigada) as участок
,isnull(c.[Отгрузка, шт],0) as [Отгрузка, шт]
,isnull(c.[Отгрузка, гр],0) as [Отгрузка, гр]
,isnull(d.[Приход, шт],0) as [Приход, шт]
,isnull(d.[Приход, гр],0) as [Приход, гр]
from
#ot as c
FULL JOIN #vz as d 
on c.Тип=d.Тип and c.Артикул=d.Артикул
LEFT JOIN general.san.TypIzd as typ with(nolock) on typ.nomer=coalesce(c.Тип,d.Тип)
ORDER BY  Группа, Тип1, Артикул

Drop table #ot
drop table #vz

Все Case мне нужны. ну когда один Case закомментирую, тогда срабатывает. Я так понял нужно упростить запрос.. но как..он то сам не сложный..только очень много CASE. Что посоветуйте?
3 дек 09, 16:57    [8015808]     Ответить | Цитировать Сообщить модератору
 Re: Internal error: An expression services limit has been reached. Please look for potentially  [new]
Glory
Member

Откуда:
Сообщений: 104751
minya13_85

Все Case мне нужны. ну когда один Case закомментирую, тогда срабатывает. Я так понял нужно упростить запрос.. но как..он то сам не сложный..только очень много CASE. Что посоветуйте?

Заведите нормальную таблицу-справочник, а не пишите ек каждый раз в тексте запроса
3 дек 09, 17:00    [8015838]     Ответить | Цитировать Сообщить модератору
 Re: Internal error: An expression services limit has been reached. Please look for potentially  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Все Case мне нужны. ну когда один Case закомментирую, тогда срабатывает.
> Я так понял нужно упростить запрос.. но как..он то сам не
> сложный..только очень много CASE. Что посоветуйте?

подзапрос
> ) a
во временную таблицу сбросить.

Posted via ActualForum NNTP Server 1.4

3 дек 09, 17:00    [8015839]     Ответить | Цитировать Сообщить модератору
 Re: Internal error: An expression services limit has been reached. Please look for potentially  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Вот не зря говорят, что программист по натуре должен быть ленивым, дабы думать головой, а не писать такой говнокод.
3 дек 09, 17:28    [8016061]     Ответить | Цитировать Сообщить модератору
 Re: Internal error: An expression services limit has been reached. Please look for potentially  [new]
проходящий.
Guest
minya13_85
Все Case мне нужны.
Это очень спорное утверждение. Все их содержимое просится в таблицы. С соответсвующим, прямо таки волшебным, упрощением запроса и предоставлением оптимизатору недостающих для быстрого выполнения данных.
3 дек 09, 17:33    [8016092]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Internal error: An expression services limit has been reached. Please look for potentially  [new]
Igorekss
Guest
Подобная ошибка в 1С 8.1, на локальной копии всё работает, а на сервере не работает, в итоге разбил запрос на 4 части. всё заработало :)
26 дек 11, 16:15    [11827710]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить