账套升级报错19.000.000.0098 DATA DSTR ST MIX_SERVICE_165186_NL-14001出错:子查询返回的值不止一个，当子查询跟随在=、!=、<、<=、>=之后，或子查询用作表达式时，这种情况是不允许的。

【问题现象】

账套升级报错

19.000.000.0098 DATA DSTR ST MIX_SERVICE_165186_NL-14001出错:子查询返回的值不止一个，当子查询跟随在=、!=、<、<=、>=之后，或子查询用作表达式时，这种情况是不允许的。

如下图

【解决方案】

使用下方脚本前请做好数据备份

先恢复账套后，执行下方脚本再升级账套。

----补丁脚本----

GO

--批量修改字段顺序

ALTER PROCEDURE [dbo].[EAP_SP_SearchItem_BatchSetLocation]

@SearchName nvarchar(200),

@PreFieldName nvarchar(200),

@FieldNames nvarchar(1000) --字段名称 以,分割

AS

BEGIN

declare @SearchId int

declare @PreOrderNum int

declare @UpdateNum int

declare @TEMPTable TABLE ( a varchar(100),b int )

SELECT @SearchId=[Id] FROM [EAP_SearchInfo_Ext] WHERE [name]=@SearchName

INSERT @TEMPTable(a,b) SELECT a,b FROM dbo.Str_Split(@FieldNames,',') order by b

SELECT @UpdateNum = count(*) FROM @TEMPTable

IF(@PreFieldName='')

BEGIN

SET @PreOrderNum = (select MIN([OrderNum]) FROM [EAP_SearchItem_Ext] where [SearchId]=@SearchId )

--@SearchName对应的所有查询项[OrderNum]字段值都加+ @UpdateNum

UPDATE [EAP_SearchItem_Ext]

SET [OrderNum]=[OrderNum]+ @UpdateNum

WHERE [SearchId]=@SearchId

--@SearchName中的查询项(@FieldName)的[OrderNum]设置为最小值

UPDATE si SET si.OrderNum = @PreOrderNum+ (t.b-1)

FROM [EAP_SearchItem_Ext] si,@TEMPTable t

WHERE si.[FieldName] = t.a

and si.[SearchId]=@SearchId

UPDATE EAP_SearchPlanItem_Ext

SET OrderNum = OrderNum + @UpdateNum

FROM EAP_SearchPlanItem_Ext

inner join EAP_SearchPlanInfo_Ext on EAP_SearchPlanItem_Ext.planid = EAP_SearchPlanInfo_Ext.id

WHERE OrderNum>@PreOrderNum and EAP_SearchPlanInfo_Ext.SearchId = @SearchId and EAP_SearchPlanInfo_Ext.SearchStyle<>3

RETURN

END

ELSE

BEGIN

SET @PreOrderNum = (select top 1 [OrderNum] from [EAP_SearchItem_Ext] where [SearchId]=@SearchId and [FieldName]=@PreFieldName)

UPDATE [EAP_SearchItem_Ext]

set [OrderNum]=[OrderNum]+@UpdateNum

where [OrderNum]>@PreOrderNum and [SearchId]=@SearchId

UPDATE si SET si.OrderNum = @PreOrderNum+ (t.b)

FROM [EAP_SearchItem_Ext] si,@TEMPTable t

WHERE si.[FieldName] = t.a

and si.[SearchId]=@SearchId

UPDATE EAP_SearchPlanItem_Ext

SET OrderNum = OrderNum + @UpdateNum

FROM EAP_SearchPlanItem_Ext

inner join EAP_SearchPlanInfo_Ext on EAP_SearchPlanItem_Ext.planid = EAP_SearchPlanInfo_Ext.id

WHERE OrderNum>@PreOrderNum and EAP_SearchPlanInfo_Ext.SearchId = @SearchId and EAP_SearchPlanInfo_Ext.SearchStyle<>3

END

UPDATE pitem set pitem.OrderNum = si.OrderNum

FROM EAP_SearchPlanItem_Ext pitem

join EAP_SearchPlanInfo_Ext sp on pitem.planid = sp.id

join EAP_SearchItem_Ext si on pitem.SearchItemId = si.id

WHERE sp.SearchId = @SearchId and si.FieldName in(select a from @TEMPTable) and sp.SearchStyle<>3

END ; ;

GO