19.0升级报错：【库存(ST)】模块的SQL 脚本 19.000.000.0181 DATA33001

【问题现象】：

19.0升级182号以上补丁，报错：【库存(ST)】模块的SQL 脚本: 19.000.000.0181 DATA_DPRD_ST_Mix_SERVICE_177090_NL-33001 出错：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

【解决方案】：

先备份好账套，进入数据库，对应用账套库执行下列语句：（全部复制过去，一起执行）

执行完后，接着升级即可；

脚本：

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

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