|
При попытке запустить продцедуру получаю ошибку :
Cannot resolve collation conflict for equal to operation. не понимаю что это значит ...
вроде мне казалось что это связанно с одинаковым названием полей в таблицах участвующих в запросе, но ни использование префиксов, ни смена названий полей не помогла
Продцедура:
DECLARE @SDate datetime
DECLARE @EDate datetime
SET @SDate=Cast('2001-11-01' as datetime)
SET @EDate=Cast('2001-11-20' as datetime)
--SET NOCOUNT ON
CREATE TABLE #SPDetail(Lagr_id INT PRIMARY KEY, User_id varchar(25), FixDate datetime, AgrStatus tinyint, AgrCDate datetime, DisburseDate datetime, FinLVL money)
INSERT INTO #SPDetail
SELECT a.Lagr_id,a.User_id, a.FixDate, CASE
WHEN a.TakeOverFrom_id IS NOT NULL and a.Status=10 THEN 11
ELSE a.Status
END as AgrStatus, a.AgrCDate,
ISNULL(CASE
WHEN a.Status IN (12,13,14,15,16) THEN (SELECT MAX(c.ChDate) FROM LAgrStatusChanges c WHERE c.LAgr_id=a.LAgr_id and c.Status=a.Status)
ELSE a.DisburseDate
END,GetDate()) as DisburseDate,
ROUND(dbo.fL_GetAgrFin(a.LAgr_id)*dbo.fL_GetLBRateLVL(a.GraphCurr,@EDate),2)
as FinLVL
FROM LAgreements a
WHERE a.Status>1 and a.User_id IN (SELECT u.User_id FROM Users u WHERE u.Post='MNG')
/*
SELECT sm.SignedPrev+sm.SignedIn+sm.ActivePrev+sm.ActiveIn as SignedTotal,
sm.DisbursedPrev+sm.DisbursedIn+sm.TakeOver as DisbursedTotal,
sm.SignedIn+sm.DisbursedIn+sm.TakeOver as SignedDisbInTotal
FROM
(
*/
-- Ошибка происходит тут !
SELECT
m.name + m.surname as UserName, m.Dep,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus=2),0) AS Project,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus IN (5,6)),0) AS Committee,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus IN (3,4)),0) AS Refused,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus=7 and AgrCDate<@SDate),0) AS SignedPrev,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus=7 and AgrCDate>=@SDate and AgrCDate<=@EDate),0) AS SignedIn,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus=8 and AgrCDate<@SDate),0) AS ActivePrev,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus=8 and AgrCDate>=@SDate and AgrCDate<=@EDate),0) AS ActiveIn,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus IN (9,10) and AgrCDate<@SDate and DisburseDate>=@SDate and DisburseDate<=@EDate),0) AS DisbursedPrev,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus IN (9,10) and AgrCDate>=@SDate and AgrCDate<=@EDate and DisburseDate>=@SDate and DisburseDate<=@EDate),0) AS DisbursedIn,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus = 11 and DisburseDate>=@SDate and DisburseDate<=@EDate),0) AS TakeOver,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus = 13 and DisburseDate>=@SDate and DisburseDate<=@EDate),0) AS Stopped,
ISNULL((SELECT SUM(d.FinLVL) FROM #SPDetail d
WHERE d.User_id=m.user_id and d.AgrStatus IN (12,14,15,16) and DisburseDate>=@SDate and DisburseDate<=@EDate),0) AS Ended
FROM Users m
WHERE m.Post='MNG' and m.Status>0
--) as sm
ORDER BY m.Dep
-- Такой запрос выполняеться без проблем
--SELECT * FROM #SPDetail
DROP TABLE #SPDetail
P.S. Использую MS SQL 2000
|
