--返回一个整理好的表
create function dbo.MF_NewCustomerLocation_Function()
returns @Tmp_Table_2 table
(
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))
as
begin--用做临时表,存放非自然城市的CustomerLocation的基础信息,便于循环数据
declare @Tmp_Table_1 table
(
ID int identity,
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
RegionLevel int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))--当循环数据时,临时存放关联的结果
declare @Tmp_Table_3 table
(
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
RegionLevel int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))--CustomerRegion数据应该是有效IsVoid='N'
insert @Tmp_Table_1(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
SELECT TMP.CustID,TMP.CustCode,CT.MobnetCustomerTypeID AS CustType,
SR.RegionID AS LocationID,TMP.LocationCode,SR.RegionLevel,TMP.RMDFlag,
TMP.CreateUser,TMP.CreateDate,TMP.ModifyUser,TMP.ModifyDate,TMP.IsVoid
FROM
(
SELECT CR.[CustID] as CustID,SC.[CustCode] as CustCode,
CR.[RegionCode] as LocationCode,
CR.[CreateUser], CR.[CreateDate], CR.[ModifyUser], CR.[ModifyDate], CR.[IsVoid],
--判断是否是RMD,将所有RMD的CustTypeID都转换成3
(case when SC.CustTypeID = 4 then 3 else SC.CustTypeID end ) as CustTypeID,CR.RMDFlag
FROM SEM_CustomerRegion CR
LEFT JOIN SEM_Customer SC ON SC.CustID = CR.CustID
where CR.[RegionCode] !='national'
) TMP
left JOIN SEM_CustomerType CT ON CT.CustomerTypeID = TMP.CustTypeID
LEFT JOIN SEM_Region SR ON SR.RegionCode = TMP.LocationCode
where SR.regionlevel != 4 and TMP.IsVoid = 'N'
order by SR.regionlevel desc--循环数据
--初始化@i
declare @i int
set @i=1while @i<=(select max(ID) from @Tmp_Table_1)
begin--先将按ID提取的数据放入@Tmp_Table_3中
insert @Tmp_Table_3(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select CustID,CustCode collate chinese_prc_CS_Ai,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid
from @Tmp_Table_1
where ID = @i--注意:Level值只能是一种级别
declare @RegionLevel_3 int
set @RegionLevel_3 = (select distinct RegionLevel from @Tmp_Table_3)--通过判断RegionLevel,关联出自然城市
while @RegionLevel_3 <(select max(regionlevel) from sem_region)
begin--关联出来的LocationCode也必须是有效的IsVoid ='N'/*总是这里报错Cannot resolve collation conflict for equal to operation.为什么呢??*/
insert @Tmp_Table_3(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select a.CustID,a.CustCode,a.CustType,c.regionid,c.regioncode,c.RegionLevel,a.RMDFlag,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate,a.IsVoid
from @Tmp_Table_3 a left join sem_region b on b.regioncode = a.LocationCode left join sem_region c on b.regioncode = c.parentcode
where c.isvoid = 'n'set @RegionLevel_3 = @RegionLevel_3 + 1
end
--将最终结果插入@Tmp_Table_2中
insert @Tmp_Table_2(CustID,CustCode,CustType,LocationID,LocationCode,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select CustID,CustCode,CustType,LocationID,LocationCode,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid
from @Tmp_Table_3
where regionlevel = @RegionLevel_3--清空@Tmp_Table_3的数据
delete @Tmp_Table_3--取得下一个ID的数据
set @i = @i+1
end--返回结果
return;end
为什么在有的查询分析器中没有问题,有些就报Cannot resolve collation conflict for equal to operation.这个错呢?
先谢谢各位了
create function dbo.MF_NewCustomerLocation_Function()
returns @Tmp_Table_2 table
(
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))
as
begin--用做临时表,存放非自然城市的CustomerLocation的基础信息,便于循环数据
declare @Tmp_Table_1 table
(
ID int identity,
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
RegionLevel int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))--当循环数据时,临时存放关联的结果
declare @Tmp_Table_3 table
(
CustID int,
CustCode varchar(50),
CustType int,
LocationID int,
LocationCode varchar(50),
RMDFlag int,
RegionLevel int,
CreateUser varchar(50),
CreateDate datetime,
ModifyUser varchar(50),
ModifyDate datetime,
IsVoid char(1))--CustomerRegion数据应该是有效IsVoid='N'
insert @Tmp_Table_1(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
SELECT TMP.CustID,TMP.CustCode,CT.MobnetCustomerTypeID AS CustType,
SR.RegionID AS LocationID,TMP.LocationCode,SR.RegionLevel,TMP.RMDFlag,
TMP.CreateUser,TMP.CreateDate,TMP.ModifyUser,TMP.ModifyDate,TMP.IsVoid
FROM
(
SELECT CR.[CustID] as CustID,SC.[CustCode] as CustCode,
CR.[RegionCode] as LocationCode,
CR.[CreateUser], CR.[CreateDate], CR.[ModifyUser], CR.[ModifyDate], CR.[IsVoid],
--判断是否是RMD,将所有RMD的CustTypeID都转换成3
(case when SC.CustTypeID = 4 then 3 else SC.CustTypeID end ) as CustTypeID,CR.RMDFlag
FROM SEM_CustomerRegion CR
LEFT JOIN SEM_Customer SC ON SC.CustID = CR.CustID
where CR.[RegionCode] !='national'
) TMP
left JOIN SEM_CustomerType CT ON CT.CustomerTypeID = TMP.CustTypeID
LEFT JOIN SEM_Region SR ON SR.RegionCode = TMP.LocationCode
where SR.regionlevel != 4 and TMP.IsVoid = 'N'
order by SR.regionlevel desc--循环数据
--初始化@i
declare @i int
set @i=1while @i<=(select max(ID) from @Tmp_Table_1)
begin--先将按ID提取的数据放入@Tmp_Table_3中
insert @Tmp_Table_3(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select CustID,CustCode collate chinese_prc_CS_Ai,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid
from @Tmp_Table_1
where ID = @i--注意:Level值只能是一种级别
declare @RegionLevel_3 int
set @RegionLevel_3 = (select distinct RegionLevel from @Tmp_Table_3)--通过判断RegionLevel,关联出自然城市
while @RegionLevel_3 <(select max(regionlevel) from sem_region)
begin--关联出来的LocationCode也必须是有效的IsVoid ='N'/*总是这里报错Cannot resolve collation conflict for equal to operation.为什么呢??*/
insert @Tmp_Table_3(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select a.CustID,a.CustCode,a.CustType,c.regionid,c.regioncode,c.RegionLevel,a.RMDFlag,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate,a.IsVoid
from @Tmp_Table_3 a left join sem_region b on b.regioncode = a.LocationCode left join sem_region c on b.regioncode = c.parentcode
where c.isvoid = 'n'set @RegionLevel_3 = @RegionLevel_3 + 1
end
--将最终结果插入@Tmp_Table_2中
insert @Tmp_Table_2(CustID,CustCode,CustType,LocationID,LocationCode,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select CustID,CustCode,CustType,LocationID,LocationCode,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid
from @Tmp_Table_3
where regionlevel = @RegionLevel_3--清空@Tmp_Table_3的数据
delete @Tmp_Table_3--取得下一个ID的数据
set @i = @i+1
end--返回结果
return;end
为什么在有的查询分析器中没有问题,有些就报Cannot resolve collation conflict for equal to operation.这个错呢?
先谢谢各位了
解决方案 »
- 请教两个表来更新一个表方法
- 我的这个存储过程怎么得不到值啊
- 有人碰到过这个问题吗?太夸张了。SQL SERVER问题。
- SQL-SERVER的数据同步可以在2台SQL-SERVER之间同步数据,那有什么方法可以在ACCESS和SQL-SERVER之间同步数据呢?我想把SQL-SERVER的数据同
- sqlserver 行转列问题求高人指点
- DTS里我想创建一个行集的全局变量,但是不行。
- 如何把本地的在*.bak文件恢复到远程SQL SERVER 2000数据库里?
- 初学:在查询分析器中出现:“列名 'Green' 无效。”错误是怎么回事?
- 有个合并数据的程序优化问题!!!!
- 关联表的查询
- 紧急,救命数据库的问题!
- 装了KV2007后SQL Server7.0不能自动备份了。
collation conflict应该是字符集冲突,看看Server的字符集,表上的字符集是否一致
insert @Tmp_Table_3(CustID,CustCode,CustType,LocationID,LocationCode,RegionLevel,RMDFlag,CreateUser,CreateDate,ModifyUser,ModifyDate,IsVoid)
select a.CustID,a.CustCode,a.CustType,c.regionid,c.regioncode,c.RegionLevel,a.RMDFlag,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate,a.IsVoid
from @Tmp_Table_3 a left join sem_region b on b.regioncode = a.LocationCode COLLATE SQL_Latin1_General_CP1_CI_AS left join sem_region c on b.regioncode = c.parentcode COLLATE SQL_Latin1_General_CP1_CI_AS
where c.isvoid = 'n'