[步骤]导出报表【637】失败。失败原因：数据库处理错误，请联系智汇技术！标识：N15085869
CODE:N15085869Error:15_137_2_-2146232060
SQL:DROP TABLE IF EXISTS #OrderSourceTypeName
SELECT
a.OrderSourceType,
a.OrderSourceTypeName
INTO #OrderSourceTypeName
FROM(
SELECT 1 OrderSourceType , '亚马逊平台' OrderSourceTypeName UNION
SELECT 2 OrderSourceType , 'Ebay平台' OrderSourceTypeName UNION
SELECT 3 OrderSourceType , '团购平台' OrderSourceTypeName UNION
SELECT 4 OrderSourceType , 'B2C网站平台' OrderSourceTypeName UNION
SELECT 5 OrderSourceType , '赠送订单' OrderSourceTypeName UNION
SELECT 6 OrderSourceType , '枪手订单' OrderSourceTypeName UNION
SELECT 7 OrderSourceType , '问题订单重发' OrderSourceTypeName UNION
SELECT 8 OrderSourceType , '问题订单补发' OrderSourceTypeName UNION
SELECT 9 OrderSourceType , '代发货渠道' OrderSourceTypeName UNION
SELECT 10 OrderSourceType , '亚马逊FBA' OrderSourceTypeName UNION
SELECT 19 OrderSourceType , '其他第三方渠道' OrderSourceTypeName UNION
SELECT 20 OrderSourceType , '本地仓库备库订单' OrderSourceTypeName UNION SELECT 21 OrderSourceType , '海外仓库备库订单' OrderSourceTypeName UNION SELECT 22 OrderSourceType , '亚马逊仓库备库订单' OrderSourceTypeName UNION SELECT 23 OrderSourceType , 'Wish平台' OrderSourceTypeName UNION SELECT 24 OrderSourceType , '共享平台' OrderSourceTypeName UNION SELECT 25 OrderSourceType , '速卖通平台' OrderSourceTypeName UNION SELECT 26 OrderSourceType , '麦进斗平台' OrderSourceTypeName UNION SELECT 27 OrderSourceType , '敦煌平台' OrderSourceTypeName UNION SELECT 28 OrderSourceType , 'PayPal交易平台' OrderSourceTypeName UNION SELECT 29 OrderSourceType , '新蛋平台' OrderSourceTypeName UNION SELECT 30 OrderSourceType , 'LetsEcig平台' OrderSourceTypeName UNION SELECT 31 OrderSourceType , 'Meetgift平台' OrderSourceTypeName UNION SELECT 32 OrderSourceType , 'Qoo10平台' OrderSourceTypeName UNION SELECT 33 OrderSourceType , 'Lazada平台' OrderSourceTypeName UNION SELECT 34 OrderSourceType , 'Alibaba平台' OrderSourceTypeName UNION SELECT 35 OrderSourceType , 'Cdiscount平台' OrderSourceTypeName UNION SELECT 36 OrderSourceType , 'Linio平台' OrderSourceTypeName UNION SELECT 37 OrderSourceType , 'Bellabuy平台' OrderSourceTypeName UNION SELECT 38 OrderSourceType , '京东平台' OrderSourceTypeName UNION SELECT 39 OrderSourceType , 'Tophatter平台' OrderSourceTypeName UNION SELECT 40 OrderSourceType , 'PriceMinister平台' OrderSourceTypeName UNION SELECT 41 OrderSourceType , 'Mercadolibre平台' OrderSourceTypeName UNION SELECT 42 OrderSourceType , 'Shopee平台' OrderSourceTypeName UNION SELECT 43 OrderSourceType , 'Kamvio平台' OrderSourceTypeName UNION SELECT 44 OrderSourceType , 'Amazonvendorexpress平台' OrderSourceTypeName UNION SELECT 45 OrderSourceType , 'Walmart平台' OrderSourceTypeName UNION SELECT 46 OrderSourceType , 'Deal平台' OrderSourceTypeName UNION SELECT 47 OrderSourceType , 'Jumia平台' OrderSourceTypeName UNION SELECT 48 OrderSourceType , 'Jet平台' OrderSourceTypeName UNION SELECT 49 OrderSourceType , 'Sears平台' OrderSourceTypeName UNION SELECT 50 OrderSourceType , 'Shopify平台' OrderSourceTypeName UNION SELECT 51 OrderSourceType , 'Joom平台' OrderSourceTypeName UNION SELECT 52 OrderSourceType , 'MyMall平台' OrderSourceTypeName UNION SELECT 53 OrderSourceType , 'FactoryMarket平台' OrderSourceTypeName UNION SELECT 54 OrderSourceType , 'Fyndiq平台' OrderSourceTypeName UNION SELECT 55 OrderSourceType , '京东国内平台' OrderSourceTypeName UNION SELECT 56 OrderSourceType , 'Oberlo平台' OrderSourceTypeName UNION SELECT 57 OrderSourceType , '速卖通V2平台' OrderSourceTypeName UNION SELECT 58 OrderSourceType , 'LazadaV2平台' OrderSourceTypeName UNION SELECT 59 OrderSourceType , 'Yandex平台' OrderSourceTypeName UNION SELECT 60 OrderSourceType , 'Paytm平台' OrderSourceTypeName UNION SELECT 61 OrderSourceType , '京东国际平台' OrderSourceTypeName UNION SELECT 62 OrderSourceType , 'Vova平台' OrderSourceTypeName UNION SELECT 63 OrderSourceType , '京东印尼平台' OrderSourceTypeName UNION SELECT 64 OrderSourceType , 'UMKA平台' OrderSourceTypeName UNION SELECT 65 OrderSourceType , 'Rakuten平台' OrderSourceTypeName UNION SELECT 66 OrderSourceType , '值点平台' OrderSourceTypeName UNION SELECT 67 OrderSourceType , 'Allegro平台' OrderSourceTypeName UNION SELECT 68 OrderSourceType , '亲库平台' OrderSourceTypeName UNION SELECT 69 OrderSourceType , 'ZoodMall' OrderSourceTypeName UNION SELECT 70 OrderSourceType , 'Mercadolibre国际平台' OrderSourceTypeName UNION SELECT 71 OrderSourceType , 'Gmarket平台' OrderSourceTypeName UNION SELECT 72 OrderSourceType , 'Auction平台' OrderSourceTypeName UNION SELECT 73 OrderSourceType , 'Coupang平台' OrderSourceTypeName UNION SELECT 74 OrderSourceType , 'FacebookMarketplace平台' OrderSourceTypeName UNION SELECT 75 OrderSourceType , 'KauflandDe平台' OrderSourceTypeName UNION SELECT 76 OrderSourceType , '阿里巴巴国际站平台' OrderSourceTypeName UNION SELECT 77 OrderSourceType , 'RatukenDE平台' OrderSourceTypeName UNION SELECT 78 OrderSourceType , 'Wayfair平台' OrderSourceTypeName UNION SELECT 79 OrderSourceType , '店匠平台' OrderSourceTypeName UNION SELECT 80 OrderSourceType , 'B2W平台' OrderSourceTypeName UNION SELECT 81 OrderSourceType , 'Kogan平台' OrderSourceTypeName UNION SELECT 82 OrderSourceType , 'Fruugo平台' OrderSourceTypeName UNION SELECT 83 OrderSourceType , 'Komplett平台' OrderSourceTypeName UNION SELECT 84 OrderSourceType , 'Digitect平台' OrderSourceTypeName UNION SELECT 85 OrderSourceType , 'Yahoo平台' OrderSourceTypeName UNION SELECT 86 OrderSourceType , 'Bukalapak平台' OrderSourceTypeName UNION SELECT 87 OrderSourceType , '沃尔玛DSV平台' OrderSourceTypeName UNION SELECT 88 OrderSourceType , 'Shopyy平台' OrderSourceTypeName UNION SELECT 89 OrderSourceType , 'L192平台' OrderSourceTypeName UNION SELECT 90 OrderSourceType , 'OTTO平台' OrderSourceTypeName UNION SELECT 91 OrderSourceType , 'CrossMall平台' OrderSourceTypeName UNION SELECT 92 OrderSourceType , 'Fnac平台' OrderSourceTypeName UNION SELECT 93 OrderSourceType , 'Darty平台' OrderSourceTypeName UNION SELECT 94 OrderSourceType , 'EPrice平台' OrderSourceTypeName UNION SELECT 95 OrderSourceType , 'Daraz平台' OrderSourceTypeName UNION SELECT 96 OrderSourceType , 'Americanas平台' OrderSourceTypeName UNION SELECT 97 OrderSourceType , 'ShopeeV2平台' OrderSourceTypeName UNION SELECT 98 OrderSourceType , 'Snapdeal平台' OrderSourceTypeName UNION SELECT 99 OrderSourceType , 'Submarino平台' OrderSourceTypeName UNION SELECT 100 OrderSourceType , 'Overstock平台' OrderSourceTypeName UNION SELECT 101 OrderSourceType , 'ManoMano平台' OrderSourceTypeName UNION SELECT 102 OrderSourceType , 'RueduCommerce平台' OrderSourceTypeName UNION SELECT 103 OrderSourceType , 'TradeMe平台' OrderSourceTypeName UNION SELECT 104 OrderSourceType , 'TikTok平台' OrderSourceTypeName UNION SELECT 105 OrderSourceType , 'Worten平台' OrderSourceTypeName UNION SELECT 106 OrderSourceType , 'PayPayMall平台' OrderSourceTypeName UNION SELECT 107 OrderSourceType , 'Target平台' OrderSourceTypeName UNION SELECT 108 OrderSourceType , 'Flipkart平台' OrderSourceTypeName UNION SELECT 109 OrderSourceType , 'BestBuy平台' OrderSourceTypeName UNION SELECT 110 OrderSourceType , 'BolCom平台' OrderSourceTypeName UNION SELECT 111 OrderSourceType , 'Tokopedia平台' OrderSourceTypeName UNION SELECT 112 OrderSourceType , 'SHOPLINE平台' OrderSourceTypeName UNION SELECT 114 OrderSourceType , 'Akulaku平台' OrderSourceTypeName UNION SELECT 115 OrderSourceType , 'Funpinpin平台' OrderSourceTypeName UNION SELECT 116 OrderSourceType , 'Fannoshop平台' OrderSourceTypeName UNION SELECT 117 OrderSourceType , 'OnBuy' OrderSourceTypeName UNION SELECT 118 OrderSourceType , 'HomeDepot' OrderSourceTypeName UNION SELECT 119 OrderSourceType , 'WooCommerce' OrderSourceTypeName UNION SELECT 120 OrderSourceType , 'Thisshop' OrderSourceTypeName UNION SELECT 121 OrderSourceType , 'XShoppy' OrderSourceTypeName UNION SELECT 124 OrderSourceType , 'Catch平台' OrderSourceTypeName UNION SELECT 125 OrderSourceType , '拼多多跨境' OrderSourceTypeName )a -- 渠道来源 CREATE TABLE #temp_OrderSource ( [OrderSourceID] INT NOT NULL, [CustomerID] INT NOT NULL, [平台] VARCHAR(200) NULL, [来源渠道名称] VARCHAR(200) NULL, [导入时间] DATETIME2 NULL, [账号类型] VARCHAR(100) NULL, [负责部门] NVARCHAR(50) NULL, [渠道系统状态] VARCHAR(50) NULL, [来源渠道最近更新时间] DATETIME NULL, [start_order_time] DATETIME2 NULL, [end_order_time] DATETIME2 NULL, [店铺授权情况] NVARCHAR(50) NULL, [授权中断时间] DATETIME NULL ) INSERT #temp_OrderSource ( OrderSourceID, CustomerID, 平台, 来源渠道名称, 导入时间, 账号类型, 负责部门, 渠道系统状态, 来源渠道最近更新时间, [start_order_time], [end_order_time], 店铺授权情况, 授权中断时间 ) SELECT os.ID OrderSourceID, os.CustomerID, ost.OrderSourceTypeName '平台', os.OrderSourceName [来源渠道名称], osl.AddTime [导入时间], CASE WHEN osl.AddTime >= DATEADD(MONTH,-3,GETDATE()) THEN '新账号' WHEN osl.AddTime < DATEADD(MONTH,-3,GETDATE()) and osl.AddTime >= DATEADD(MONTH,-12,GETDATE()) THEN '普通账号' ELSE '老账号' END [账号类型], de.DepartmentName [负责部门], CASE os.SourceStatus WHEN 0 THEN '正常' WHEN 1 THEN '申诉中' WHEN 2 THEN '暂停' WHEN 3 THEN '注销' ELSE NULL END [渠道系统状态], os.UpdateTime [来源渠道最近更新时间], DATEADD(DAY, 30, osl.AddTime) [start_order_time], DATEADD(DAY, 60, osl.AddTime) [end_order_time], CASE WHEN rboeapi.IsActive=1 AND eabyAPI.IsActive=1 THEN '已授权' ELSE '未授权' END [店铺授权情况], (SELECT TOP 1 AddTime FROM RB_OrderSource_Log(NOLOCK) t WHERE t.LogTableName='RB_OrderSource_EbayAmazonAPI' AND t.Remark LIKE'%需要重新授权%' AND t.CustomerID=os.CustomerID AND t.LogType=CONVERT(NCHAR(10),os.ID) ORDER BY t.ID ASC ) AS 授权中断时间 FROM dbo.RB_OrderSource os (NOLOCK) LEFT JOIN ( SELECT CustomerID,LogTableID,MIN(AddTime) AddTime FROM dbo.RB_OrderSource_Log osl(NOLOCK) WHERE osl.LogTableName='RB_OrderSource' GROUP BY osl.CustomerID,osl.LogTableID ) osl ON osl.CustomerID = os.CustomerID AND osl.LogTableID = os.ID LEFT JOIN irobotbox.dbo.RB_Admin ad(NOLOCK) ON ad.CustomerID = os.CustomerID AND ad.ID = os.SaleAdminID LEFT JOIN irobotbox.dbo.RB_Department de(NOLOCK) ON de.CustomerID = os.CustomerID AND de.ID = ad.DepartmentID LEFT JOIN #OrderSourceTypeName ost ON ost.OrderSourceType = os.OrderSourceType LEFT JOIN dbo.RB_OrderSource_EbayAmazonAPI(NOLOCK) rboeapi ON os.ID=rboeapi.OrderSourceID AND os.CustomerID=rboeapi.CustomerID LEFT JOIN RB_OrderSource_API (NOLOCK) eabyAPI ON os.ID=eabyAPI.OrderSourceID AND os.CustomerID=eabyAPI.CustomerID WHERE os.CustomerID=@CustomerID AND os.OrderSourceType IN (SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@OrderSourceType,',')) AND 1=1 AND 1=1 AND ad.ID IN (SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@Auth_AdminID,',')) -- 订单数据 DROP TABLE IF EXISTS #temp_Orderinfo SELECT ros.CustomerID, ros.OrderSourceID, a.导入时间, ros.PayTime, ros.OrderCode, (ros.Incomes/ros.USD_ExchangeRMB) [销售额(USD)], (ros.Incomes) [销售额(RMB)] INTO #temp_Orderinfo FROM #temp_OrderSource a INNER JOIN( SELECT ros.CustomerID, ros.OrderCode, ros.OrderSourceID, ros.Incomes, ros.Profits, ros.USD_ExchangeRMB, ros.PayTime, ROW_NUMBER() OVER(PARTITION BY oi.OrderSourceID ORDER BY oi.PayTime DESC) [rank] FROM dbo.RB_Report_OrderSettlement ros(NOLOCK) INNER JOIN dbo.RB_OrderInfo oi(NOLOCK) ON oi.CustomerID = ros.CustomerID AND oi.OrderCode = ros.OrderCode WHERE ros.CustomerID=1 AND oi.IsStockOrder=0 AND oi.IsSystem = 0 AND ros.PayTime >= @start_paytime AND ros.PayTime <= @end_paytime )ros ON ros.OrderSourceID=a.OrderSourceID AND ros.CustomerID = a.CustomerID --- 循环添加自然月 DECLARE @loop_max_num INT= (SELECT DATEDIFF(DAY,MIN(start_order_time),GETDATE())/30 FROM #temp_OrderSource) DECLARE @loop_num INT= 1 WHILE @loop_num <= @loop_max_num BEGIN INSERT #temp_OrderSource SELECT DISTINCT * FROM #temp_OrderSource a SET @loop_num += 1 END DROP TABLE IF EXISTS #temp_OrderSource2 SELECT a.OrderSourceID, a.CustomerID, a.平台, a.来源渠道名称, a.导入时间, a.账号类型, a.负责部门, a.渠道系统状态, a.来源渠道最近更新时间, DATEADD(DAY,(ROW_NUMBER() OVER(PARTITION BY a.OrderSourceID,a.CustomerID ORDER BY a.导入时间)-1)*30,a.start_order_time) start_order_time, DATEADD(DAY,(ROW_NUMBER() OVER(PARTITION BY a.OrderSourceID,a.CustomerID ORDER BY a.导入时间)-1)*30,a.end_order_time) end_order_time INTO #temp_OrderSource2 FROM #temp_OrderSource a DROP TABLE IF EXISTS #temp_OrderSource3 -- 求自然月的总订单量 SELECT a.OrderSourceID, a.CustomerID, a.start_order_time, a.end_order_time, COUNT(DISTINCT b.OrderCode) 订单总量 INTO #temp_OrderSource3 FROM #temp_OrderSource2 a LEFT JOIN #temp_Orderinfo b ON b.CustomerID = a.CustomerID AND b.OrderSourceID = a.OrderSourceID AND a.start_order_time<=b.PayTime AND a.end_order_time>b.PayTime WHERE a.end_order_time <= GETDATE() GROUP BY a.OrderSourceID, a.CustomerID, a.start_order_time, a.end_order_time DROP TABLE IF EXISTS #temp_Orderinfo2 SELECT b.OrderSourceID, b.CustomerID, MIN(b.PayTime) 首笔订单支付时间, MAX(b.PayTime) 最近一笔订单支付时间, (CASE WHEN MIN(b.PayTime)<= DATEADD(DAY, 30, b.导入时间) THEN '是' ELSE '否' END) [新账号达标（分配销售30天内出1单）], COUNT(DISTINCT b.OrderCode) 订单总量, SUM(b.[销售额(USD)]) [销售额(USD)], SUM(b.[销售额(RMB)]) [销售额(RMB)], SUM(b.[销售额(USD)]) / COUNT(DISTINCT b.OrderCode) [平均客单价(USD)], SUM(b.[销售额(RMB)]) / COUNT(DISTINCT b.OrderCode) [平均客单价(RMB)] INTO #temp_Orderinfo2 FROM #temp_Orderinfo b GROUP BY b.OrderSourceID, b.CustomerID, b.导入时间 -- 查询数据 SELECT a.OrderSourceID, a.平台, a.来源渠道名称, a.导入时间, a.账号类型, a.负责部门, a.渠道系统状态, a.来源渠道最近更新时间, b.首笔订单支付时间, b.最近一笔订单支付时间, ISNULL(b.[新账号达标（分配销售30天内出1单）],'否') [新账号达标（分配销售30天内出1单）], b.订单总量, b.[销售额(USD)], b.[销售额(RMB)], b.[平均客单价(USD)], b.[平均客单价(RMB)], (CASE WHEN SUM(CASE WHEN c.订单总量<5 THEN 1 WHEN c.订单总量>=5 THEN 0 END) > 0 THEN '否' WHEN SUM(CASE WHEN c.订单总量<5 THEN 1 WHEN c.订单总量>=5 THEN 0 END) = 0 THEN '是' ELSE '否' END) [是否订单达标], a.店铺授权情况, a.授权中断时间 FROM #temp_OrderSource a LEFT JOIN #temp_Orderinfo2 b ON b.CustomerID = a.CustomerID AND b.OrderSourceID = a.OrderSourceID LEFT JOIN #temp_OrderSource3 c ON c.CustomerID = a.CustomerID AND c.OrderSourceID = a.OrderSourceID WHERE a.CustomerID=@CustomerID GROUP BY a.OrderSourceID, a.平台, a.来源渠道名称, a.导入时间, a.账号类型, a.负责部门, a.渠道系统状态, a.来源渠道最近更新时间, b.首笔订单支付时间, b.最近一笔订单支付时间, b.[新账号达标（分配销售30天内出1单）], b.订单总量, b.[销售额(USD)], b.[销售额(RMB)], b.[平均客单价(USD)], b.[平均客单价(RMB)], a.店铺授权情况, a.授权中断时间 --SELECT * FROM #temp_Orderinfo2 --SELECT * FROM #temp_OrderSource3 PARAMS:@OrderSourceType = 2 ,@Auth_AdminID = 0,6867 ,@CustomerId = 1 , MESSAGE:Must declare the scalar variable "@start_paytime". TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData() 在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) 在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) 在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) 在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) 在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms) 在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout) 在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReportToSqlDataReader(String firstFileFullPath, RB_CustomReportTask customReportTask, String[] noPermsissionfields, Dictionary`2 paramList, List`1 exportFields, String orderBy, Int32 timeOut) 在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportCSVBySteam(RB_CustomReportTask customReportTask, String[] noPermsissionfields, Dictionary`2 paramValues, List`1 exportFields, String orderField, Int32 timeOut) 在 Biz.Irobotbox.ReportCentre.CustomReportService.ExecuteCustomReportTask(List`1 customReportList) 在 System.Threading.Tasks.Task.Execute() 在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) 在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) 在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot) 在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution) 在 System.Threading.Tasks.Schedulers.LimitedConcurrencyLevelTaskScheduler.b__6_0(Object _) 在 System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() 在 System.Threading.ThreadPoolWorkQueue.Dispatch()
[结果]
[期望]修复SQL 异常