[步骤]
prometheus告警
开始时间: 18:05:00
错误代码: N1805003
sql类型: --DECLARE
sql: [18:05:00] -------------------------------------------------------------
CODE:N1805003
Error:16_8156_1_-2146232060
SQL:--DECLARE @CustomerID INT = 1
--DECLARE @StartTime DATETIME = '2022-10-01'
--DECLARE @EndTime DATETIME = '2022-11-01'
select top 0 a.SKU,
b.OnlineTime,
CASE a.ProductProperty
WHEN 0 THEN '未选择'
WHEN 1 THEN '非仿牌'
WHEN 2 THEN '仿牌'
WHEN 3 THEN '灰色产品'
END AS '产品侵权风险',
CASE WHEN a.ProductState = 2 THEN '停产' ELSE '未停产' END AS '是否停产',
c.ClassNameCn AS '产品一级品类',
a.ProductNameCN AS '中文名称',
a.IsGroup
INTO #sku
FROM
dbo.RB_Product(NOLOCK) AS a
LEFT JOIN dbo.RB_Product_Admin(NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU
LEFT JOIN dbo.RB_Product_Class(NOLOCK) AS c ON a.CustomerID = c.CustomerID AND a.ClassID1 = c.ID
WHERE a.CustomerID = @CustomerID AND a.DevelopType = 5
AND a.OnlineStatus = 1
select top 0 k.GroupSku,
k.SKU
INTO #groupsku
FROM
(select top 0 b.GroupSku,
b.GroupSku AS 'SKU'
FROM
#sku AS a LEFT JOIN dbo.RB_Product_GroupSku(NOLOCK) AS b ON b.CustomerID = 1 AND a.SKU = b.GroupSku
WHERE a.IsGroup = 1
GROUP BY
b.GroupSku
HAVING
COUNT(DISTINCT b.SKU) > 1
UNION ALL
select top 0 m.GroupSku,
n.SKU
FROM
(select top 0 b.GroupSku
FROM
#sku AS a LEFT JOIN dbo.RB_Product_GroupSku(NOLOCK) AS b ON b.CustomerID = 1 AND a.SKU = b.GroupSku
WHERE a.IsGroup = 1
GROUP BY
b.GroupSku
HAVING
COUNT(DISTINCT b.SKU) = 1) AS m
LEFT JOIN dbo.RB_Product_GroupSku(NOLOCK) AS n ON n.CustomerID = 1 AND m.GroupSku = n.GroupSku) AS k
select top 0 m.SKU,
m.OnlineTime,
m.产品侵权风险,
m.是否停产,
m.产品一级品类,
m.中文名称,
m.提报人员,
m.AddAdminId
INTO #tb
FROM
(select top 0 a.SKU,
a.OnlineTime,
a.产品侵权风险,
a.是否停产,
a.产品一级品类,
a.中文名称,
a.提报人员,
a.AddAdminId,
a.AddTime,
ROW_NUMBER()OVER(PARTITION BY a.SKU ORDER BY a.AddTime) AS 'rowid'
FROM
(select top 0 a.SKU,
a.OnlineTime,
a.产品侵权风险,
a.是否停产,
a.产品一级品类,
a.中文名称,
c.AddAdminName AS '提报人员',
c.AddAdminId,
c.AddTime
FROM
#sku AS a
LEFT JOIN #groupsku AS d ON a.SKU = d.GroupSku
--LEFT JOIN dbo.RB_Product_GroupSku(NOLOCK) AS d ON d.CustomerID = @CustomerID AND a.SKU = d.GroupSku
LEFT JOIN dbo.RB_ProductDevelopment_Related_Product(NOLOCK) AS b ON b.CustomerID= @CustomerID AND ISNULL(d.SKU,a.SKU) = b.SKU
LEFT JOIN RB_Product_Development(NOLOCK) AS c ON c.CustomerID = @CustomerID AND b.ProductDevelopmentId = c.Id ) AS a) AS m
WHERE m.rowid = 1
select top 0 CASE WHEN LEFT(d.DepartmentName,1) = '成' THEN '成都销售部'
WHEN LEFT(d.DepartmentName,1) = '日' THEN '日本项目部'
WHEN LEFT(d.DepartmentName,1) = '1' THEN '1部公共组'
ELSE LEFT(d.DepartmentName,1) END AS '部门',
d.DepartmentName AS '小组',
a.OrderSourceID,
a.OrderSourceName AS '来源渠道',
b.提报人员 AS '提报人员',
a.BusinessUserName AS 'listing销售人员',
a.SKU,
'销售提报-FBA新品' AS 'SKU开发类型',
b.OnlineTime AS 'SKU终审时间',
b.产品侵权风险,
b.是否停产,
b.产品一级品类,
b.中文名称,
a.OrderSourceSKU AS '渠道SKU',
a.ASINorItemID AS 'ASIN',
a.AddTime AS 'listing刊登时间',
a.OrderSourceSkuOrderTime AS '渠道首单时间',
a.AvgDailySales AS '估算销量',
case
when a.OnShelfMode = 1 then '一键上架'
when a.OnShelfMode = 2 then '草稿上架'
when a.OnShelfMode = 3 then '表格上架'
when a.OnShelfMode = 4 then '关联导入'
when a.OnShelfMode = 5 then '批量跟卖'
when a.OnShelfMode = 6 then '凤凰重刊'
when a.OnShelfMode = 7 then '复制上架'
when a.OnShelfMode = 8 then '智能刊登'
WHEN a.OnShelfMode = 9 THEN '自动刊登'
else ''
end as '上架方式',
a.OrderSourceSkuOrderTime
INTO #listing
FROM
dbo.RB_Amazon_Product_Base(NOLOCK) AS a
INNER JOIN #tb AS b ON a.CustomerID = 1 AND a.SKU = b.SKU
LEFT JOIN dbo.RB_Admin(NOLOCK) AS c ON b.AddAdminId = c.ID AND c.CustomerID = 1
LEFT JOIN dbo.RB_Department(NOLOCK) AS d ON d.CustomerID = 1 AND c.DepartmentID = d.ID
WHERE a.IsFBA = 1 AND a.CustomerID = @CustomerID
AND a.IsDelete = 0 AND a.IsOffShelf = 0
AND a.OrderSourceID IN (@OrderSourceID)
select top 0 m.OrderCode,
m.SKU,
m.ASIN,
m.SellerSKU,
m.AddTime,
m.数量
INTO #bk2
FROM
(select top 0 a.OrderCode,
TRIM(b.ASIN) AS 'ASIN',
b.ASIN,
b.SellerSKU,
a.OrderSourceID,
d.AddTime,
(b.ProductNum + b.ProductNumUpdate) AS '数量',
ROW_NUMBER()OVER(PARTITION BY b.SKU,TRIM(b.ASIN),b.SellerSKU ORDER BY d.AddTime) AS 'rowid'
FROM
dbo.RB_OrderInfo(NOLOCK) AS a
LEFT JOIN dbo.RB_OrderList(NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.OrderCode = b.OrderCode
INNER JOIN #listing AS c ON a.CustomerID = 1 AND a.OrderSourceID = c.OrderSourceID AND b.CustomerID = 1 AND TRIM(b.ASIN) = TRIM(c.ASIN)
AND b.SKU = c.SKU AND b.SellerSKU = c.渠道SKU
--LEFT JOIN RB_Procurement_Order_Package(NOLOCK) AS d ON a.CustomerID = d.CustomerID AND a.OrderCode = d.MergerPackageNo
LEFT JOIN dbo.RB_FBA_ShipmentDetail_Log (NOLOCK) AS d ON a.CustomerID = d.CustomerId AND a.AmazonShipmentId = d.ShipmentId
AND b.SellerSKU = d.SellerSKU AND d.QuantityReceived > 0 AND d.OrderSourceID = a.OrderSourceID
LEFT JOIN dbo.RB_WareHouse(NOLOCK) AS e ON a.CustomerID = e.CustomerID AND a.ToWareHouseID = e.ID
WHERE a.CustomerID = @CustomerID AND a.IsStockOrder = 1 AND a.OrderStatus != 2 AND (b.ProductNum + b.ProductNumUpdate) > 0
AND d.AddTime IS NOT NULL
AND e.WareHouseType = 3
--AND d.AddTime >= @StartTime
--AND d.AddTime <= @EndTime
) AS m
WHERE m.rowid = 1
select top 0 a.OrderCode,
a.SKU,
a.ASIN,
a.SellerSKU,
a.AddTime,
a.数量
INTO #bk
FROM
#bk2 AS a
WHERE a.AddTime >= @StartTime
AND a.AddTime <= @EndTime
select top 0 a.OrderSourceID,
a.SKU,
a.ASIN,
b.AddTime
INTO #temp
FROM
#listing AS a
LEFT JOIN #bk AS b ON a.SKU = b.SKU AND a.渠道SKU = b.SellerSKU
select top 0 a.OrderCode,
a.SKU,
a.OrderSourceID,
a.ASINorItemID,
a.ShipTime,
--c.FirstReceivingTime,
SUM(a.SalesPrice ) AS '销售额收入',
SUM(a.Incomes) AS '销售额',
SUM(a.Profits) AS '利润',
SUM(a.Tax + a.MarketplaceFacilitatorTax) AS '税费收入',
SUM(a.ProductNum + a.ProdutNumUpdate) AS '销量'
INTO #sale
FROM
dbo.RB_Report_OrderSettlement(NOLOCK) AS a
INNER JOIN #tb AS b ON a.CustomerID = 1
AND a.SKU = b.SKU
--INNER JOIN #bk AS c ON a.CustomerID = 1 AND a.SKU = c.SKU AND a.SellerSKU = c.SellerSKU
--AND a.PayDate >= c.FirstReceivingTime
--AND DATEDIFF(d,c.FirstReceivingTime,a.PayDate) <= 30
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 a.OrderSourceType = 1
AND a.BusinessTeamName NOT LIKE '%品牌%' AND ISNULL(a.OrderCode,'') != ''
GROUP BY
a.OrderCode,
a.SKU,
a.OrderSourceID,
a.ASINorItemID,
a.ShipTime
--c.FirstReceivingTime
select top 0 a.OrderSourceID,
a.ReportDate,
a.SKU,
a.Asin,
SUM(a.AdCostRMB) AS '广告费'
INTO #ad
FROM
RB_Amazon_Advertisement_Product_Fees(NOLOCK) AS a
INNER JOIN #listing AS b
ON a.CustomerID = @CustomerID AND a.OrderSourceID = b.OrderSourceID AND a.SKU = b.SKU
GROUP BY
a.OrderSourceID,
a.ReportDate,
a.SKU,
a.Asin
--select top 0 --a.ReportDate,
--a.ExchangeRMB,
--a.USD_ExchangeRMB,
--SUM(a.AdCost) AS '广告费',
--SUM(a.AdCostRMB/a.USD_ExchangeRMB) AS '美元广告费'
--FROM
--RB_Amazon_Advertisement_Product_Fees(NOLOCK) AS a
----INNER JOIN #listing AS b
----ON a.CustomerID = 1 AND a.OrderSourceID = b.OrderSourceID AND a.SKU = b.SKU
--WHERE a.OrderSourceSKU = '17L8KOW029U28E1NS4ZPPC' AND a.CustomerID = 1
--GROUP BY
--a.ExchangeRMB,
--a.USD_ExchangeRMB,
--a.ReportDate
select top 0 m.OrderSourceID,
m.SKU,
m.ASINorItemID,
SUM(case when (m.ShipTime >= m.AddTime) and DATEDIFF(d,m.AddTime,m.ShipTime) <= 30 then m.销量 ELSE 0 END ) AS '到货30天销量',
SUM(case when (m.ShipTime >= m.AddTime) and DATEDIFF(d,m.AddTime,m.ShipTime) <= 30 then m.利润 ELSE 0 END) AS '到货30天利润',
SUM(case when (m.ShipTime >= m.AddTime) and DATEDIFF(d,m.AddTime,m.ShipTime) <= 30 then m.销售额 ELSE 0 END) AS '到货30天销售额',
SUM(m.销量 ) AS '累计销量',
SUM(m.利润) AS '累计利润',
SUM(m.销售额) AS '累计销售额'
INTO #sale2
FROM
(select top 0 a.OrderCode,
a.ShipTime,
a.SKU,
a.OrderSourceID,
a.ASINorItemID,
a.利润,
a.销售额,
--a.利润 - (a.销售额收入 * (d.广告费0.01 + d.平台其他费用0.01 + d.退款率*0.01) + d.盛宝隆仓租) AS '扣除后利润',
a.销量,
c.AddTime
FROM
#sale AS a
LEFT JOIN #temp AS c ON a.SKU = c.SKU AND a.ASINorItemID = c.ASIN AND a.OrderSourceID = c.OrderSourceID
WHERE a.销售额 > 0) AS m
GROUP BY
m.OrderSourceID,
m.SKU,
m.ASINorItemID
select top 0 m.OrderSourceID,
m.SKU,
m.Asin,
SUM(case when m.ReportDate >= m.AddTime and DATEDIFF(d,m.AddTime,m.ReportDate) <= 30 then m.广告费 ELSE 0 END ) AS '到货30天广告费',
SUM(m.广告费) AS '累计广告费'
INTO #ad2
FROM
(select top 0 a.OrderSourceID,
a.ReportDate,
a.SKU,
a.Asin,
a.广告费,
c.AddTime
FROM
#ad AS a
LEFT JOIN #temp AS c ON a.SKU = c.SKU AND a.Asin = c.ASIN AND a.OrderSourceID = c.OrderSourceID ) AS m
GROUP BY
m.OrderSourceID,
m.SKU,
m.Asin
select top 0 a.部门,
a.小组,
a.来源渠道,
a.提报人员,
a.SKU,
a.SKU开发类型,
a.SKU终审时间,
a.产品侵权风险,
a.是否停产,
a.产品一级品类,
a.中文名称,
a.渠道SKU,
a.ASIN,
a.listing刊登时间,
a.上架方式,
b.OrderCode AS '首次到货FBA备库单号',
b.AddTime AS '首次到货时间',
b.数量 AS '首次到货数量',
DATEDIFF(d,a.listing刊登时间,a.渠道首单时间) AS '出单天数(上架-渠道首单)',
DATEDIFF(d,b.AddTime,a.渠道首单时间) AS '出单天数(到货-渠道首单)',
a.渠道首单时间,
a.估算销量,
c.到货30天销量,
ROUND(c.到货30天销售额,2) AS '到货30天销售额',
ROUND(c.到货30天利润,2) AS '到货30天利润',
c.累计销量,
ROUND(c.累计销售额,2) AS '累计销售额',
ROUND(c.累计利润,2) AS '累计利润',
ROUND(d.到货30天广告费,2) AS '到货30天广告费',
ROUND(d.累计广告费,2) AS '累计广告费',
ROUND(ISNULL(c.到货30天利润,0) - ISNULL(d.到货30天广告费,0),2) AS '到货30天利润(扣除广告费)',
ROUND(ISNULL(c.累计利润,0) - ISNULL(d.累计广告费,0),2) AS '累计利润(扣除广告费)'
INTO #final
FROM
#listing AS a LEFT JOIN #bk AS b
ON a.SKU = b.SKU AND a.渠道SKU = b.SellerSKU
LEFT JOIN #sale2 AS c
ON a.OrderSourceID = c.OrderSourceID AND a.SKU = c.SKU AND a.ASIN = c.ASINorItemID
LEFT JOIN #ad2 AS d
ON a.OrderSourceID = d.OrderSourceID AND a.SKU = d.SKU AND a.ASIN = d.Asin
select top 0 * FROM #final
select top 0 a.部门,
a.小组,
a.提报人员,
CONCAT(YEAR(a.首次到货时间),'-',MONTH(a.首次到货时间)) AS '月份',
COUNT(DISTINCT a.渠道SKU) AS '当月FBA新品数量',
COUNT(DISTINCT CASE WHEN a.累计销量 > 0 THEN a.渠道SKU END ) AS '当月FBA新品出单数量',
ROUND((CASE WHEN COUNT(DISTINCT a.渠道SKU) > 0 THEN
COUNT(DISTINCT CASE WHEN a.到货30天销量 > 0 THEN a.渠道SKU END )*1.0/COUNT(DISTINCT a.渠道SKU)
ELSE NULL END),2) AS '新品30天出单率',
SUM(a.到货30天销量) AS '到货30天销量',
ROUND(SUM(a.到货30天销售额),2) AS '到货30天销售额',
ROUND(SUM(a.到货30天利润),2) AS '到货30天利润',
SUM(a.累计销量) AS '累计销量',
ROUND(SUM(a.累计销售额),2) AS '累计销售额',
ROUND(SUM(a.累计利润),2) AS '累计利润',
ROUND(SUM(a.到货30天广告费),2) AS '到货30天广告费',
ROUND(SUM(a.累计广告费),2) AS '累计广告费',
ROUND(SUM(a.[到货30天利润(扣除广告费)]),2) AS '到货30天利润(扣除广告费)',
ROUND(SUM(a.[累计利润(扣除广告费)]),2) AS '累计利润(扣除广告费)',
CASE WHEN SUM(a.到货30天销售额) > 0 THEN
ROUND(SUM(a.[到货30天利润(扣除广告费)])/SUM(a.到货30天销售额),2)
ELSE NULL END AS '利润率',
CASE WHEN SUM(a.[到货30天利润(扣除广告费)]) > 0
THEN ROW_NUMBER()OVER(PARTITION BY CONCAT(YEAR(a.首次到货时间),'-',MONTH(a.首次到货时间))ORDER BY SUM(a.[到货30天利润(扣除广告费)]) DESC)
ELSE NULL END AS '排名'
FROM
#final AS a
WHERE a.首次到货时间 IS NOT NULL
GROUP BY
a.部门,
a.小组,
a.提报人员,
CONCAT(YEAR(a.首次到货时间),'-',MONTH(a.首次到货时间))
--HAVING
--SUM(a.[累计利润(扣除广告费)]) > 0
ORDER BY
月份,
SUM(a.[到货30天利润(扣除广告费)]) DESC
PARAMS:@OrderSourceID = null ,@StartTime = null ,@EndTime = null ,@CustomerId = 0 ,
MESSAGE:The column 'ASIN' was specified multiple times for 'm'.
Invalid column name 'SKU'.
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.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) 在 RB.DALExtend.RB_CustomReportExtend.GetSheetFieldsDr(String sqlContent, List1 sqlParameters)
在 Biz.Irobotbox.ReportCentre.CustomReportService.CheckSqlAndGetFields(String sqlContent, List`1 paramList)
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportEdit.CheckSqlAndGetFields()
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportEdit.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_customreportedit_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)
[结果]
[期望]
非本组报告