[步骤]
prometheus告警
开始时间: 15:53:18
错误代码: N1553186
sql类型: SELECT
sql: [15:53:18] -------------------------------------------------------------
CODE:N1553186
Error:15_156_1_-2146232060
SQL:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [月份] desc) AS [PageIndex] ,* FROM ( SELECT
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)) AS '月份',
a.SKU,
f.ProductNameCN AS '产品名称',
a.OrderSourceID,
e.DepartmentName,
SUM(a.Incomes )AS '销售额',
SUM(a.Profits) AS '利润',
SUM(-a.ImportFee) AS '人工导入费用'
INTO #sbl
FROM
dbo.RB_Report_OrderSettlement(NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource(NOLOCK) AS c ON a.CustomerID = c.CustomerID AND a.OrderSourceID = c.ID
LEFT JOIN dbo.RB_Admin(NOLOCK) AS d ON a.CustomerID = d.CustomerID AND c.SaleAdminID = d.ID
LEFT JOIN dbo.RB_Department(NOLOCK) AS e ON a.CustomerID = e.CustomerID AND d.DepartmentID = e.ID
LEFT JOIN dbo.RB_Product(NOLOCK) AS f ON a.CustomerID = f.CustomerID AND a.SKU = f.SKU
WHERE a.CustomerID = @CustomerID AND e.DepartmentName LIKE ('%品牌%') AND ISNULL(a.OrderCode,'') = ''
AND a.ShipTime >= @StartTime AND a.ShipTime <= @EndTime
AND a.ImportFee != 0
AND a.OrderDescription LIKE '%盛宝隆%' AND a.OrderDescription LIKE '%仓租%'
GROUP BY
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)),
a.SKU,
a.OrderSourceID,
e.DepartmentName,
f.ProductNameCN
SELECT
m.SKU,
m.RelationSku
INTO #oversea
FROM
(SELECT
DISTINCT
a.SKU,
CASE WHEN ISNULL(b.RelationSku,'') != '' THEN b.RelationSku ELSE c.SKU END AS 'RelationSku'
FROM
#sbl AS a LEFT JOIN dbo.RB_WareHouse_ProductDetail(NOLOCK) AS b ON b.CustomerID = 1 AND b.CustomerID = 1
AND a.SKU = b.ProductSKU
LEFT JOIN dbo.RB_WareHouse_Product(NOLOCK) AS c ON c.CustomerID = 1 AND a.SKU = c.OrderSourceSKU
--LEFT JOIN dbo.RB_WareHouse_Product(NOLOCK) AS d ON d.CustomerID = 1 AND a.SKU = d.FNSKU
WHERE a.产品名称 IS NULL AND a.SKU != '' ) AS m
WHERE m.RelationSku != ''
--正常订单
SELECT
CAST(a.ShipTime AS DATE) AS '日期',
a.SKU,
a.OrderSourceID,
b.ProductNameCN AS '产品名称',
d.BusinessTeamName AS '小组',
SUM( CASE WHEN e.WareHouseType = 3 THEN a.Incomes ELSE 0 END ) AS 'FBA销售额(对应月份)',
SUM(CASE WHEN e.WareHouseType != 3 THEN a.Incomes ELSE 0 END ) AS '海外仓销售额(对应月份)',
SUM( CASE WHEN e.WareHouseType = 3 THEN (a.ProductNum + a.ProdutNumUpdate) ELSE 0 END ) AS 'FBA销量(对应月份)',
SUM(CASE WHEN e.WareHouseType != 3 THEN (a.ProductNum + a.ProdutNumUpdate) ELSE 0 END ) AS '海外仓销量(对应月份)',
SUM( CASE WHEN e.WareHouseType = 3 THEN a.Profits ELSE 0 END ) AS 'FBA利润(对应月份)',
SUM(CASE WHEN e.WareHouseType != 3 THEN a.Profits ELSE 0 END ) AS '海外仓利润(对应月份)'
INTO #sale
FROM
dbo.RB_Report_OrderSettlement(NOLOCK) AS a
LEFT JOIN dbo.RB_Product(NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU
LEFT JOIN dbo.RB_Amazon_Product_Base(NOLOCK) AS d ON a.CustomerID = d.CustomerID
AND a.OrderSourceID = d.OrderSourceID AND a.SKU = d.SKU AND a.ASINorItemID = d.ASINorItemID AND a.SellerSKU = d.OrderSourceSKU
LEFT JOIN dbo.RB_WareHouse(NOLOCK) AS e ON a.CustomerID = e.CustomerID AND a.WareHouseID = e.ID
WHERE
a.CustomerID = @CustomerID
AND a.OrderStatus != 2
AND d.BusinessTeamName LIKE '%品牌%'
AND d.BusinessTeamName NOT LIKE '%营销%'
AND a.ShipTime >= @StartTime
AND a.ShipTime <= @EndTime
AND ISNULL(a.OrderCode,'') != ''
AND ISNULL(a.SKU,'') != ''
GROUP BY
CAST(a.ShipTime AS DATE),
d.BusinessTeamName,
a.SKU,
a.OrderSourceID,
b.ProductNameCN
SELECT
CONCAT(YEAR(a.日期) ,'-',MONTH(a.日期)) AS '月份',
a.小组,
a.OrderSourceID,
a.SKU,
a.产品名称,
SUM(a.[FBA销售额(对应月份)]) AS 'FBA销售额(对应月份)',
SUM(a.[海外仓销售额(对应月份)]) AS '海外仓销售额(对应月份)',
SUM(a.[FBA销量(对应月份)]) AS 'FBA销量(对应月份)',
SUM(a.[海外仓销量(对应月份)]) AS '海外仓销量(对应月份)',
SUM(a.[FBA利润(对应月份)]) AS 'FBA利润(对应月份)',
SUM(a.[海外仓利润(对应月份)]) AS '海外仓利润(对应月份)'
INTO #sale2
FROM #sale AS a
GROUP BY
CONCAT(YEAR(a.日期) ,'-',MONTH(a.日期)),
a.小组,
a.OrderSourceID,
a.SKU,
a.产品名称
SELECT
a.OrderSourceID,
a.SKU,
a.ASINorItemID,
SUM(a.KykcNum) AS 'FBA可用库存',
SUM(a.WeekSaleNum) AS '周销量',
SUM(a.msaleNum) AS '30天销量',
SUM(a.AvgDailySales) AS '估算销量'
INTO #listing
FROM
dbo.RB_Amazon_Product_Base(NOLOCK) AS a
WHERE a.CustomerID = @CustomerID
AND a.BusinessTeamName LIKE '%品牌%'
AND a.BusinessTeamName NOT LIKE '%营销%'
AND a.IsDelete = 0 AND a.IsOffShelf = 0
GROUP BY
a.OrderSourceID,
a.SKU,
a.ASINorItemID
SELECT
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)) AS '月份',
a.SKU,
a.OrderSourceID,
e.DepartmentName,
SUM(-a.OrderSourceOtherDebits) AS 'FBA仓租'
INTO #other
FROM
dbo.RB_Report_OrderSettlement(NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource(NOLOCK) AS c ON a.CustomerID = c.CustomerID AND a.OrderSourceID = c.ID
LEFT JOIN dbo.RB_Admin(NOLOCK) AS d ON a.CustomerID = d.CustomerID AND c.SaleAdminID = d.ID
LEFT JOIN dbo.RB_Department(NOLOCK) AS e ON a.CustomerID = e.CustomerID AND d.DepartmentID = e.ID
WHERE a.CustomerID = 1 AND e.DepartmentName LIKE ('%品牌%') AND ISNULL(a.OrderCode,'') = ''
AND a.ShipTime >= @StartTime AND a.ShipTime <= @EndTime
AND a.OrderSourceOtherDebits != 0
AND a.OrderDescription NOT LIKE '%盛宝隆%'
AND e.DepartmentName != '品牌营销组'
AND a.OrderDescription LIKE '%amazon仓储费分摊%'
AND a.SKU != ''
GROUP BY
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)),
a.SKU,
a.OrderSourceID,
e.DepartmentName
SELECT
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)) AS '月份',
a.OrderSourceID,
e.DepartmentName,
a.ASINorItemID,
SUM(-a.OrderSourceOtherDebits) AS 'FBA仓租'
INTO #other2
FROM
dbo.RB_Report_OrderSettlement(NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource(NOLOCK) AS c ON a.CustomerID = c.CustomerID AND a.OrderSourceID = c.ID
LEFT JOIN dbo.RB_Admin(NOLOCK) AS d ON a.CustomerID = d.CustomerID AND c.SaleAdminID = d.ID
LEFT JOIN dbo.RB_Department(NOLOCK) AS e ON a.CustomerID = e.CustomerID AND d.DepartmentID = e.ID
WHERE a.CustomerID = 1 AND e.DepartmentName LIKE ('%品牌%') AND ISNULL(a.OrderCode,'') = ''
AND a.ShipTime >= @StartTime AND a.ShipTime <= @EndTime
AND a.OrderSourceOtherDebits != 0
AND a.OrderDescription NOT LIKE '%盛宝隆%'
AND e.DepartmentName != '品牌营销组'
AND a.OrderDescription LIKE '%amazon仓储费分摊%'
AND a.SKU = ''
GROUP BY
CONCAT(YEAR(a.ShipTime),'-',MONTH(a.ShipTime)),
a.ASINorItemID,
a.OrderSourceID,
e.DepartmentName
SELECT
m.月份,
m.SKU,
m.OrderSourceID,
m.DepartmentName,
SUM(m.FBA仓租) AS 'FBA仓租'
INTO #other3
FROM
(SELECT
a.月份,
b.SKU,
a.OrderSourceID,
a.DepartmentName,
SUM(a.FBA仓租) AS 'FBA仓租'
FROM
#other2 AS a LEFT JOIN
(SELECT
DISTINCT
a.ASINorItemID,
b.SKU,
ROW_NUMBER()OVER(PARTITION BY a.ASINorItemID ORDER BY b.SKU) AS rowid
FROM
#other2 AS a LEFT JOIN dbo.RB_Amazon_Product_Base(NOLOCK) AS b ON a.ASINorItemID = b.ASINorItemID AND b.CustomerID = 1
WHERE a.ASINorItemID != '') AS b ON a.ASINorItemID = b.ASINorItemID AND b.rowid = 1
GROUP BY
a.月份,
a.OrderSourceID,
b.SKU,
a.DepartmentName
UNION ALL
SELECT * FROM #other) AS m
GROUP BY
m.月份,
m.SKU,
m.OrderSourceID,
m.DepartmentName
SELECT
a.月份,
a.小组,
e.OrderSourceName AS '账号',
a.SKU,
d.ProductNameCN AS '产品中文名',
c.season AS '季节属性',
SUM(b.FBA可用库存) AS 'FBA可用库存',
SUM(b.估算销量) AS '估算销量',
CASE WHEN SUM(b.估算销量) > 0 THEN SUM(b.FBA可用库存)*1.0/SUM(b.估算销量) ELSE NULL END AS '可售天数',
SUM(b.周销量) AS '周销量',
SUM(b.[30天销量]) AS'30天销量' ,
SUM(a.[FBA销售额(对应月份)]) AS 'FBA销售额(对应月份)',
SUM(a.[海外仓销售额(对应月份)]) AS '海外仓销售额(对应月份)',
SUM(a.[FBA销量(对应月份)]) AS 'FBA销量(对应月份)',
SUM(a.[海外仓销量(对应月份)]) AS '海外仓销量(对应月份)',
SUM(a.盛宝隆仓租) AS '盛宝隆仓租',
SUM(a.FBA仓租) AS 'FBA仓租',
SUM(a.[FBA利润(对应月份)]) AS 'FBA利润(对应月份)',
SUM(a.[海外仓利润(对应月份)]) AS '海外仓利润(对应月份)'
FROM
(SELECT
ISNULL(ISNULL(m.月份,n.月份),k.月份) AS '月份',
ISNULL(ISNULL(m.小组,n.DepartmentName),k.DepartmentName) AS '小组',
ISNULL(ISNULL(m.OrderSourceID,n.OrderSourceID),k.OrderSourceID) AS 'OrderSourceID',
ISNULL(ISNULL(m.SKU,n.SKU),k.SKU) AS 'SKU',
ISNULL(SUM(m.[FBA销售额(对应月份)]),0) AS 'FBA销售额(对应月份)',
ISNULL(SUM(m.[海外仓销售额(对应月份)]),0) AS '海外仓销售额(对应月份)',
ISNULL(SUM(m.[FBA销量(对应月份)]),0) AS 'FBA销量(对应月份)' ,
ISNULL(SUM(m.[海外仓销量(对应月份)]),0) AS '海外仓销量(对应月份)',
ISNULL(SUM(m.[FBA利润(对应月份)]),0) AS 'FBA利润(对应月份)',
ISNULL(SUM(m.[海外仓利润(对应月份)]),0) AS '海外仓利润(对应月份)',
ISNULL(SUM(n.人工导入费用),0) AS '盛宝隆仓租',
ISNULL(SUM(k.FBA仓租),0) AS 'FBA仓租'
FROM
(SELECT
a.月份,
a.小组,
a.OrderSourceID,
a.SKU,
a.产品名称,
a.[FBA销售额(对应月份)],
a.[海外仓销售额(对应月份)],
a.[FBA销量(对应月份)],
a.[海外仓销量(对应月份)],
a.[FBA利润(对应月份)],
a.[海外仓利润(对应月份)]
FROM
#sale2 AS a) AS m
FULL JOIN
(SELECT
a.月份,
ISNULL(b.RelationSku,a.SKU) AS 'SKU',
a.OrderSourceID,
a.DepartmentName,
a.人工导入费用
FROM
#sbl AS a LEFT JOIN #oversea AS b ON a.SKU = b.SKU) AS n
ON m.月份 = n.月份 AND n.SKU = m.SKU AND n.OrderSourceID = m.OrderSourceID
FULL JOIN
#other3 AS k ON k.SKU = m.SKU AND k.OrderSourceID = m.OrderSourceID AND k.月份 = m.月份
GROUP BY
ISNULL(ISNULL(m.月份,n.月份),k.月份),
ISNULL(ISNULL(m.小组,n.DepartmentName),k.DepartmentName),
ISNULL(ISNULL(m.OrderSourceID,n.OrderSourceID),k.OrderSourceID),
ISNULL(ISNULL(m.SKU,n.SKU),k.SKU)) AS a
LEFT JOIN (SELECT OrderSourceID,
SKU,
SUM(FBA可用库存) AS 'FBA可用库存',
SUM(周销量) AS '周销量',
SUM([30天销量]) AS '30天销量',
SUM(估算销量) AS '估算销量' FROM #listing GROUP BY OrderSourceID,SKU) AS b ON a.OrderSourceID = b.OrderSourceID AND a.SKU = b.SKU
LEFT JOIN dbo.yp AS c ON a.SKU = c.SKU
LEFT JOIN dbo.RB_Product(NOLOCK) AS d ON a.SKU = d.SKU AND d.CustomerID = 1
LEFT JOIN dbo.RB_OrderSource(NOLOCK) AS e ON a.OrderSourceID = e.ID AND e.CustomerID = 1
GROUP BY
a.月份,
a.小组,
e.OrderSourceName,
a.SKU,
d.ProductNameCN,
c.season
) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20
PARAMS:@StartTime = 2022-12-01 ,@EndTime = 2022-12-13 23:59:59 ,@CustomerId = 1 ,
MESSAGE:Incorrect syntax near the keyword 'INTO'.
Incorrect syntax near ')'.
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, TaskCompletionSource1 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.ExecuteDbDataReader(CommandBehavior behavior) 在 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) 在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms) 在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DALExtend.RB_CustomReportExtend.GetPageDataNoTotalCount(String sqlContent, List1 sqlParameters, CM_GetCustomReportPageDataParam pager, String orderBy, Int32 timeOut)
在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReport(Int32 AdminID, Int32 customerId, Int32 customReportId, Int32 templateId, Dictionary2 paramList, CM_GetCustomReportPageDataParam pager, String orderBy, Int32 timeOut, Boolean isGetTotalCount) 在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReport(Int32 AdminID, Int32 customerId, Int32 customReportId, Int32 templateId, Dictionary2 paramList, Pager pager, String orderBy, Boolean isGetTotalCount)
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportExportList.GetPageData()
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportExportList.Page_Load(Object sender, EventArgs e)
在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
在 System.Web.UI.Control.OnLoad(EventArgs e)
在 System.Web.UI.Control.LoadRecursive()
在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest()
在 System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
在 System.Web.UI.Page.ProcessRequest(HttpContext context)
在 ASP.irobotbox_reportcentre_customreportexportlist_aspx.ProcessRequest(HttpContext context)
在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
[结果]
[期望]
