【問題現(xiàn)象】：

19.0升級182號以上補丁，報錯：【庫存(ST)】模塊的SQL 腳本: 19.000.000.0181 DATA_DPRD_ST_Mix_SERVICE_177090_NL-33001 出錯：子查詢返回的值不止一個。當子查詢跟隨在 =、!=、<、<=、>、>= 之后，或子查詢用作表達式時，這種情況是不允許的。

【解決方案】：

先備份好賬套，進入數(shù)據(jù)庫，對應用賬套庫執(zhí)行下列語句：（全部復制過去，一起執(zhí)行）

執(zhí)行完后，接著升級即可；

腳本：

----補丁腳本----

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]設(shè)置為最小值

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