综合排序
最新发布
【问题现象】: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) --字段名称 以,分割 ASBEGIN 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.SearchStyle3 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.SearchStyle3 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.SearchStyle3END ; ; GO
新年大吉
  • 冬去山川齐奇丽
  • 喜来桃里共芳香