Msg 8728, Level 16, State 1, Server CSR-SQL-01, Procedure AssociateClient, Line 197 RANGE 窗口框架的 ORDER BY 列表不能包含 LOB 类型的表达式。 Procedure execution failed [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]RANGE 窗口框架的 ORDER BY 列表不能包含 LOB 类型的表达式。 (8728) 时间: 0.617s ALTER PROCEDURE [dbo].[AssociateClient] @BusinessCode AS nvarchar(100) , --客户代码 @ShopCode AS nvarchar(100) , --店铺代码 @ThisDate AS NVarchar(50) --年月 AS BEGIN set nocount on -- routine body goes here, e.g. -- SELECT 'Navicat for SQL Server' --常规 create table #Temp_Ought --创建临时表#Tmp ( TakeDate datetime , --日期 Type varchar(MAX), --类型 Remark varchar(MAX), --说明 ExpenseItemName varchar (MAX), --费用项目 BillNo varchar(MAX) , -- 单据号 SourceBillNo varchar(MAX) , -- 来源单号 counts varchar(MAX) , -- 货品总数 AmountOught Numeric(10,2) , -- 本期应收 ); create table #Temp_Ready --创建临时表#Tmp ( TakeDate datetime, --日期 Type varchar(MAX), --类型 Remark varchar(MAX), --说明 ExpenseItemName varchar (MAX), --费用项目 BillNo varchar(MAX) , -- 单据号 SourceBillNo varchar(MAX) , -- 来源单号 counts varchar(MAX) , -- 货品总数 AmountGathering Numeric(10,2) , -- 本期应收 ); INSERT into #Temp_Ought(TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught) select voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '应收' end as 类型 , case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次' else voucher.ManualBillNo end as Remark , expenseItem.ExpenseItemName,voucher.BillNo ,voucher.SourceBillNo ,(select sum(Qty) from FIRP_Arp_VoucherDetail s left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID where s.BillNo = voucher.BillNo) as counts ,voucher.Amount from FIRP_ARP_VoucherMaster voucher left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo FROM dbo.FIRP_ARP_VoucherOtherExpense fav LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> '' where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo LEFT JOIN vwFIRP_ARP_PlanDetail detail on detail.BillNo = voucher.BillNo LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo where CONVERT(NVARCHAR(10),VOUCHER.CHECKDATE,120) like ''+ @ThisDate +'%' and manerag.VendCustCode = ''+convert(nvarchar(10),@BusinessCode)+'' ORDER BY voucher.CheckDate, voucher.BillNo INSERT into #Temp_Ready(TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountGathering) select voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '收款' end as 类型 , case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次' when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次' else voucher.ManualBillNo end as Remark , expenseItem.ExpenseItemName, voucher.BillNo , voucher.SourceBillNo ,(select sum(Qty) from FIRP_Arp_VoucherDetail s left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID where s.BillNo = voucher.BillNo) as counts,voucher.Amount from FIRP_Pre_RPVoucherMaster voucher left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID left join Bas_Shop shop on shop.CustomerID = manerag.VendCustID left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo FROM dbo.FIRP_ARP_VoucherOtherExpense fav LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> '' where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo where convert(nvarchar(10) , voucher.CheckDate,120) like ''+ @ThisDate +'%' --CONVERT(NVARCHAR(10),VOUCHER.CHECKDATE,120) like ''+CONVERT(NVARCHAR(10),@THISDATE)+'%' and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+'' ORDER BY voucher.CheckDate, voucher.BillNo -- DATEADD(ss,+(rand(checksum(newid()))*10),TakeDate) SELECT * INTO #end_Temp FROM ( SELECT TakeDate as TakeDate ,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught,CONVERT(decimal(18,2),0) SHOURU ,AmountOught AS HEJI FROM #Temp_Ought UNION ALL SELECT TakeDate as TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,00, AmountGathering,-AmountGathering AS HEJI FROM #Temp_Ready ) T IF OBJECT_ID('#end_Temp') IS NOT NULL declare @AmountOught_sym Numeric(10,2), -- 期初 @SQL_ NVARCHAR(MAX) -- routine body goes here, e.g. -- SELECT 'Navicat for SQL Server create table #Temp_Ought_Previous --创建临时表#Tmp ( TakeDate datetime, --日期 AmountOught Numeric(10,2) , -- 本期应收 ); create table #Temp_Ready_Previous --创建临时表#Tmp ( TakeDate datetime, --日期 AmountGathering Numeric(10,2) , -- 本期应收 ); INSERT into #Temp_Ought_Previous(TakeDate,AmountOught) select voucher.CheckDate as thisDate, voucher.Amount from FIRP_ARP_VoucherMaster voucher left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID where convert(nvarchar(10) , voucher.CheckDate,120) < ''+CONVERT(VARCHAR(10),@ThisDate+'-01')+'' and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+'' ORDER BY voucher.CheckDate --voucher.CheckDate INSERT into #Temp_Ready_Previous(TakeDate,AmountGathering) select voucher.CheckDate as thisDate, voucher.Amount from FIRP_Pre_RPVoucherMaster voucher left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID where convert(nvarchar(10) , voucher.CheckDate,120) < ''+CONVERT(VARCHAR(10),@ThisDate+'-01')+'' and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+'' ORDER BY voucher.CheckDate SELECT * INTO #end_Temp_Previous FROM ( SELECT TakeDate, AmountOught,CONVERT(decimal(18,2),0) SHOURU ,AmountOught AS HEJI FROM #Temp_Ought_Previous UNION ALL SELECT TakeDate, 00, AmountGathering,-AmountGathering AS HEJI FROM #Temp_Ready_Previous ) T --select * from #end_Temp_Previous set @AmountOught_sym = (SELECT top 1 SUM(HEJI)OVER( ORDER BY TakeDate ) AS YUE FROM #end_Temp_Previous ORDER BY TakeDate desc) --select @AmountOught_sym --计算期初 --SELECT @ThisDate SET @ThisDate=''+CONVERT(VARCHAR(10),@ThisDate+'-01')+'' --SELECT @ThisDate /* INSERT into #end_Temp(TakeDate,Type) select ''+CONVERT(NVARCHAR(10),@ThisDate)+'','期初' */ -- if exists(select * from #end_Temp) INSERT into #end_Temp(TakeDate,Type,HEJI) select ''+CONVERT(NVARCHAR(10),@ThisDate)+'','期初', ''+convert(nvarchar(100),@AmountOught_sym)+' ' select * from #end_Temp SELECT DISTINCT *,SUM(HEJI)OVER(ORDER BY TakeDate,BillNo) AS YUE FROM #end_Temp ORDER BY TakeDate -- SELECT * FROM #end_Temp ORDER BY TakeDate PARTITION BY TakeDate -- select TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught,AmountGathering,Balance from #Temp END ------------- SELECT DISTINCT *,SUM(HEJI)OVER(ORDER BY TakeDate,BillNo) AS YUE FROM #end_Temp ORDER BY TakeDate 把order by 后面的 billNo删除掉就正常了 |