求解决
- SQL code
;with cte as(select [SRV_PART_NO],[HW_PART_NO]=substring(case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end,1,charindex(';',case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end)-1),col3=stuff(case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end,1,charindex(';',case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end),'') from win7_32union allselect [SRV_PART_NO],[HW_PART_NO]=substring(col3,1,charindex(';',col3)-1),col3=stuff(col3,1,charindex(';',col3),'') from ctewhere charindex(';',col3)>0)--insert into [Commdity Split] ([HW Part No],[HW_PART_NO])select distinct [SRV_PART_NO],[HW_PART_NO] from cte
报错
Types don't match between the anchor and the recursive part in column "HW_PART_NO" of recursive query "cte".
------解决方案--------------------
try:
- SQL code
;with cte as(select [SRV_PART_NO],[HW_PART_NO]=cast(substring(case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end,1,charindex(';',case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end)-1) as varchar(2000)),col3=cast(stuff(case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end,1,charindex(';',case when right([HW_PART_NO],1)=';' then [HW_PART_NO] else [HW_PART_NO]+';' end),'') as varchar(2000)) from win7_32union allselect [SRV_PART_NO],[HW_PART_NO]=cast(substring(col3,1,charindex(';',col3)-1),col3=stuff(col3,1,charindex(';',col3),'') as varchar(2000)) from ctewhere charindex(';',col3)>0)--insert into [Commdity Split] ([HW Part No],[HW_PART_NO])select distinct [SRV_PART_NO],[HW_PART_NO] from cte
------解决方案--------------------