CASE WHEN

Neron777
Дата: 21.05.2015 09:30:08
Здравствуйте, форумчане!
Вопрос, есть запрос
select 
   g.Ku as Kod_Tov
 , RTRIM(g.nmp) as nmp
 , n.kol_ras as kol_tov
 , n.cen_re  
 , n.sumkon
 , r.dat_r as Date
 , r.nakl as Nakl
 , RTRIM(k.NA_KLI) as NA_KLI
 , r.sum_v as Summ
 , r.sklad as Sklad
 from  dbo.vwRash r                          
 INNER JOIN dbo.vwNomenk n ON r.isrash=n.isrash and r.sklad=n.sklad and n.nttn=r.nttn         
 LEFT OUTER JOIN KLIENT k on k.COD_KLI = r.cod_kli      
 LEFT OUTER JOIN dbo.vwKlasf g ON g.sklad=r.sklad and g.KU = n.KU
 LEFT OUTER JOIN Employees e on k.COD_KLI = e.ClientId  
 LEFT OUTER JOIN (SELECT eid.Id, eph.EmployeeId, eph.PosName, eph.FirmId, RTRIM(eph.FirmName) AS firmName
, eph.StoreId, eph.PosId  
FROM (SELECT EmployeeId, MIN(Id) AS Id FROM dbo.vwEmployeePosHistory
WHERE(IsActive = 1)
GROUP BY EmployeeId) AS eid 
INNER JOIN dbo.vwEmployeePosHistory AS eph ON eid.Id = eph.Id) AS eph_1 ON e.Id = eph_1.EmployeeId  
 Where 0=0 
  AND r.dat_r BETWEEN {d '2015-02-20'} and {d '2015-05-20'}  
  AND r.vid_ras = @Vid_ras     
  AND CASE WHEN @Sklad=-1 THEN -1 ELSE r.sklad END = @Sklad
  AND CASE WHEN @IDRec is not Null THEN eph_1.PosId IN(SELECT DataId FROM dbo.MultiSelectReport WHERE UserId = @custn and ReportId=@ReportId and ArgId=@ArgId) END
ORDER BY Sklad, Date, Nakl, Na_KLI, nmp


проблемы со строкой
  AND CASE WHEN @IDRec is not Null THEN eph_1.PosId IN(SELECT DataId FROM dbo.MultiSelectReport WHERE UserId = @custn and ReportId=@ReportId and ArgId=@ArgId) END


пишет Неправильный синтаксис около ключевого слова "IN".
никак не могу решить эту проблему. Нужно что бы в зависимости от значения @IDRec , либо добавлялась строка эта, либо пропускалась.
Glory
Дата: 21.05.2015 09:34:08
Neron777
никак не могу решить эту проблему.

После THEN должно стоять выражение, а не "кусок запроса"
Кролик-зануда
Дата: 21.05.2015 09:35:26
Neron777,

вам нужен OR, а не CASE
AlanDenton
Дата: 21.05.2015 09:41:43
SELECT 
	  g.Ku as Kod_Tov
	, RTRIM(g.nmp) as nmp
	, n.kol_ras as kol_tov
	, n.cen_re  
	, n.sumkon
	, r.dat_r as Date
	, r.nakl as Nakl
	, RTRIM(k.NA_KLI) as NA_KLI
	, r.sum_v as Summ
	, r.sklad as Sklad
FROM dbo.vwRash r                          
JOIN dbo.vwNomenk n ON r.isrash=n.isrash and r.sklad=n.sklad and n.nttn=r.nttn         
LEFT JOIN KLIENT k on k.COD_KLI = r.cod_kli      
LEFT JOIN dbo.vwKlasf g ON g.sklad=r.sklad and g.KU = n.KU
LEFT JOIN Employees e on k.COD_KLI = e.ClientId  
LEFT JOIN (
	SELECT eid.Id, eph.EmployeeId, eph.PosName, eph.FirmId, RTRIM(eph.FirmName) AS firmName, eph.StoreId, eph.PosId  
	FROM (
		SELECT EmployeeId, MIN(Id) AS Id
		FROM dbo.vwEmployeePosHistory
		WHERE IsActive = 1
		GROUP BY EmployeeId
	) AS eid 
	JOIN dbo.vwEmployeePosHistory AS eph ON eid.Id = eph.Id
) AS eph_1 ON e.Id = eph_1.EmployeeId  
WHERE r.dat_r BETWEEN '20150220' and '20150520'
	AND r.vid_ras = @Vid_ras     
	AND CASE WHEN @Sklad=-1 THEN -1 ELSE r.sklad END = @Sklad
	AND
	(
			@IDRec is not NULL
		OR
			eph_1.PosId IN(
				SELECT DataId
				FROM dbo.MultiSelectReport
				WHERE UserId = @custn and ReportId=@ReportId and ArgId=@ArgId
			)
	)
ORDER BY Sklad, Date, Nakl, Na_KLI, nmp


Что-то типа такого?